Constraint to Validate Data and Optimize SQL - Manual Partition

I always suggest developers to add constraints on tables in Oracle Database, as constraints not only validate the data, but they also helps Oracle Database Optimizer to choose better execution plan and hence tune the SQL. Surprised? Let me share my experience with constraints in Oracle Database, when I first came to know the actual power of constraint. That time I was working with Oracle 11g Standard Edition, as we all know in Partition feature is not available with Oracle 11g Standard Edition. I was asked to design manual partitions for "WORKORDER" as per the workorder status column having 4 values. ('New', 'WIP', 'Closed', 'Cancelled').

With this post, I am trying to explain how Oracle Database can use constraint to optimize the a complex SQL. For the demo, I am using my favorite table SCOTT.EMP, and will be creating the partitions as per department name. I have created 3 tables from SCOTT.EMP, each of which contains data for ACCOUNTING, RESEARCH and SALES department respectively.

SQL> create table emp_accounting as
  2  select empno, ename, job, hiredate, sal, dname
  3  from emp e, dept d
  4  where e.deptno = d.deptno
  5  and dname = 'ACCOUNTING';
Table created.

SQL> create table emp_research as
  2  select empno, ename, job, hiredate, sal, dname
  3  from emp e, dept d
  4  where e.deptno = d.deptno
  5  and dname = 'RESEARCH';
Table created.

SQL> create table emp_sales as
  2  select empno, ename, job, hiredate, sal, dname
  3  from emp e, dept d
  4  where e.deptno = d.deptno
  5  and dname = 'SALES';
Table created.

Now, we created a view by doing UNION of all 3 tables, on which user or application will run the SELECT SQL with filter on DNAME column and the Oracle Database should only use the relevant partition (table).

SQL> create view emp_view as
  2  select * from emp_accounting
  3  union all
  4  select * from emp_research
  5  union all
  6  select * from emp_sales;
View created.

SQL> set autot trace

SQL> select * from emp_view
  2  where dname = 'RESEARCH';

Execution Plan
----------------------------------------------------------
Plan hash value: 965804354
--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |     5 |   210 |     3   (0)| 00:00:01 |
|   1 |  VIEW               | EMP_VIEW       |     5 |   210 |     3   (0)| 00:00:01 |
|   2 |   UNION-ALL         |                |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| EMP_ACCOUNTING |     1 |    41 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP_RESEARCH   |     5 |   195 |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP_SALES      |     1 |    37 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("DNAME"='RESEARCH')
   4 - filter("DNAME"='RESEARCH')
   5 - filter("DNAME"='RESEARCH')

As we can see in above example, filter dname = 'RESEARCH' is doing FULL TABLE SCAN for all 3 tables. Even if we create indexes on DNAME of all 3 tables, it will reduce the SELECT STATEMENT cost, but still touch the indexes and table, along with putting the overhead on all the DML statements. You can create indexes and test it out if you want.

In process of Optimizing our SELECT STATEMENT on VIEW (custom-partition), we tested CONSTRAINT for our need, and were simply amazed.

SQL> alter table emp_sales
  2  add constraint emp_sales_ck
  3  check (dname = 'SALES');
Table altered.

SQL> alter table emp_research
  2  add constraint emp_research_ck
  3  check (dname = 'RESEARCH');
Table altered.

SQL> alter table emp_accounting
  2  add constraint emp_accounting_ck
  3  check (dname = 'ACCOUNTING');
Table altered.

SQL> select * from emp_view
  2  where dname = 'RESEARCH';
Execution Plan
----------------------------------------------------------
Plan hash value: 2539605777
---------------------------------------------------------------------------------------
| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |     5 |   210 |     3   (0)| 00:00:01 |
|   1 |  VIEW                | EMP_VIEW       |     5 |   210 |     3   (0)| 00:00:01 |
|   2 |   UNION-ALL          |                |       |       |            |          |
|*  3 |    FILTER            |                |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| EMP_ACCOUNTING |     1 |    41 |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL | EMP_RESEARCH   |     5 |   195 |     2   (0)| 00:00:01 |
|*  6 |    FILTER            |                |       |       |            |          |
|*  7 |     TABLE ACCESS FULL| EMP_SALES      |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(NULL IS NOT NULL)
   4 - filter("DNAME"='RESEARCH')
   5 - filter("DNAME"='RESEARCH')
   6 - filter(NULL IS NOT NULL)
   7 - filter("DNAME"='RESEARCH')

As you can see in above example, Oracle Database Optimizer simply added filter "NULL IS NOT NULL" for EMP_ACCOUNTING and EMP_SALES, which will simply bypass these tables. The CONSTRAINT on DNAME columns of each table allowed Oracle Database to rewrite the SQL, and change the access path to generate the better execution plan.

I hope you have enjoyed reading this post.

Following are the other posts on Optimization of SQL statement.
- Query optimization tips for Oracle
- Why prefer COALESCE over NVL
- Datatype is Important for Good Execution Plan and SQL Performance
- Why Primary Key Foreign Key Relationship and Join Elimination
- Why my distinct query is not using index?
- Avoiding unnecessary function calls to optimize SQL statements
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Foreign Key in Oracle Data Warehouse - Best Practice
- Tune Complete Refresh of Materialized View by atomic_refresh=>false
- Oracle SQL Developer - Autotrace - Insufficient Privileges