SQL Tuning
Query Transformations : Join Elimination
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Tuesday, 04 November 2014 19:03
-
Last Updated on Tuesday, 10 December 2019 14:48
-
Published on Tuesday, 04 November 2014 19:25
-
Written by Guy Lambregts
-
Hits: 6077
Query Transformations : Join Elimination
Join Elimniation is maybe not really a query transformation but it happens behind the scenes ( and screens ).
If we trace the optimizer with event 10053 we can capture join elimniation info as shown below
Assume we setup the sample schema
SQL> alter session set events='10053 trace name context forever, level 1';
Session altered.
SQL> select * from VALL_JE where ts_pk=100;
SQL> alter session set events='10053 trace name context off';
Session altered.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 461574646
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 525 | 44625 | 172 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 525 | 44625 | 172 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TS | 1 | 24 | 1 (0)| 00:00:01 |
| * 3 | INDEX UNIQUE SCAN | TS_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | VIEW | | 525 | 32025 | 171 (1)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | WINDOW BUFFER | | 52 | 1560 | 103 (1)| 00:00:01 |
| * 7 | TABLE ACCESS FULL | TC | 52 | 1560 | 103 (1)| 00:00:01 |
| 8 | WINDOW BUFFER | | 473 | 12298 | 69 (2)| 00:00:01 |
| 9 | NESTED LOOPS | | 473 | 12298 | 69 (2)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | TS_PK | 1 | 3 | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | TI | 473 | 10879 | 69 (2)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TS_PK"=100)
7 - filter("TC_DATE3" IS NULL AND "TS_FK"=100)
10 - access("TS_PK"=100)
11 - filter("TS_FK"=100)
26 rows selected.
The trace file in the diagnostic destination show us
####################################################################################################################################################
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "TC"."TC_PK" "PK","TC"."TS_FK" "TS_FK","TC"."TC_DATE1" "DATE1","TC"."TC_VAL1" "VAL1",SUM("TC"."TC_VAL1") OVER ( PARTITION BY "TC"."TS_FK") "VAL1_SUM" FROM TUNING."TC" "TC",TUNING."TS" "TS" WHERE "TS"."TS_PK"="TC"."TS_FK" AND "TC"."TC_DATE3" IS NULL
JE: cfro: TC objn:140223 col#:2 dfro:TS dcol#:1
JE: cfro: TC objn:140223 col#:2 dfro:TS dcol#:1
Query block (0x7f28804a9850) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "TC"."TC_PK" "PK","TC"."TS_FK" "TS_FK","TC"."TC_DATE1" "DATE1","TC"."TC_VAL1" "VAL1",SUM("TC"."TC_VAL1") OVER ( PARTITION BY "TC"."TS_FK") "VAL1_SUM" FROM TUNING."TC" "TC",TUNING."TS" "TS" WHERE "TC"."TS_FK"="TS"."TS_PK" AND "TC"."TC_DATE3" IS NULL
JE: eliminate table: TS (TS)
Registered qb: SEL$A21CCAED 0x804a9850 (JOIN REMOVED FROM QUERY BLOCK SEL$3; SEL$3; "TS"@"SEL$3")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$A21CCAED nbfros=1 flg=0
fro(0): flg=0 objn=140225 hint_alias="TC"@"SEL$3"
SQL:******* UNPARSED QUERY IS *******
SELECT "TC"."TC_PK" "PK","TC"."TS_FK" "TS_FK","TC"."TC_DATE1" "DATE1","TC"."TC_VAL1" "VAL1",SUM("TC"."TC_VAL1") OVER ( PARTITION BY "TC"."TS_FK") "VAL1_SUM" FROM TUNING."TC" "TC" WHERE "TC"."TS_FK" IS NOT NULL AND "TC"."TC_DATE3" IS NULL
Query block SEL$A21CCAED (#0) simplified
####################################################################################################################################################
What would the explain plan be if join elimination would not have happened ? We can control join elimination with the parameter _optimizer_join_elimination_enabled, it defaults to false.
There are not many reasons to alter the default value from true to false. The reason why I do it here is for learning and demo purposes.
SQL> alter session set "_optimizer_join_elimination_enabled"=false;
Session altered.
SQL> explain plan for select * from VALL_JE where ts_pk=100;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value:
1339558207
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 525 | 44625 | 172 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 525 | 44625 | 172 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TS | 1 | 24 | 1 (0)| 00:00:01 |
| * 3 | INDEX UNIQUE SCAN | TS_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | VIEW | | 525 | 32025 | 171 (1)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | WINDOW BUFFER | | 52 | 1716 | 103 (1)| 00:00:01 |
| 7 | NESTED LOOPS | | 52 | 1716 | 103 (1)| 00:00:01 |
| * 8 | INDEX UNIQUE SCAN | TS_PK | 1 | 3 | 0 (0)| 00:00:01 |
| * 9 | TABLE ACCESS FULL | TC | 52 | 1560 | 103 (1)| 00:00:01 |
| 10 | WINDOW BUFFER | | 473 | 12298 | 69 (2)| 00:00:01 |
| 11 | NESTED LOOPS | | 473 | 12298 | 69 (2)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | TS_PK | 1 | 3 | 0 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | TI | 473 | 10879 | 69 (2)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TS_PK"=100)
8 - access("TS_PK"=100)
9 - filter("TC_DATE3" IS NULL AND "TS_FK"=100)
12 - access("TS_PK"=100)
13 - filter("TS_FK"=100)
29 rows selected.