2011-12-14

A table vanishing from a plan

Christian Antognini published his presentation slides Challenges and Chances of the 11g Query Optimizer. Worth reading to get a nice overview. The 11g new feature "join elimination" is mentioned there. There is a similar behavior even in 10gR2 optimizer. A table mentioned in a query text vanishes from the query plan. It is not a join but a correlated sub query with a aggregate.
select 1 
  from dual mai 
 where exists (
   select max(1) 
     from dual inn 
    where mai.dummy=inn.dummy
)
;

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|   1 |  FAST DUAL       |      |
---------------------------------
The sub query and the "inn" table is not seen in the plan. There is no need as the aggregate returns a row. This is not the case in my earlier post about not exists null. There the sub query is not correlated and it is using not exists. The not exists correlated makes similar elimination. But in addition adds a filter(NULL IS NOT NULL) just before entering even the first table.
select 1 
  from dual mai 
 where not exists (
   select max(1) 
     from dual inn 
    where mai.dummy=inn.dummy
)
;

---------------------------------
| Id  | Operation        | Name |
---------------------------------
|   0 | SELECT STATEMENT |      |
|*  1 |  FILTER          |      |
|   2 |   FAST DUAL      |      |
---------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
One could ask, why would somebody write such a query...

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.