In some specific situations the query optimizer is able to completely avoid executing a join even if a SQL statement explicitly calls for it. Two are the cases currently covered by this optimization technique, which is called join elimination. The first one was introduced in Oracle Database 10g Release 2, the second one in Oracle Database 11g Release 2. Let’s take a look at two cases to illustrate how join elimination works.
Case #1
Up to Oracle Database 11g Release 1 join elimination is especially useful when views containing joins are used. Note, however, that join elimination does not work only with views. It can be applied to SQL statements without views as well. The following SQL statements define two tables and one view. Notice that between table T1 and table T2, there is a master-child relationship. In fact, table T2, with its column T1_ID, references the primary key of table T1.
SQL> CREATE TABLE t1 (
2 id NUMBER NOT NULL,
3 n NUMBER,
4 pad VARCHAR2(4000),
5 CONSTRAINT t1_pk PRIMARY KEY(id)
6 );
SQL> CREATE TABLE t2 (
2 id NUMBER NOT NULL,
3 t1_id NUMBER NOT NULL,
4 n NUMBER,
5 pad VARCHAR2(4000),
6 CONSTRAINT t2_pk PRIMARY KEY(id),
7 CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1
8 );
SQL> CREATE VIEW v AS
2 SELECT t1.id AS t1_id, t1.n AS t1_n, t2.id AS t2_id, t2.n AS t2_n
3 FROM t1, t2
4 WHERE t1.id = t2.t1_id;
When all the columns are referenced, as shown in the following example, the join is regularly executed. No surprise here.
SQL> EXPLAIN PLAN FOR SELECT * FROM v;
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 3114288414
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | T2 |
| 4 | INDEX UNIQUE SCAN | T1_PK |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 |
----------------------------------------------
However, as illustrated in the next example, when only columns defined in the child table are referenced, the query optimizer is able to eliminate the join. It can do so because there is a validated foreign key constraint that guarantees that all rows in table T2 reference one row in table T1.
SQL> EXPLAIN PLAN FOR SELECT t2_id, t2_n FROM v;
PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 1513984157
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T2 |
----------------------------------
The relevant part of the output of event 10053 is the following (notice that two queries are shown; the one before the transformation and the one after the transformation):
JE: Considering Join Elimination on query block SEL$2 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T1" "T1",CHA."T2" "T2" WHERE "T1"."ID"="T2"."T1_ID"
JE: cfro: T2 objn:86871 col#:2 dfro:T1 dcol#:2
JE: cfro: T2 objn:86871 col#:2 dfro:T1 dcol#:2
Query block (0x2b732c78) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T1" "T1",CHA."T2" "T2" WHERE "T2"."T1_ID"="T1"."ID"
JE: eliminate table: T1 (T1)
Registered qb: SEL$FFBD8603 0x2b732c78 (JOIN REMOVED FROM QUERY BLOCK SEL$2; SEL$2; "T1"@"SEL$2")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$FFBD8603 nbfros=1 flg=0
fro(0): flg=0 objn=86873 hint_alias="T2"@"SEL$2"
SQL:******* UNPARSED QUERY IS *******
SELECT "T2"."ID" "T2_ID","T2"."N" "T2_N" FROM CHA."T2" "T2"
Query block SEL$FFBD8603 (#0) simplified
Case #2
As of Oracle Database 11g Release 2 join elimination covers another case. Its aim is to avoid the execution of “unnecessary” self-joins. The following SQL statements show an example. Notice that since the join is performed on the primary key (column ID) there is no need to access the table twice. In fact, it is possible to replace the references to the eliminated table (T2 in this example) in the SELECT clause with columns of the table that is not eliminated (T1).
SQL> EXPLAIN PLAN FOR SELECT t11.*, t12.* FROM t1 t11, t1 t12 WHERE t11.id = t12.id;
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 3617692013
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T1 |
----------------------------------
The relevant part of the output of event 10053 is the following (also in this case notice that there are two queries):
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T12"."ID" "ID","T12"."N" "N","T12"."PAD" "PAD" FROM "CHA"."T1" "T11","CHA"."T1" "T12" WHERE "T11"."ID"="T12"."ID"
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:86871 col#:1 dfro:T1 dcol#:1
Query block (0x2c14f098) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T12"."ID" "ID","T12"."N" "N","T12"."PAD" "PAD" FROM "CHA"."T1" "T11","CHA"."T1" "T12" WHERE "T11"."ID"="T12"."ID"
JE: eliminate table: T1 (T12)
JE: Replaced column: T12.PAD with column: T11.PAD
JE: Replaced column: T12.N with column: T11.N
JE: Replaced column: T12.ID with column: T11.ID
Registered qb: SEL$DF69B110 0x2c14f098 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "T12"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$DF69B110 nbfros=1 flg=0
fro(0): flg=0 objn=86871 hint_alias="T11"@"SEL$1"
SQL:******* UNPARSED QUERY IS *******
SELECT "T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD","T11"."ID" "ID","T11"."N" "N","T11"."PAD" "PAD" FROM "CHA"."T1" "T11"
Query block SEL$DF69B110 (#0) simplified
Note that running the previous example in Oracle Database 11g Release 1 or earlier leads, as expected, to a join like the following one.
SQL> EXPLAIN PLAN FOR SELECT t11.*, t12.* FROM t1 t11, t1 t12 WHERE t11.id = t12.id;
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 774821007
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | T1 |
| 4 | INDEX UNIQUE SCAN | T1_PK |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 |
----------------------------------------------
