24. To analyze the use of Explain Plan, the SQL statement Explain Plan is a good analysis of the SQL statement. It can even analyze the statement without performing SQL. By analysis, we can know how Oracle connection table, what to use Mode scanning table (index scan or full table scan) and the used index name. You need to interpret the analysis from the top to the next order. The result of Explain Plan analysis is used in the indent format. The most internal operation will be interpreted first. If the two operations are in the same layer, the minimum operand will be executed first. NESTED LOOP is a small number of operations that do not process the above rules, the correct execution path is check pair NESTED LOOP provides data operation, where the number of operating numbers will be processed first. Translator Press: By practical, feel is more convenient to use the SET TRACE function in SQLPLUS. Example: SQL> List 1 SELECT * 2 from DEPT, EMP 3 * where emp.deptno = dept.deptno sql> set autotrace traceonly / * traceonly can not display execution result * / SQL> / 14 Rows SELECTED. EXECUTION Plan ---------------------------------------------- ------------------------------------------ 0 Select Statement Optimizer = choose 1 0 Nested Loops 2 1 Table Access (Full) of 'EMP' 3 1 Table Access (By Index RowID) of 'DEPT' 4 3 INDEX (Unique Scan) of 'PK_DEPT' (UNIQUE) statistics --------- ------------------------------------------------- 0 Recursive Calls 2 DB Block Get 30 Consistent Gets 0 Physical Reads 0 Redo Size 2598 Bytes Sent Via Sql * Net To Client 503 BYtes Received Via SQL * Net from Cliant 2 SQL * Net RO Undtrips to / from client 0 Sorts (Memory) 0 Sorts (Disk) 14 Rows Processed passes the above analysis, you can draw the actual execution steps: 1. Table Access (Full) of 'EMP' 2. Index (Unique Scan) of 'Pk_dept' (unique) 3. Table access' 4. Nested loops (Joining 1 and 3) Note: Many third-party tools such as Toad and Oracle itself provide tools such as Omes SQL Analyze It provides an extremely convenient Explain Plan tool. Maybe friends who like the graphical interface can choose them.
