Oracle SQL: most efficient way to perform filtering

Question

As I start writing SQL queries on big tables, I wonder which one is the most efficient. To me they all look the same. Looking for an explanation of how SQL will decompose and execute these queries as well

Option 1:

select a.*
from table_a a
where a.column in (select filter from table_b)

Option 2:

select a.*
from table_a a, table_b b
where a.column=b.filter

Option 3:

select a.*
from table_a a
inner join table_b b
on a.column=b.filter

Answer 1

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.

Query 1 :

SQL> explain plan for
  2  select a.*
  3  from emp a
  4  WHERE A.deptno IN (SELECT deptno FROM dept);

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   546 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

   1 - filter("A"."DEPTNO" IS NOT NULL)

13 rows selected.

SQL>

Query 2 :

SQL> explain plan for
  2  SELECT A.*
  3  FROM emp A, dept b
  4  where a.deptno=b.deptno;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   546 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

   1 - filter("A"."DEPTNO" IS NOT NULL)

13 rows selected.

SQL>

Query 3 :

SQL> explain plan for
  2  select a.*
  3  from emp a
  4  INNER JOIN dept b
  5  ON A.deptno=b.deptno;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   546 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

   1 - filter("A"."DEPTNO" IS NOT NULL)

13 rows selected.

SQL>

So, the optimizer chose the most efficient execution plan irrespective of the way you wrote the queries differently. Unless you force the optimizer to chose a different plan using hints etc. You would always see there are executed using the same plan.