Table Elimination - Part V

Having discussed in the past (part1, part2, part3 and part4) about the Join elimination – ability of the optimizer to remove joins that are not required in the query. It is also important to know what precondition must be satisfied for join elimination.

To enable join elimination, the relationship between the tables must be implemented with foreign key constraints. If the constraints are missing, most of the query transformation are not possible at all. Therefore, it is highly recommended to use constraints.

In data warehouses, a common approach is to define the foreign key constraints with DISABLE NOVALIDATE state. In this case, the constraints are visible in the database and can be used for documentation and reverse engineering of the data model, but also for query transformation like join elimination and query rewrites. But the data loaded by the ETL process are not checked/validated against the constrains. Therefore, data consistency has to be guaranteed by the ETL process.

When a constraints is not validated, it should be defined as a reliable in the data warehouse. This is required if query rewrite on materialized view is used. But it is a precondition for join elimination too.

demo@ORA11G> create table emp as

  2  select *

  3  from scott.emp ;

Table created.

demo@ORA11G> create table dept as

  2  select *

  3  from scott.dept ;

Table created.

demo@ORA11G> alter table dept

  2  add constraint dept_pk

  3  primary key(deptno) rely novalidate;

Table altered.

demo@ORA11G> alter table emp

  2  add constraint emp_fk

  3  foreign key(deptno) references dept rely novalidate

  4  modify deptno not null;

Table altered.

demo@ORA11G> select constraint_name,table_name,constraint_type,status,validated,rely

  2  from user_constraints

  3  where table_name in ('EMP','DEPT')

  4  and constraint_type in ('P','R') ;

CONSTRAINT_NAME TABLE_NAME      C STATUS   VALIDATED     RELY

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

DEPT_PK         DEPT            P ENABLED  NOT VALIDATED RELY

EMP_FK          EMP             R ENABLED  NOT VALIDATED RELY

demo@ORA11G> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE

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

optimizer_features_enable            string      11.2.0.4

demo@ORA11G> set autotrace traceonly explain

demo@ORA11G> select ename

  2  from emp e, dept d

  3  where e.deptno = d.deptno;

Execution Plan

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

Plan hash value: 3956160932

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

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

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

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

|   1 |  TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |

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

Note

-----

   - dynamic sampling used for this statement (level=2)

demo@ORA11G>

With NOVALIDATE and RELY constraints table DEPT was eliminated in the above sql. However, in Oracle 12c the plan looks slightly different.

demo@PDB1> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE

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

optimizer_features_enable            string      12.2.0.1

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select ename

  2  from emp e, dept d

  3  where e.deptno = d.deptno;

Execution Plan

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

Plan hash value: 4269077325

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

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

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

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

|   1 |  NESTED LOOPS      |         |    14 |   168 |     3   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP     |    14 |   126 |     3   (0)| 00:00:01 |

|*  3 |   INDEX UNIQUE SCAN| DEPT_PK |     1 |     3 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - access("E"."DEPTNO"="D"."DEPTNO")

In Oracle 12c, join elimination with NOVALIDATE will work only, if QUERY_REWRITE_INTEGRITY parameter set to TRUSTED. This is mentioned in the documentation.

If a foreign key constraint is in NOVALIDATE state, join elimination is not done when QUERY_REWRITE_INTEGRITY=enforced. This means that queries with joins over a foreign key constraint that is in RELY NOVALIDATE state can potentially take longer to parse and execute as the optimizer does not trust the RELY.

demo@PDB1> show parameter query_rewrite_integrity

NAME                                 TYPE        VALUE

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

query_rewrite_integrity              string      enforced

demo@PDB1> alter session set query_rewrite_integrity=Trusted;

Session altered.

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select ename

  2  from emp e, dept d

  3  where e.deptno = d.deptno;

Execution Plan

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

Plan hash value: 3956160932

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

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

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

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

|   1 |  TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |

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

Note

-----

   - rely constraint used for this statement

demo@PDB1>

In Summary foreign key constraints in data warehouses are often defined with NOVALIDATE. To enable query transformation such as join elimination and query rewrite, the constraints have to be set to reliable with RELY keyword. Additionally, you have to take care that the parameter QUERY_REWRITE_INTEGRITY is set to TRUSTED on an Oracle 12c environment, otherwise join elimination will not work anymore.

Table Elimination - Part IV

One of the key thing addressed in Oracle 12.2 optimizer is the ability to perform table elimination (aka join elimination) for relationships based on composite keys.

demo@PDB1> create table t1 as

  2  select rownum as t1_id ,

  3     a.*

  4  from all_objects a

  5  where rownum <=5;

