Comparing two join queries in Oracle

Question

I have 2 queries do the same job:

SELECT * FROM student_info 
INNER JOIN class 
ON student_info.id = class.studentId 
WHERE student_info.name = 'Ken'

SELECT * FROM (SELECT * FROM student_info WHERE name = 'Ken') studInfo
INNER JOIN class 
ON student_info.id = class.studentId

Which one is faster? I guess the second but not sure, I am using Oracle 11g.

UPDATED:

My tables are non-indexed and I confirm two PLAN_TABLE_OUTPUTs are almost same:

Full size image

Answer 1

You'd need to show us the query plans and the execution statistics to be certain. That said, assuming name is indexed and statistics are reasonably accurate, I'd be shocked if the two queries didn't generate the same plan (and, thus, the same performance). With either query, Oracle is free to evaluate the predicate before or after it evaluates the join so it is unlikely that it would choose differently in the two cases.

Answer 2

In the latest versions of Oracle, the optimizer is smart enough to do its job. So it won't matter and both of your queries would be internally optimized to do the task efficiently. Optimizer might do a query re-write and opt an efficient execution plan.

Let's understand this with a small example of EMP and DEPT table. I will use two similar queries like yours in the question.

I will take two cases, first a predicate having a non-indexed column, second with an indexed column.

Case 1 - predicate having a non-indexed column

SQL> explain plan for
  2  SELECT * FROM emp e
  3  INNER JOIN dept d
  4  ON e.deptno = d.deptno
  5  where ename = 'SCOTT';

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    59 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    59 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    39 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

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

   3 - filter("E"."ENAME"='SCOTT')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Note
-----
   - this is an adaptive plan

22 rows selected.

SQL>
SQL> explain plan for
  2  SELECT * FROM (SELECT * FROM emp WHERE ename = 'SCOTT') e
  3  INNER JOIN dept d
  4  ON e.deptno = d.deptno;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3625962092

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    59 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |     1 |    59 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP     |     1 |    39 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

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

   3 - filter("ENAME"='SCOTT')
   4 - access("EMP"."DEPTNO"="D"."DEPTNO")

Note
-----
   - this is an adaptive plan

22 rows selected.

SQL>

Case 2 - predicate having an indexed column

SQL> explain plan for
  2  SELECT * FROM emp e
  3  INNER JOIN dept d
  4  ON e.deptno = d.deptno
  5  where empno = 7788;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2385808155

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    59 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    59 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    39 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

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

   3 - access("E"."EMPNO"=7788)
   5 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

SQL>
SQL> explain plan for
  2  SELECT * FROM (SELECT * FROM emp where empno = 7788) e
  3  INNER JOIN dept d
  4  ON e.deptno = d.deptno;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2385808155

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    59 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    59 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    39 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

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

   3 - access("EMPNO"=7788)
   5 - access("EMP"."DEPTNO"="D"."DEPTNO")

18 rows selected.

SQL>

Is there any difference between the explain plans in each case respectively? No.

Answer 3

I would definitely lean towards the first query.

When selects are nested, Oracle has fewer optimization opportunities. It generally has to evaluate the inner select into a temporary view and then apply the outer select to that. That is rarely faster than a JOIN where Oracle will evaluate everything together.

Showing your EXPLAIN PLAN would provide extra info for us as well.