Why Primary Key Foreign Key Relationship and Join Elimination

The long weekend is always fun, especially when some Oracle Event is scheduled. Last Sunday, I attended awesome "OTNYatra", an event where we can gain knowledge from Top Experts in World, and also connect with Oracle Database professionals and discuss problems, doubts and solutions.

In one of the discussion I was pulled was "Why should we create Primary and Foreign Keys (Constraints) in Database when data consistency is guaranteed by Application?" My answer was simple, it allows Oracle Optimizer (CBO) to have better knowledge of data and generate optimal execution plans, plus never trust application [developer] :)

Without going into the details of discussions, let us run and test some SQLs on my favorite tables EMP and DEPT, which has following Referential Integrity Constraints.
- Primary Key on DEPT.DEPTNO
- Primary Key on EMP.EMPNO
- foreign Key on EMP.DEPTNO referencing DEPT.DEPTNO

Example 1: Get data of all Employees whose department is listed in DEPT table
nimish@garg> select e.* from emp e, dept d where e.deptno = d.deptno;
13 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    13 |   494 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    13 |   494 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("E"."DEPTNO" IS NOT NULL)

As you can see here, Oracle simply removed the DEPT table from execution and added predicate "e.deptno is not null" because Oracle knows that only allowed values in E.DEPTNO are NULL or values from DEPT.DEPTNO.

Example 2: Get data of all Employees whose department is listed in DEPT table, here I tried my SQL with IN clause.
nimish@garg> select e.* from emp e where e.deptno in (select d.deptno from dept d);
13 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    13 |   494 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    13 |   494 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("E"."DEPTNO" IS NOT NULL)

Same result as Example 1. Oracle Optimizer removed DEPT table from execution plan and added "e.deptno is not null".

For my last example I went one more step ahead, and added a NOT NULL constraint on EMP.DEPTNO to give more information to Oracle Database Optimizer.
Example 3: Get data of all Employees whose department is not in DEPT table.
nimish@garg> alter table emp modify deptno not null;
Table altered.

nimish@garg> select e.* from emp e where e.deptno not in (select d.deptno from dept d);
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    38 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    13 |   494 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

In above example, Oracle Optimizer knows that there can not be any row in EMP table which has DEPTNO not present in DEPT table and also EMP.DEPTNO can't be null. So it removed the DEPT table completely.

The three examples we saw are actually of Oracle "Join Elimination" transformation, which is a technique of removing one or more tables are from execution without changing the output. There must be Primary Key and Foreign Key relationship defined among table to allow Oracle to perform "Join Elimination" transformation.

Related Posts:
- Complex View Merging Transformation and ORA-00979 in Oracle 12c
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- How to Get Execution Plan and Statistics of SQL Query
- Top 15 new features of Oracle Database 12.2 for developers

10 comments:

  1. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...

    ReplyDelete
  2. Well it is same good old decision - what is priority for your database design for those tables - INSERT / UPDATE efficiency or data search (SELECT) efficiency. There can be no "always best" strategy.
    Apart from the advantage you have specified - improving data search efficiency, the constraints may also be useful for improving ease and efficiency of data replication operations (e.g. GG replication , logical standby)

    ReplyDelete
  3. My relatives every time say that I am killing my time here at net,
    except I know I am getting explerience everyday by reading such good articles or reviews.

    ReplyDelete
  4. I am sure this article has touched all the internet people,
    its really really pleasant post on building up new website.

    ReplyDelete
  5. Very nice post. I just stumbled upon your weblog and wanted to say that
    I have really enjoyed browsing your blog posts. After all I will be subscribing to your rss feed and I hope you write
    again very soon!

    ReplyDelete
  6. Appreciating the hrd work you put into your website and detailed information you offer.
    It's good to come across a blog every once in a while that isn't the same out of date rejashed information. Excellent read!
    I've bookmarked your site and I'm including your RSS feeds
    to my Google account.

    ReplyDelete
  7. excellent article ! Your previous artcilcle infact about Migrating to NO SQL was the best I liked & infact I quoted that Hotel booking example in one of the meeting where we were discussing NO SQL cons :
    http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html?m=1

    ReplyDelete
  8. can You post an article on "Oracle Stream Replication".

    ReplyDelete
  9. A much more important reason is that it prevents users that can access the database from sqlplus from making incorrect changes. In addition, I have seen very apps so perfectly designed that integrity issues cannot be created due to programming errors or hardware failure in the middle of a transaction.

    ReplyDelete