Oracle constraints (constraints) affect the query plan, oracleconstraints

Oracle constraints (constraints) affect the query plan, oracleconstraints
Original article: http://www.oracle.com/technetwork/issue-archive/2009/09-may/o39asktom-096149.html


How constraints in oracle affect the query plan


It is generally considered that the constraints are only related to data integrity. However, the constraints are also used by the optimizer to optimize the execution plan.
The optimizer takes the following resources as the most input inputs:


1) query to be optimized
2) statistics on all database objects
3) system statistics, if possible (CPU speed, single-block I/O speed, etc. as a measure of physical hardware)
4) initialization parameters
5) Constraints


I often hear that people ignore constraints in Data Warehouses/report systems. The reason is that the data itself is OK and we clean the data (if we enable the constraint, but it turns out they do need data integrity constraints to get better execution plans. A poor execution plan in a data warehouse may take hours or even days to complete. The following example illustrates the importance of constraints to the execution plan:


1. Let's see how the not null constraint affects the execution plan:
Code1: create a data isolation table and view
Drop table t1;
Drop table t2;
Drop view v;


Create table t1
As
Select * from all_objects
Where object_type in ('table', 'view ');




Alter table t1 modify object_type not null;


Alter table t1 add constraint t1_check_otype check (object_type in ('table', 'view '));




Create table t2
As
Select * from all_objects
Where object_type in ('synonym', 'processed ');




Alter table t2 modify object_type not null;


Alter table t2 add constraint t2_check_otype
Check (object_type in ('synonym', 'processed '));




Create or replace view v
As
Select * from t1
Union all
Select * from t2;


Code2: optimize a table
Set autotrace traceonly explain
Select * from v where object_type = 'table ';
Execution Plan
----------------------------------------------------------------------------
Plan hash value: 3982894595


-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-----------------------------------------------------------------------------
| 0 | select statement | 40 | 6320 | 151 (1) | 00:00:02 |
| 1 | VIEW | V | 40 | 6320 | (1) | 00:00:02 |
| 2 | UNION-ALL |
| * 3 | table access full | T1 | 3083 | 475K | 31 (0) | 00:00:01 |
| * 4 | FILTER |
| * 5 | table access full | T2 | 5 | 790 | 12 (1) | 00:00:02 |
-----------------------------------------------------------------------------


Predicate Information (identified by operation id ):
-------------------------------


3-filter ("OBJECT_TYPE" = 'table ')
4-filter (null is not null)
5-filter ("OBJECT_TYPE" = 'table ')


The strange thing is: the data we query only exists in T1, And it is none of T2! In addition, we do NOT specify the condition filter4: null is not null. It IS added to the execution plan!
This condition is never FALSE.


To explain how the not null constraint works, let's look at another example:
Code3:
Drop table t;
Create table t
As
Select * from all_objects;


Create index t_idx on t (object_type );


Exec dbms_stats.gather_table_stats (user, 'T ');


Select count (*) from t;
Execution Plan
----------------------------------------
Plan hash value: 2966233522


-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 1 | 283 (1) | 00:00:04 |
| 1 | sort aggregate | 1 |
| 2 | table access full | T | 68437 | 283 (1) | 00:00:04 |
-------------------------------------------------------------------


The index we created was not used in this plan. The reason is that the object_type column is nullable and the index does not contain all NULL values. Therefore, it cannot be performed based on the index key value.
COUNT operation. If we tell the database: OBJECT_TYPE is not null, the execution plan will change immediately!
Code4:
Alter table t modify object_type not null;
Select count (*) from t;
Execution Plan
------------------------------------------
Plan hash value: 1058879072


------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
------------------------------------------------------------------------
| 0 | select statement | 1 | 54 (2) | 00:00:01 |
| 1 | sort aggregate | 1 |
| 2 | index fast full scan | T_IDX | 68437 | 54 (2) | 00:00:01 |
------------------------------------------------------------------------


Some kind of magic!
The problem is that the added object_type column can be NULL. How can this problem be solved? The answer is that we can create multiple-column indexes (composite indexes). Of course, the object_type ratio is among them.
For example:
Code5:
Drop index t_idx;
Create index t_idx on t (object_type, 0 );
Code6:
Select * from t where object_type is null;
Execution Plan
-----------------------------
Plan hash value: 470836197


Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 101 | 1 (0) | 00:00:01 |
| 1 | table access by index rowid | T | 1 | 101 | 1 (0) | 00:00:01 |
| * 2 | index range scan | T_IDX | 1 | 1 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------


Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_TYPE" is null)



2. check how the primary and foreign key constraints affect the execution plan:
Code7:
Drop table emp;
Drop table dept;
Drop view emp_dept;


Create table emp
As
Select *
From scott. emp;


Create table dept
As
Select *
From scott. dept;


Create or replace view emp_dept
As
Select emp. ename, dept. dname
From emp, dept
Where emp. deptno = dept. deptno;


-- We pretend that EMP and DEPT represent big tables!
Begin
Dbms_stats.set_table_stats
(User, 'emp', numrows => 1000000, numblks => 100000 );
Dbms_stats.set_table_stats
(User, 'dept', numrows => 100000, numblks => 10000 );
End;
/

Code8:
SQL> select ename from emp_dept;


Execution Plan
-----------------------------
Plan hash value: 615168685


Bytes ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------
| 0 | select statement | 31515 k | 31M | (1) | 00:06:19 |
| * 1 | hash join | 31515 k | 31M | 2448K | (1) | 00:06:19 |
| 2 | table access full | DEPT | 100K | 1269K | 2716 (1) | 00:00:33 |
| 3 | table access full | EMP | 1000K | 19M | 27151 (1) | 00:05:26 |
Bytes ----------------------------------------------------------------------------------------


Predicate Information (identified by operation id ):
---------------------------------------------------
1-access ("EMP". "DEPTNO" = "DEPT". "DEPTNO ")


Here we only query the ename column of the EMP table, and DEPT does not need to be executed. However, DEPTNO is the primary key of the DEPT table, and the foreign key of the EMP table! In this way, the DEPTNO column in the EMP table is not empty. However, we did not specify this layer of relationship,
ORACLE naturally does not know, so we do this:
Alter table dept add constraint dept_pk primary key (deptno );


Alter table emp add constraint emp_fk_dept foreign key (deptno) references dept (deptno );


Select ename from emp_dept;


Execution Plan
------------------------------
Plan hash value: 3956160932


--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 50000 | 976K | 27152 (1) | 00:05:26 |
| * 1 | table access full | EMP | 50000 | 976K | 27152 (1) | 00:05:26 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("EMP". "DEPTNO" is not null)


It works! Added a filter.

3. How does a query with a not null and a foreign key constraint affect the execution plan?
I often use materialized views as "Data Warehouse indexes", and its most important use is as "preanswer ", save the complex and long-running results for a specific table in a permanent table.
To put it bluntly, the query speed is accelerated.
Alter table emp drop constraint emp_fk_dept;


Alter table dept drop constraint dept_pk;


Code10:
Create materialized view mv enable query rewrite
As
Select dept. deptno, dept. dname, count (*) from emp, dept
Where emp. deptno = dept. deptno
Group by dept. deptno, dept. dname;


Begin
Dbms_stats.set_table_stats
(User, 'mv ', numrows => 100000, numblks => 10000 );
End;
/


Code11: materialized view for a query
Select dept. dname, count (*) from emp, dept
Where emp. deptno = dept. deptno and dept. dname = 'sales'
Group by dept. dname;


Execution Plan
------------------------------
Plan hash value: 1703036361


Bytes --------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------
| 0 | select statement | 1000 | 22000 | 2716 (1) | 00:00:33 |
| 1 | sort group by nosort | 1000 | 22000 | 2716 (1) | 00:00:33 |
| * 2 | MAT_VIEW rewrite access full | MV | 1000 | 22000 | 2716 (1) | 00:00:33 |
Bytes --------------------------------------------------------------------------------------------


Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("MV". "DNAME" = 'sales ')


Code12: No materialized view is used for a query.
SQL> select count (*) from emp;


COUNT (*)
--------
14


SQL> select * from table (dbms_xplan.display_cursor );


PLAN_TABLE_OUTPUT
-----------------------
SQL _ID g59vz2u4cu404, child number 1
-----------------------
Select count (*) from emp


Plan hash value: 2083865914


-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time |
-------------------------------------------------------------------
| 0 | select statement | 27142 (100) |
| 1 | sort aggregate | 1 |
| 2 | table access full | EMP | 27142 k | (1) | 00:05:26 |
-------------------------------------------------------------------
14 rows selected.




Obviously, more execution plans should be to query the computed data in the materialized view.
Therefore, we need to tell the database the following:
DEPTNO in DEPT is a primary key
DEPTNO in EMP is a foreign key
DEPTNO in EMP is a not null column


Alter table dept add constraint dept_pk primary key (deptno );


Alter table emp add constraint emp_fk_dept foreign key (deptno) references dept (deptno );


Alter table emp modify deptno not null;


Code13:
SQL> select count (*) from emp;


COUNT (*)
-------
14


SQL> select * from table (dbms_xplan.display_cursor );


PLAN_TABLE_OUTPUT
-----------------------
SQL _ID g59vz2u4cu404, child number 2
-----------------------
Select count (*) from emp


Plan hash value: 1747602359


Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 2716 (100) |
| 1 | sort aggregate | 1 | 13 |
| 2 | MAT_VIEW rewrite access full | MV | 100K | 1269K | 2716 (1) | 00:00:33 |
Bytes --------------------------------------------------------------------------------------




The query rewriting feature of Materialized View reference: http://blog.itpub.net/28719055/viewspace-1258720/

-------------------------------

Dylan Presents.