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