Syntax performance of INNER JOIN

Question

Is the performance of both these examples the same?

Example 1:

SELECT t1.wanted_1, t2.wanted_2 
FROM table1 t1 
INNER JOIN table2 t2 ON t1.common_col = t2.common_col

Example 2:

SELECT wanted_1, wanted_2 
FROM 
    (SELECT wanted_1, common_col FROM table1) 
INNER JOIN 
    (SELECT wanted_2, common_col FROM table_2) USING(common_col)

I am using example #2 at the moment since I am joining 15+ tables, each table with many unnecessary columns and many rows (1 million+)

Why don't you run these two queries and measure their performance for yourself?? — marc_s, Dec 22 '20 at 10:41

score 4 · Accepted Answer · answered Dec 22 '20 at 10:52

Oracle is smart enough and does not take all columns from table 1 and join them with all columns from table 2 and only presents the few columns you need in the result (what you probably fear)

To see it simple use explain plan of the query

EXPLAIN PLAN  SET STATEMENT_ID = 'jara1' into   plan_table  FOR   
SELECT t1.wanted_1, t2.wanted_2 FROM table1 t1 INNER JOIN table2 t2 ON t1.common_col=t2.common_col
;

SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'jara1','ALL'))

You get this result

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    52 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |        |     1 |    52 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TABLE1 |     1 |    26 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TABLE2 |     1 |    26 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$58A6D7F6
   2 - SEL$58A6D7F6 / T1@SEL$1
   3 - SEL$58A6D7F6 / T2@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."COMMON_COL"="T2"."COMMON_COL")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "T1"."WANTED_1"[NUMBER,22], "T2"."WANTED_2"[NUMBER,22]
   2 - "T1"."WANTED_1"[NUMBER,22], "T1"."COMMON_COL"[NUMBER,22]
   3 - "T2"."WANTED_2"[NUMBER,22], "T2"."COMMON_COL"[NUMBER,22]

The most importat information is the column projection part where you can see that only referenced columns are considered.

You may also check the execution plan of the second query to see that except for minor details you get the same result.

So the consequence IMO would be that despite less readable query you will not see any significant difference while using query 2.

You own test should confim it.

Syntax performance of INNER JOIN

1 Answers1