24. Analyze SQL statements with EXPLAIN PLAN
Explain Plan is a good tool for analyzing the SQL statement. It can even analyze the statement without performing SQL. Through analysis, we can know how Oracle connection table, what way to scan the table (index scan or full Table scanning) and the name of the index.
You need to interpret the results of the analysis from the order of the upward order. The result of the EXPLAIN PLAN analysis is used in the indent format, the inside of the internal operation will be interpreted first, if the two operations are in the same layer In, there is a minimum operand will be executed first.
NESTED LOOP is a few operations that do not process the above rules, the correct execution path is to check the data of the NESTED LOOP, where the minimum operating number will be processed.
Translator presses:
By practicing, it 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 the 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 Gets
30 Consistent Gets
0 Physical READS
0 redo size
2598 BYTES SENT VIA SQL * NET to Client
503 Bytes Received Via SQL * Net from Client
2 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
14 rows proped
Through the above analysis, the actual execution step is:
1. Table Access (Full) of 'EMP'
2. INDEX (Unique Scan) of 'PK_DEPT' (UNIQUE)
3. Table Access (by index rowid) of 'Dept'
4. Nested Loops (Joining 1 and 3)
Note: Many third-party tools such as Toad and Oracle itself provide extremely convenient Explain PLAN tools such as Oms, and other friends who like the graphical interface can choose them.
(to be continued)
