ORACLE Driving Tables

How ORACLE decides Driving Tables and how it traverse rest of the things after From clause?

The driving table is the table that is joined to other tables in an SQL query and the first table accessed when a query will run. This understanding will help you tune your Query in a better way.

It’s not like that your driving table will always be the first table in your From clause. In case of Rule Based Optimizer the From Clause would be traversed from Right to Left however this would not be the case with Cost Based Optimizer.

Let’s see how From clause will work in case we will force RBO to work on Execution Plan.

SQL> SELECT /*+ RULE */ * From EMPLOYEE_DETAILS E, DEPARTMENT D
Where E.DEPTNO=D.DEPTNO;

Execution Plan
----------------------------------------------------------
Plan hash value: 1994740439
------------------------------------------------
| Id  | Operation           | Name             |
------------------------------------------------
|   0 | SELECT STATEMENT    |                  |
|   1 |  MERGE JOIN         |                  |
|   2 |   SORT JOIN         |                  |
|   3 |    TABLE ACCESS FULL|DEPARTMENT        |
|*  4 |   SORT JOIN         |                  |
|   5 |    TABLE ACCESS FULL|EMPLOYEE_DETAILS|

------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - rule based optimizer used (consider using cbo)

Always put smallest tables at the end in your From clause.

Let’s create Index on DEPARTMENT(DEPTNO) and check the Execution Plan with Changed order.

SQL> Create Index INDX_DEPT on DEPARTMENT(DEPTNO);
Index created.
SQL> SELECT /*+ RULE */ * From DEPARTMENT D,EMPLOYEE_DETAILS E
 Where E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1430033380
---------------------------------------------------------
| Id  | Operation                    | Name             |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |
|   1 |  NESTED LOOPS                |                  |
|   2 |   NESTED LOOPS               |                  |
|   3 |    TABLE ACCESS FULL         | EMPLOYEE_DETAILS |
|*  4 |    INDEX RANGE SCAN          | INDX_DEPT        |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - rule based optimizer used (consider using cbo)

So, in this case EMPLOYEE_DETAILS table was taken as Driving Table as we created Index on DEPARTMENT(DEPTNO) Table. So, ORACLE will fetch all the ROWS from EMPLOYEE_DETAILS Table (Full Scan) and will compare it with DEPARTMENT Table and display the result.

Let’s check it with CBO with the same Order.

SQL> SELECT * From DEPARTMENT D, EMPLOYEE_DETAILS E
Where  E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1365186588
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |     5 |   125 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | INDX_DEPT        |     5 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                  |     8 |   336 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEE_DETAILS |     8 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)

In this case, ORACLE takes DEPARTMENT as Driving Table and then search for matching records in EMPLOYEE_DETAILS Table.

Now, let’s explicitly force Optimizer to follow the Order.

SQL> SELECT /*+ ordered */ * From EMPLOYEE_DETAILS E, DEPARTMENT D
 Where  E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 124983262
---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     8 |   536 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |                  |     8 |   536 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEE_DETAILS |     8 |   336 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPARTMENT       |     5 |   125 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)

If you change the Table ordering, then it will use the same order and first table will become your Driving Table.

SQL> SELECT /*+ ordered */ * From DEPARTMENT D, EMPLOYEE_DETAILS E
 Where  E.DEPTNO=D.DEPTNO;
Execution Plan
----------------------------------------------------------
Plan hash value: 1365186588
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |                  |     8 |   536 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |     5 |   125 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | INDX_DEPT        |     5 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |                  |     8 |   336 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEE_DETAILS |     8 |   336 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)

So, ORACLE will use the Driving table which is based on the Cost and Cardinality and you can force the Optimizer to use the Driving Table as per your choice to make the Better Execution Plan as per your requirement.