This is version 12.1.0.2 of oracle Exadata. And i am seeing below query is actually going for a NESTED LOOP OUTER path and having no such possible index its causing the query to run longer as because it scan/drive the table INV_TAB as FULL for each record in STAGE_TAB. But i was thinking why its not going for a "HASH JOIN OUTER" rather. I found its the last line of the inner query("AND ( INV.COMT_3TXT = STG.STRD_TYP OR ( COMT_3TXT = 'YYY' AND STG.STRD_TYP = 'XXXXXXX'))" which makes it difficult for the optimizer to opt HASH JOIN OUTER path of execution. Even if i forced the query with USE_HASH hint, it still not following the HASH JOIN OUTER path of execution. I executed the query without that specific part("AND ( INV.COMT_3TXT = STG.STRD_TYP OR ( COMT_3TXT = 'YYY' AND STG.STRD_TYP = 'XXXXXXX'))", i am seeing the query finishing in quick time by following the "HASH JOIN OUTER" path of execution.
So want to understand if there exists any bug or restriction for the HASH JOIN OUTER?
SELECT * FROM (STAGE_TAB STG
LEFT JOIN INV_TAB INV
ON INV.ECD = STG.ECD
AND INV.O_CD = STG.SCD
AND INV.ST_CD = STG.DCD
AND INV.SB_ID = STG.SUB_ID
AND INV.SRT_CD = STG.TR_TYP
AND ROUND (INV.OC_AMT, 2) = ROUND (STG.SC_AMT, 2)
AND INV.TSR_DATE IS NULL
AND NVL (ADD1, 'X') = NVL (P_TYP_CD, 'X')
AND ( INV.COMT_3TXT = STG.STRD_TYP OR ( COMT_3TXT = 'YYY' AND STG.STRD_TYP = 'XXXXXXX'))
)
WHERE ID = :b1;
Global Information
------------------------------
Status : EXECUTING
Instance ID : 2
SQL ID : 6muuaygcrtppd
SQL Execution ID : 33554432
Execution Started : 03/15/2019 11:33:02
First Refresh Time : 03/15/2019 11:33:02
Last Refresh Time : 03/15/2019 11:52:09
Duration : 1147s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 41
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B1 | 1 | VARCHAR2(4001) | 99999999 |
========================================================================================================================
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 1141 | 1132 | 9.24 | 41 | 245M |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=158619230)
======================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
======================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 1148 | +0 | 1 | 203K | 0.18 | Cpu (2) |
| -> 1 | NESTED LOOPS OUTER | | 3415 | 72M | 1148 | +0 | 1 | 203K | | |
| -> 2 | TABLE ACCESS STORAGE FULL | STAGE_TAB | 3415 | 102 | 1148 | +0 | 1 | 3173 | | |
| -> 3 | VIEW | VW_LAT_9B1B8082 | 1 | 21048 | 1148 | +0 | 3173 | 203K | | |
| -> 4 | TABLE ACCESS STORAGE FULL | INV_TAB | 1 | 21048 | 1148 | +0 | 3173 | 203K | 99.82 | Cpu (1139) |
======================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("STG"."ID"=TO_NUMBER(:B1))
filter("STG"."ID"=TO_NUMBER(:B1))
4 - storage("INV"."TSR_DATE" IS NULL)
filter("INV"."TSR_DATE" IS NULL AND "INV"."SB_ID"="STG"."SUB_ID" AND
"INV"."O_CD"="STG"."SCD" AND "INV"."SB_ID" IS NOT NULL AND
"INV"."ST_CD"="STG"."DCD" AND "INV"."ECD"="STG"."ECD" AND
"INV"."O_CD" IS NOT NULL AND "INV"."SRT_CD"="STG"."TR_TYP" AND
("INV"."COMT_3TXT" IS NOT NULL AND "INV"."COMT_3TXT"="STG"."STRD_TYP
" OR "STG"."STRD_TYP"='XXXXXXX' AND "COMT_3TXT"='YYY') AND
ROUND("INV"."OC_AMT",2)=ROUND("STG"."SC_AMT",2) AND
NVL("ADD1",'X')=NVL("P_TYP_CD",'X'))
| --By removing the last line of the inner query | |
SELECT * FROM (STAGE_TAB STG
LEFT JOIN INV_TAB INV
ON INV.ECD = STG.ECD
AND INV.O_CD = STG.SCD
AND INV.ST_CD = STG.DCD
AND INV.SB_ID = STG.SUB_ID
AND INV.SRT_CD = STG.TR_TYP
AND ROUND (INV.OC_AMT, 2) = ROUND (STG.SC_AMT, 2)
AND INV.TSR_DATE IS NULL
AND NVL (ADD1, 'X') = NVL (P_TYP_CD, 'X')
/*AND ( INV.COMT_3TXT = STG.STRD_TYP OR ( COMT_3TXT = 'YYY' AND STG.STRD_TYP = 'XXXXXXX'))*/
)
WHERE ID = :b1;
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL ID : 5qmxj34jwmyhb
SQL Execution ID : 33554433
Execution Started : 03/15/2019 11:22:12
First Refresh Time : 03/15/2019 11:22:12
Last Refresh Time : 03/15/2019 11:22:50
Duration : 38s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 175
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B1 | 1 | VARCHAR2(4001) | 99999999 |
========================================================================================================================
Global Stats
============================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets |
============================================================
| 2.49 | 2.24 | 0.00 | 0.25 | 175 | 77675 |
============================================================
SQL Plan Monitoring Details (Plan Hash Value=4179437095)
===========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===========================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 39 | +0 | 1 | 869K | | 100.00 | Cpu (1) |
| | | | | | | | | | | | SQL*Net more data to client (1) |
| 1 | HASH JOIN OUTER | | 3495 | 21128 | 39 | +0 | 1 | 869K | 5M | | |
| 2 | TABLE ACCESS STORAGE FULL | STAGE_TAB | 3435 | 102 | 1 | +0 | 1 | 13581 | | | |
| 3 | TABLE ACCESS STORAGE FULL | INV_TAB | 814K | 21023 | 39 | +0 | 1 | 1M | | | |
===========================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("INV"."ECD"(+)="STG"."ECD" AND "INV"."O_CD"(+)="STG"."SCD" AND "INV"."ST_CD"(+)="STG"."DCD" AND "INV"."SB_ID"(+)="STG"."SUB_ID" AND "INV"."SRT_CD"(+)="STG"."TR_TYP" AND
ROUND("INV"."OC_AMT"(+),2)=ROUND("STG"."SC_AMT",2) AND NVL("ADD1",'X')=NVL("P_TYP_CD"(+),'X'))
2 - storage("STG"."ID"=TO_NUMBER(:B1))
filter("STG"."ID"=TO_NUMBER(:B1))
3 - storage("INV"."TSR_DATE"(+) IS NULL AND "INV"."SB_ID"(+) IS NOT NULL AND "INV"."O_CD"(+) IS NOT NULL)
filter("INV"."TSR_DATE"(+) IS NULL AND "INV"."SB_ID"(+) IS NOT NULL AND "INV"."O_CD"(+) IS NOT NULL)