Table created.

demo@PDB1>

demo@PDB1> create table t2 as

  2  select mod(rownum,5)+1 as t1_id,

  3     rownum as t2_id ,

  4     a.*

  5  from all_objects a

  6  where rownum <=10000;

Table created.

demo@PDB1> create table t3 as

  2  select t1_id, t2_id ,

  3     rownum as t3_id ,

  4     rpad('*',80,'*') as string_pad

  5  from t2 ;

Table created.

demo@PDB1> alter table t1 add constraint t1_pk primary key(t1_id);

Table altered.

demo@PDB1> alter table t2 add constraint t2_pk primary key(t1_id,t2_id);

Table altered.

demo@PDB1> alter table t3 add constraint t3_pk primary key(t1_id,t2_id,t3_id);

Table altered.

demo@PDB1> alter table t2 add constraint t2_fk

  2  foreign key(t1_id)

  3  references t1;

Table altered.

demo@PDB1> alter table t3 add constraint t3_fk

  2  foreign key(t1_id,t2_id)

  3  references t2;

Table altered.

demo@PDB1>

We got three tables T1, T2 and T3, where T1 and T2 connected using single-column primary/foreign keys. Where T2 and T3 connected using composite primary/foreign keys.

Now we will take a simple query involving three table joins and test it against 12.1 and 12.2 optimizers.

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

Session altered.

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select count(*)

  2  from t3, t2, t1

  3  where t1.t1_id = t2.t1_id

  4  and t2.t1_id = t3.t1_id

  5  and t2.t2_id = t3.t2_id;

Execution Plan

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

Plan hash value: 402256924

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

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

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

|   0 | SELECT STATEMENT       |       |     1 |    14 |    11  (10)| 00:00:01 |

|   1 |  SORT AGGREGATE        |       |     1 |    14 |            |          |

|   2 |   NESTED LOOPS         |       | 10000 |   136K|    11  (10)| 00:00:01 |

|   3 |    INDEX FAST FULL SCAN| T3_PK | 10000 | 70000 |    10   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN   | T2_PK |     1 |     7 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("T2"."T1_ID"="T3"."T1_ID" AND "T2"."T2_ID"="T3"."T2_ID")

The need for the table T1 is eliminated, since prior to 12.2 optimizer can do table elimination for only single column primary/foreign keys.

However running the same query against the 12.2 optimizer produced the results like this.

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

Session altered.

demo@PDB1> select count(*)

  2  from t3, t2, t1

  3  where t1.t1_id = t2.t1_id

  4  and t2.t1_id = t3.t1_id

  5  and t2.t2_id = t3.t2_id;

Execution Plan

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

Plan hash value: 2110918630

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

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

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

|   0 | SELECT STATEMENT      |       |     1 |    10   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |       |     1 |            |          |

|   2 |   INDEX FAST FULL SCAN| T3_PK | 10000 |    10   (0)| 00:00:01 |

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

demo@PDB1>

This confirms that 12.2 optimizer can do table elimination for relationships involving composite primary/foreign key columns.

However, one important thing to note is if we change the order of the tables in from clause like this t1 -> t2 -> t3  then plan changes like this.

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select count(*)

  2  from t1, t2, t3

  3  where t1.t1_id = t2.t1_id

  4  and t2.t1_id = t3.t1_id

  5  and t2.t2_id = t3.t2_id;

Execution Plan

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

Plan hash value: 611653035

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

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

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

|   0 | SELECT STATEMENT       |       |     1 |     6 |    10   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE        |       |     1 |     6 |            |          |

|   2 |   NESTED LOOPS         |       | 10000 | 60000 |    10   (0)| 00:00:01 |

