SQLMDX

Eventually it's all about data

Query transformations in Oracle: join elimination

Posted by sqlmdx on December 14, 2013

Join elimination appeared in Oracle version 10gR2.

optimizer_features_enable hint reveals that JE was not applied in 10.2.0.2 while in 10.2.0.3 it took place.

create table t1(id1 number primary key, value number);
insert into t1 select rownum, rownum from dual connect by level <= 10000;
create table t2(id2 number primary key, id1 number references t1, value number);
insert into t2 select rownum, rownum, rownum from dual connect by level <= 10000;
create table t3(id3 number primary key, id2 number references t2, value number);
insert into t3 select rownum, rownum, rownum from dual connect by level <= 10000;
begin
  dbms_stats.gather_table_stats(user,'t1');
  dbms_stats.gather_table_stats(user,'t2');
  dbms_stats.gather_table_stats(user,'t3');
end;
/
SQL> alter session set "optimizer_features_enable" = '10.2.0.2';

Session altered.

SQL> select t3.*
  2    from t1
  3    join t2 on t2.id1 = t1.id1
  4    join t3 on t3.id2 = t2.id2
  5   where t3.id3 = 1;
         1          1          1

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST COST'));
SQL_ID  0y1fu9bc0tjk2, child number 0
-------------------------------------
select t3.*   from t1   join t2 on t2.id1 = t1.id1   join t3 on t3.id2
= t2.id2  where t3.id3 = 1

Plan hash value: 109076599

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     2 (100)|      1 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS                 |              |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       8 |
|   2 |   NESTED LOOPS                |              |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       6 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T3           |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       3 |
|*  4 |     INDEX UNIQUE SCAN         | SYS_C0020301 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2           |      1 |     82 |     1   (0)|      1 |00:00:00.01 |       3 |
|*  6 |     INDEX UNIQUE SCAN         | SYS_C0020299 |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       2 |
|*  7 |   INDEX UNIQUE SCAN           | SYS_C0020298 |      1 |     82 |     0   (0)|      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T3"."ID3"=1)
   6 - access("T3"."ID2"="T2"."ID2")
   7 - access("T2"."ID1"="T1"."ID1")


27 rows selected.

SQL> alter session set "optimizer_features_enable" = '10.2.0.3';

Session altered.

SQL> select t3.*
  2    from t1
  3    join t2 on t2.id1 = t1.id1
  4    join t3 on t3.id2 = t2.id2
  5   where t3.id3 = 1;
         1          1          1

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST COST'));
SQL_ID  0y1fu9bc0tjk2, child number 1
-------------------------------------
select t3.*   from t1   join t2 on t2.id1 = t1.id1   join t3 on t3.id2
= t2.id2  where t3.id3 = 1

Plan hash value: 2578003

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |     2 (100)|      1 |00:00:00.01 |       6 |
|   1 |  NESTED LOOPS                |              |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T3           |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       3 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0020301 |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| T2           |      1 |      4 |     1   (0)|      1 |00:00:00.01 |       3 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C0020299 |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T3"."ID3"=1)
   4 - filter("T2"."ID1" IS NOT NULL)
   5 - access("T3"."ID2"="T2"."ID2")


25 rows selected.

As you can see in second case index SYS_C0020298 (primary key in t1) disappeared from plan.
But more interesting point is that access to primary key of T2 and T2 itself is still in the plan even though access only to T3 is enough to produce required result.
I tried above query on 11.2.0.1 and 12.1.0.1 and plan remains the same as on 10.2.0.3.
It means that currently Oracle is able to eliminate only parent table but not “grandparent” one.
Although above doesn’t mean that Oracle can eliminate only one table in a query block. For example, if fact table has foreign keys to dimension tables and select returns only rows from fact table then all joins with dimensions can be eliminated.

Another interesting point is that cost without join elimination for INDEX UNIQUE SCAN for SYS_C0020298 and SYS_C0020299 (primary keys for t1 and t2 respectively) is zero (even with estimated row count equal to 82 for SYS_C0020298).
Please also note that zero cost is not very rare case and can be easily simulated for instance with predicate which is always evaluated to false.

SQL> explain plan for select * from t3 where null is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3859223164

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    12 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T3   | 10000 |   117K|     9   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

14 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: