29 | 03 | 2024
Latest Articles
Popular Articles

SQL Tuning

Query Transformations : Join Elimination

User Rating:  / 1
PoorBest 

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.