Table Elimination - Part III

One of the nice touch added in Oracle 11g Release 2 optimizer is the ability to eliminate the redundant tables based on a self-joins.

That is if a table joins to itself based on only the primary/unique key columns then table elimination is possible from Oracle 11g Release 2.

We created a table with primary key on it.

demo@PDB1> create table t as select * from scott.emp;

Table created.

demo@PDB1> alter table t modify empno not null;

Table altered.

demo@PDB1> alter table t add constraint t_pk primary key(empno);

Table altered.

Setting OFE prior to 11.2 does not bring table elimination for self-joins.

demo@PDB1> alter session set optimizer_features_enable='11.1.0.7';

Session altered.

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select *

  2  from t t1, t t2

  3  where t1.empno = t2.empno ;

Execution Plan

----------------------------------------------------------

Plan hash value: 2447473301

-------------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |    14 |  1064 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN                  |      |    14 |  1064 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    14 |   532 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | T_PK |    14 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |      |    14 |   532 |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | T    |    14 |   532 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("T1"."EMPNO"="T2"."EMPNO")

       filter("T1"."EMPNO"="T2"."EMPNO")

However, with 11.2 optimizer table elimination for self-joins goes like this

demo@PDB1> alter session set optimizer_features_enable='11.2.0.4';

Session altered.

demo@PDB1> select *

  2  from t t1, t t2

  3  where t1.empno = t2.empno ;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |    14 |   532 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Table elimination for self-join is still possible using unique constraints also.

demo@PDB1> alter table t drop constraint t_pk;

Table altered.

demo@PDB1> alter table t add constraint t_pk unique(empno);

Table altered.

demo@PDB1> select *

  2  from t t1, t t2

  3  where t1.empno = t2.empno ;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |    14 |   532 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

However, without unique or primary key constraints, no table eliminations were possible.

demo@PDB1> alter table t drop constraint t_pk;

Table altered.

demo@PDB1> select *

  2  from t t1, t t2

  3  where t1.empno = t2.empno ;

Execution Plan

----------------------------------------------------------

Plan hash value: 2135975663

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    14 |  1064 |     6   (0)| 00:00:01 |

|*  1 |  HASH JOIN         |      |    14 |  1064 |     6   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T    |    14 |   532 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T    |    14 |   532 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T1"."EMPNO"="T2"."EMPNO")

demo@PDB1> set autotrace off