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