|   3 |    INDEX FAST FULL SCAN| T3_PK | 10000 | 30000 |    10   (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN   | T1_PK |     1 |     3 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("T1"."T1_ID"="T3"."T1_ID")

demo@PDB1>

The optimizer has eliminate T2 but not T1.

Looking into the 10053 trace it shows this

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "DEMO"."T1" "T1","DEMO"."T2" "T2","DEMO"."T3" "T3" WHERE "T1"."T1_ID"="T2"."T1_ID" AND "T2"."T1_ID"="T3"."T1_ID" AND "T2"."T2_ID"="T3"."T2_ID"

JE:[V2] Query block (00007FFD15BD4108) before join elimination:

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "DEMO"."T1" "T1","DEMO"."T2" "T2","DEMO"."T3" "T3" WHERE "T1"."T1_ID"="T2"."T1_ID" AND "T2"."T1_ID"="T3"."T1_ID" AND "T2"."T2_ID"="T3"."T2_ID"

JE:[V2]: Try to eliminate T3 by ref. join elim using NTSID

JE:[V2]: Cannot eliminate T3 by ref. join elim - no constr. based join pred(s)

JE:[V2]: Try to eliminate T3 by ref. join elim using NTSID

JE:[V2]: Cannot eliminate T3 by ref. join elim - no constr. based join pred(s)

JE:[V2]: Try to eliminate T2 by ref. join elim using PRIMARY(T1_ID, T2_ID) <- FOREIGN(T1_ID, T2_ID)

JE:[V2]: Can eliminate T2 by ref. join elim using PRIMARY(T1_ID, T2_ID) <- FOREIGN(T1_ID, T2_ID)

JE:[V2] Eliminate table: T2 (T2)

JE:[V2] Replaced column: T2.T1_ID with column: T3.T1_ID

JE:[V2] Query block (00007FFD15BD4108) after join elimination:

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "DEMO"."T1" "T1","DEMO"."T3" "T3" WHERE "T1"."T1_ID"="T3"."T1_ID"

The above trace confirms that optimizer started with evaluation of join between T2 and T3 and eliminated the need for T2, and left off with T1 and T3, finally no relationship between T1 and T3 so no further table elimination is possible.

However, this behavior is termed as a bug 22228669 at Oracle support.

On the final note. Table elimination on composite keys to work, you might have to get a bit lucky in the order you list the tables in from clause.

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

Table Elimination - Part II

In the previous blog post we discussed how table elimination – a new optimization introduced in Oracle10g Release 2 with the ability to transparently eliminate the table during the sql execution – that supported only equi/inner joins.

With Oracle 10g Release 2 the optimizer doesn’t support table elimination for outer joins, semi joins and anti joins. However, with Oracle 11g Release 1, the optimizer support table elimination for all these joins

Table elimination for outer joins:

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

Session altered.

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select e.first_name, e.last_name,e.email

  2  from hr.employees e,

  3     hr.departments d

  4  where e.department_id = d.department_id (+);

Execution Plan

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

Plan hash value: 1445457117

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

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

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

|   0 | SELECT STATEMENT  |           |   107 |  2461 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  2461 |     3   (0)| 00:00:01 |

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

Table elimination for Semi joins:

demo@PDB1> select e.first_name, e.last_name,e.email

  2  from hr.employees e

  3  where exists( select null

  4  from hr.departments d

  5  where e.department_id = d.department_id ) ;

Execution Plan

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

Plan hash value: 1445457117

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

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

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

|   0 | SELECT STATEMENT  |           |   106 |  2756 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  2756 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

Table elimination for Anti joins:

demo@PDB1> select e.first_name, e.last_name,e.email

  2  from hr.employees e

  3  where not exists( select null

  4  from hr.departments d

  5  where e.department_id = d.department_id ) ;

Execution Plan

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

Plan hash value: 1445457117

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

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

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

|   0 | SELECT STATEMENT  |           |     1 |    26 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    26 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("E"."DEPARTMENT_ID" IS NULL)

demo@PDB1>

However with Oracle 11g Release 1 (prior to 11.1.0.7) – if the join key is referred elsewhere in the query other than the join condition in the where clause, table elimination cannot occur.

demo@PDB1> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE

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

optimizer_features_enable            string      11.1.0.6

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select e.first_name, e.last_name,e.email,d.department_id

  2  from hr.employees e,

  3     hr.departments d

  4  where e.department_id = d.department_id;

Execution Plan

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

Plan hash value: 169719308

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

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

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

|   0 | SELECT STATEMENT   |            |   106 |  3180 |     3   (0)| 00:00:01 |

|   1 |  NESTED LOOPS      |            |   106 |  3180 |     3   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  2782 |     3   (0)| 00:00:01 |

|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

demo@PDB1> set autotrace off

demo@PDB1>

demo@PDB1>

the join key in this example is d.department_id – that is available in both the join clause and select list.  Since this join key is available in the select list, table elimination doesn’t kick in with 11.1.0.6 optimizer, however that got addressed in 11.1.0.7 optimizer.

Running the same query again the 11.1.0.7 optimizer produced the plan like this

demo@PDB1>

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

Session altered.

demo@PDB1> set autotrace traceonly explain

demo@PDB1> select e.first_name, e.last_name,e.email,d.department_id

  2  from hr.employees e,

  3     hr.departments d

  4  where e.department_id = d.department_id;

Execution Plan

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

Plan hash value: 1445457117

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

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

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

|   0 | SELECT STATEMENT  |           |   106 |  2756 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  2756 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

demo@PDB1> set autotrace off