Database Reference
In-Depth Information
7 - filter("T1"."N"=19)
8 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
10 - access("T2"."ID"="T3"."T2_ID")
filter("T2"."ID"="T3"."T2_ID")
12 - access("T3"."ID"="T4"."T3_ID")
filter("T3"."ID"="T4"."T3_ID")
In the following example, only one of the columns referenced in the
SELECT
clause isn't referenced in the
WHERE
clause (
t4.id
). It's important to note that except for operation 6, smaller work areas were used for all other sorts and
that this was true even though the execution plan was the same in both cases. Also notice that the query optimizer's
estimations (column
Omem
) take this difference into consideration:
SELECT
t1.id, t2.id, t3.id, t4.id
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19
-----------------------------------------------------------
| Id | Operation | Name | OMem | Used-Mem |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | MERGE JOIN | | | |
| 2 | SORT JOIN | | 14336 |12288 (0)|
| 3 | MERGE JOIN | | | |
| 4 | SORT JOIN | | 3072 | 2048 (0)|
| 5 | MERGE JOIN | | | |
| 6 | SORT JOIN | | 3072 | 2048 (0)|
|* 7 | TABLE ACCESS FULL| T1 | | |
|* 8 | SORT JOIN | | 16384 |14336 (0)|
| 9 | TABLE ACCESS FULL| T2 | | |
|* 10 | SORT JOIN | | 106K|96256 (0)|
| 11 | TABLE ACCESS FULL | T3 | | |
|* 12 | SORT JOIN | | 407K| 361K (0)|
| 13 | TABLE ACCESS FULL | T4 | | |
-----------------------------------------------------------
7 - filter("T1"."N"=19)
8 - access("T1"."ID"="T2"."T1_ID")
filter("T1"."ID"="T2"."T1_ID")
10 - access("T2"."ID"="T3"."T2_ID")
filter("T2"."ID"="T3"."T2_ID")
12 - access("T3"."ID"="T4"."T3_ID")
filter("T3"."ID"="T4"."T3_ID")
