Table elimination a new optimization technique that got introduced in
Oracle 10g to remove redundant table from a query. A table is redundant if its columns are only
referenced in join predicates, and its guarantee that those joins neither
filter or expand the resulting rows. There are several cases where oracle will
eliminate the redundant table and that got improved over the versions of Oracle
database.
The optimizer eliminates tables that are redundant due to PK-FK
constraints in place. Consider these data model (from HR schema) for this blog
series.
And the query:
select e.first_name, e.last_name,e.email
from employees e,
departments d
where e.department_id = d.department_id;
In this query, the join to departments is redundant. The only column d.department_id
referenced in the query appears in the join predicate, and the PK-FK constraint
guarantees that there is at most one match in department for each row in
employees.
In 10g R1 (10.1.0.5) – no table elimination will be there.
demo@PDB1> alter session set
optimizer_features_enable='10.1.0.5';
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: 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")
In 10g R2 (10.2.0.5), the table elimination will be like this.
demo@PDB1> alter session set
optimizer_features_enable='10.2.0.5';
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 | |
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>
However, Oracle 10gR2 doesn’t support join elimination for queries
involving Outer, Semi and Anti joins.
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 (+)
5 /
Execution Plan
----------------------------------------------------------
Plan hash value: 3172634026
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
107 | 3210 | 3
(0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| |
107 | 3210 | 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> 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: 1768210675
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
106 | 3180 | 3
(0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | |
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 | 27 |
108 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
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: 1538847337
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 |
30 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 |
30 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107
| 2782 | 3
(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 27 |
108 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
So with Oracle 10gR2 the only supported table elimination type is Equi/Inner
join.
No comments:
Post a Comment