Having discussed in
the past (part1,
part2,
part3
and part4)
about the Join elimination – ability of the optimizer to remove joins that are
not required in the query. It is also important to know what precondition must
be satisfied for join elimination.
To enable join
elimination, the relationship between the tables must be implemented with
foreign key constraints. If the constraints are missing, most of the query
transformation are not possible at all. Therefore, it is highly recommended to
use constraints.
In data warehouses,
a common approach is to define the foreign key constraints with DISABLE
NOVALIDATE state. In this case, the constraints are visible in the database and
can be used for documentation and reverse engineering of the data model, but
also for query transformation like join elimination and query rewrites. But the
data loaded by the ETL process are not checked/validated against the
constrains. Therefore, data consistency has to be guaranteed by the ETL process.
When a constraints
is not validated, it should be defined as a reliable in the data warehouse.
This is required if query rewrite on materialized view is used. But it is a
precondition for join elimination too.
demo@ORA11G> create table emp as
2 select *
3 from scott.emp ;
Table created.
demo@ORA11G> create table dept as
2 select *
3 from scott.dept ;
Table created.
demo@ORA11G> alter table dept
2 add constraint dept_pk
3 primary key(deptno) rely
novalidate;
Table altered.
demo@ORA11G> alter table emp
2 add constraint emp_fk
3 foreign key(deptno) references
dept rely novalidate
4 modify deptno not null;
Table altered.
demo@ORA11G> select
constraint_name,table_name,constraint_type,status,validated,rely
2 from user_constraints
3 where table_name in
('EMP','DEPT')
4 and constraint_type in ('P','R')
;
CONSTRAINT_NAME TABLE_NAME C
STATUS VALIDATED RELY
--------------- --------------- - --------
------------- ----
DEPT_PK
DEPT P ENABLED NOT
VALIDATED RELY
EMP_FK
EMP R ENABLED NOT
VALIDATED RELY
demo@ORA11G> show parameter
optimizer_features_enable
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
optimizer_features_enable
string 11.2.0.4
demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select ename
2 from emp e, dept d
3 where e.deptno = d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 14 | 98 | 3
(0)| 00:00:01 |
| 1 | TABLE ACCESS FULL|
EMP | 14 | 98 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement (level=2)
demo@ORA11G>
With NOVALIDATE and
RELY constraints table DEPT was eliminated in the above sql. However, in Oracle
12c the plan looks slightly different.
demo@PDB1> show parameter
optimizer_features_enable
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
optimizer_features_enable string 12.2.0.1
demo@PDB1> set autotrace traceonly explain
demo@PDB1> select ename
2 from emp e, dept d
3 where e.deptno = d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 4269077325
------------------------------------------------------------------------------
| Id |
Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | |
14 | 168 | 3
(0)| 00:00:01 |
| 1
| NESTED LOOPS |
| 14 | 168 |
3 (0)| 00:00:01 |
| 2
| TABLE ACCESS FULL| EMP |
14 | 126 | 3
(0)| 00:00:01 |
|* 3
| INDEX UNIQUE SCAN| DEPT_PK | 1 |
3 | 0
(0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
3 -
access("E"."DEPTNO"="D"."DEPTNO")
In Oracle 12c, join
elimination with NOVALIDATE will work only, if QUERY_REWRITE_INTEGRITY parameter set to TRUSTED. This is mentioned in the documentation.
If a foreign key constraint is in NOVALIDATE
state, join elimination is not done when QUERY_REWRITE_INTEGRITY=enforced. This
means that queries with joins over a foreign key constraint that is in RELY
NOVALIDATE state can potentially take longer to parse and execute as the
optimizer does not trust the RELY.
demo@PDB1> show parameter
query_rewrite_integrity
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
query_rewrite_integrity string enforced
demo@PDB1> alter session set
query_rewrite_integrity=Trusted;
Session altered.
demo@PDB1> set autotrace traceonly explain
demo@PDB1> select ename
2 from emp e, dept d
3 where e.deptno = d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id |
Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 |
SELECT STATEMENT | |
14 | 84 | 3
(0)| 00:00:01 |
| 1
| TABLE ACCESS FULL| EMP | 14
| 84 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- rely
constraint used for this statement
demo@PDB1>
In Summary foreign
key constraints in data warehouses are often defined with NOVALIDATE. To enable
query transformation such as join elimination and query rewrite, the
constraints have to be set to reliable with RELY keyword. Additionally, you
have to take care that the parameter QUERY_REWRITE_INTEGRITY
is set to TRUSTED on an Oracle 12c
environment, otherwise join elimination will not work anymore.
No comments:
Post a Comment