Hello Experts,
There are two queries, one which uses INNER JOIN and other query which is same but with LEFT OUTER JOIN.
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Query - 1
EXPLAIN PLAN FOR
SELECT ....
FROM entry list_0
INNER JOIN title title_1
ON list_0.object_id = title_1.id
INNER JOIN pack pack_2
ON pack_2.id = title_1.id
INNER JOIN price pack_3
ON pack_2.pack_id = pack_3.pack_id
AND ( pack_3.flag_1 = 1
OR pack_3.flag_2 = 1
OR pack_3.flag_3 = 1
)
LEFT OUTER JOIN popular title_2
ON title_1.id = title_2.id
LEFT OUTER JOIN attirbute title_3
ON title_1.id = title_3.id
WHERE list_0.list_id = 276
AND list_0.sequence_num <= 1
ORDER BY list_0.sequence_num ASC
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
PLAN_TABLE_OUTPUT
Plan hash value: 616187189
------------------------------------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)| TIME |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 16 (7)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 130 | 16 (7)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 130 | 15 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 115 | 14 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 103 | 13 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 95 | 12 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 79 | 11 (0)| 00:00:01 |
|* 7 | MAT_VIEW ACCESS BY INDEX ROWID| ENTRY | 1 | 15 | 10 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_ENTRY_ID | 404 | | 2 (0)| 00:00:01 |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID| TITLE | 1 | 64 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_TITLE1 | 1 | | 1 (0)| 00:00:01 |
| 11 | MAT_VIEW ACCESS BY INDEX ROWID | ATTIRBUTE | 1 | 16 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_ATTIRBUTE | 1 | | 1 (0)| 00:00:01 |
| 13 | MAT_VIEW ACCESS BY INDEX ROWID | POPULAR | 1 | 8 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_POPULAR | 1 | | 1 (0)| 00:00:01 |
| 15 | MAT_VIEW ACCESS BY INDEX ROWID | PACK | 1 | 12 | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_PACK_ID | 1 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PRICE | 1 | 15 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_PRICE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
7 - FILTER("LIST_0"."SEQUENCE_NUM"<=1)
8 - ACCESS("LIST_0"."LIST_ID"=276)
10 - ACCESS("LIST_0"."OBJECT_ID"="TITLE_1"."ID")
12 - ACCESS("TITLE_1"."ID"="TITLE_3"."ID"(+))
14 - ACCESS("TITLE_1"."ID"="TITLE_2"."ID"(+))
16 - ACCESS("PACK_2"."ID"="TITLE_1"."ID")
17 - FILTER("PACK_3"."FLAG_1"=1 OR "PACK_3"."FLAG_2"=1 OR
"PACK_3"."FLAG_3"=1)
18 - ACCESS("PACK_2"."PACK_ID"="PACK_3"."PACK_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
125 consistent gets
0 physical reads
0 redo size
781 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
Query - 2
EXPLAIN PLAN FOR
SELECT ....
FROM entry list_0,
title title_1
LEFT OUTER JOIN popular title_2
ON title_1.id = title_2.id
LEFT OUTER JOIN attirbute title_3
ON title_1.id = title_3.id,
pack pack_2,
price pack_3
WHERE list_0.list_id = 276
AND list_0.sequence_num <= 1
AND list_0.object_id = title_1.id
AND pack_2.id = title_1.id
AND pack_2.pack_id = pack_3.pack_id
AND ( pack_3.flag_1 = 1
OR pack_3.flag_2 = 1
OR pack_3.flag_3 = 1
)
ORDER BY list_0.sequence_num ASC
Plan hash value: 616187123
------------------------------------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)| TIME |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 16 (7)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 130 | 16 (7)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 130 | 15 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 115 | 14 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 103 | 13 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 95 | 12 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 79 | 11 (0)| 00:00:01 |
|* 7 | MAT_VIEW ACCESS BY INDEX ROWID| ENTRY | 1 | 15 | 10 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IDX_ENTRY_ID | 404 | | 2 (0)| 00:00:01 |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID| TITLE | 1 | 64 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_TITLE1 | 1 | | 1 (0)| 00:00:01 |
| 11 | MAT_VIEW ACCESS BY INDEX ROWID | ATTIRBUTE | 1 | 16 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_ATTIRBUTE | 1 | | 1 (0)| 00:00:01 |
| 13 | MAT_VIEW ACCESS BY INDEX ROWID | POPULAR | 1 | 8 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_POPULAR | 1 | | 1 (0)| 00:00:01 |
| 15 | MAT_VIEW ACCESS BY INDEX ROWID | PACK | 1 | 12 | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | IDX_PACK_ID | 1 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PRICE | 1 | 15 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_PRICE | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Statistics
---------------------------------------------------------
1 recursive calls
0 db block gets
125 consistent gets
0 physical reads
0 redo size
783 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
I have cheked the explain plan of both queries and it looks similar.
When checked the Trace file ; i could see 1 extra recursive call in LEFT OUTER JOIN.
So does it means INNER JOIN are more faster?
Please let me know.
Thanks...