As we were told that "The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is executed first."
however, a simple case about the execution order troubled us a lot, plz take a brief look at the example below.
SQL> create table t1 as select rownum as RN,dbms_random.value(0,1000) as DT from xmltable('1 to 1000');
Table created.
SQL> create table t2 as select rownum as RN,dbms_random.value(0,2000) as DT from xmltable('1 to 1000');
Table created.
SQL> create table t3 as select rownum as RN,dbms_random.value(0,3000) as DT from xmltable('1 to 1000');
Table created.
SQL> create table t4 as select rownum as RN,dbms_random.value(0,4000) as DT from xmltable('1 to 1000');
Table created.
SQL> explain plan for select t1.dt,t2.dt,t3.dt,t4.dt from t3 JOIN t4 ON t3.DT=t4.DT LEFT JOIN t2 ON t3.DT=t2.DT JOIN t1 ON t2.DT=t1.DT;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2117174619
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 52000 | 12 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 52000 | 12 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 1000 | 13000 | 3 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1000 | 39000 | 9 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 1000 | 13000 | 3 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 1000 | 26000 | 6 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T3 | 1000 | 13000 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| T4 | 1000 | 13000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."DT"="T1"."DT")
3 - access("T3"."DT"="T2"."DT")
5 - access("T3"."DT"="T4"."DT")
Note
-----
- dynamic sampling used for this statement (level=2)
25 rows selected.
Technically, we guess the order of excution plan above should be 6-7-5-4-3-2-1 (refers to the 'Id' in PLAN TABLE). To our supprise, the
'CORRECT' order of execution plan for this SQL statement turns out to be 2-4-6-7-5-3-1, as it is shown below (
note the sequence of Order Column of plan table).
SQL> select * from table(XPLAN.DISPLAY_CURSOR('fjj5g6fqpn4vd'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID fjj5g6fqpn4vd, child number 0
-------------------------------------
select t1.dt,t2.dt,t3.dt,t4.dt from t3 JOIN t4 ON t3.DT=t4.DT LEFT JOIN
t2 ON t3.DT=t2.DT JOIN t1 ON t2.DT=t1.DT
Plan hash value: 2117174619
-------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | 8 | SELECT STATEMENT | | | | 16 (100)| |
|* 1 | 7 | HASH JOIN | | 1000 | 88000 | 16 (0)| 00:00:01 |
| 2 | 1 | TABLE ACCESS FULL | T1 | 1000 | 22000 | 4 (0)| 00:00:01 |
|* 3 | 6 | HASH JOIN | | 1000 | 66000 | 12 (0)| 00:00:01 |
| 4 | 2 | TABLE ACCESS FULL | T2 | 1000 | 22000 | 4 (0)| 00:00:01 |
|* 5 | 5 | HASH JOIN | | 1000 | 44000 | 8 (0)| 00:00:01 |
| 6 | 3 | TABLE ACCESS FULL| T3 | 1000 | 22000 | 4 (0)| 00:00:01 |
| 7 | 4 | TABLE ACCESS FULL| T4 | 1000 | 22000 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------
1 - access("T2"."DT"="T1"."DT")
3 - access("T3"."DT"="T2"."DT")
5 - access("T3"."DT"="T4"."DT") What puzzled me is
why 2-4-6-7-5-3-1 rather than 6-7-5-4-3-2-1? This confused us all day long.
hope you guys could help us.
The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the rightNo!
The plan starts with the
first leaf. This is the top-most operation with no children*.
To find this, you need to walk down the plan finding the
first child of each operation.
In your plan the first child of every join is a full scan. This fits with the order shown.
You know this because the operation directly below and indented to the right is a full table scan. So for each join, the database:
- Reads the rows from the first table
- Uses these to build the hash table
- Starts the second operation
For the first two joins, this is another join. For the last join it's another table scan.
* As always, there's an exception. If you have a scalar subquery, the top-most leaf in the plan is executed
last.
How do you know this?
Hint one: you have a scalar subquery :)
Hint two: (usually) you'll read the table in the subquery many times. Possibly once/row from the driving table. You can see this by enabling row stats and looking at the "Starts" column in the plan:
select /*+ gather_plan_statistics */ (
select count(*) from t2
where round(t1.dt) = round(t2.dt)
)
from t1;
select * from dbms_XPLAN.DISPLAY_CURSOR(null, null, 'ROWSTATS LAST');
--------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |
| 1 | SORT AGGREGATE | | 1000 | 1 | 1000 |
|* 2 | TABLE ACCESS FULL| T2 | 1000 | 10 | 538 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 1000 | 1000 |
--------------------------------------------------------------