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
