Hi we are using version 11.2.0.4 of Oracle Exadata. Below query is running for ~7-8 minutes. And the way this query is written here such that , table CNTRL_TAB is a just a control table so there is no join between the main table- TAB1 and this control table. And TAB1 is list partitioned on column DATE_KEY. And index TAB1_PK is composite index on (FCT_KEY,DATE_KEY).
And i saw the execution path , its going for a "INDEX STORAGE FAST FULL SCAN" path by default. but i see we are only picking up the MAX value of a column which is leading column of an unique index. Now when i tried putting hint INDEX(TAB1 TAB1_PK), it went for "INDEX FULL SCAN (MIN/MAX)" and finished in seconds. So wanted to understand why by default optimizer is not following the MIN/MAX optimization? From the plan it shows the expected costing of "INDEX FULL SCAN (MIN/MAX)" coming as ~27million as compared to the cost of "INDEX STORAGE FAST FULL SCAN" in the default path which is ~7million for same ~8billion rows estimated in both of the cases. But in case of INDEX FULL SCAN (MIN/MAX) the number of actual rows coming as ~496.
Another way i tried tweaking the query, using exists operator then i see its following MIN/MAX optimization an finishing i seconds. Why its not following that path by default?
Also the surprising part is even by default its using the same index but to be exact its doing "INDEX STORAGE FAST FULL SCAN". So how-come if i hint the query using same index(means i have not supplied any additional input/influencer to the query) its opting "INDEX FULL SCAN (MIN/MAX)"?
Default execution path:-
SQL Text
------------------------------
SELECT MAX (TAB1.FCT_KEY) AS MX_VAL FROM TAB1,
CNTRL_TAB WHERE CNTRL_TAB.CNTRL_ID = 111 AND CNTRL_TAB.STATUS IS NOT NULL
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 3
SQL ID : 2y1x33t9vf9xx
SQL Execution ID : 50331648
Execution Started : 07/09/2020 09:08:48
First Refresh Time : 07/09/2020 09:08:52
Last Refresh Time : 07/09/2020 09:16:38
Duration : 470s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
==============================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
==============================================================================================================
| 470 | 446 | 15 | 0.34 | 0.10 | 10 | 1 | 32M | 514K | 245GB | 27.54% |
==============================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3656453152)
=================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
=================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 467 | +4 | 1 | 1 | | | | | | |
| 1 | SORT AGGREGATE | | 1 | | 468 | +3 | 1 | 1 | | | | | 40.21 | Cpu (189) |
| 2 | NESTED LOOPS | | 8G | 7M | 407 | +4 | 1 | 4G | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | CNTRL_TAB | 1 | 1 | 1 | +4 | 1 | 1 | | | | | | |
| 4 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 467 | +4 | 1 | 1 | | | | | | |
| 5 | PARTITION LIST ALL | | 8G | 7M | 407 | +4 | 1 | 4G | | | | | | |
| 6 | INDEX STORAGE FAST FULL SCAN | TAB1_PK | 8G | 7M | 471 | +1 | 579 | 4G | 510K | 244GB | 27.54% | 15M | 59.79 | Cpu (262) |
| | | | | | | | | | | | | | | reliable message (9) |
| | | | | | | | | | | | | | | cell single block physical read (1) |
| | | | | | | | | | | | | | | cell smart index scan (9) |
=================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)
4 - access("CNTRL_TAB"."CNTRL_ID"=111)
With hints:-
SQL Text
------------------------------
SELECT/*+ INDEX(TAB1 TAB1_PK) */ MAX (TAB1.FCT_KEY) AS MX_VAL FROM TAB1, CNTRL_TAB WHERE CNTRL_TAB.CNTRL_ID = 111 AND CNTRL_TAB.STATUS IS NOT NULL
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 3
SQL ID : 5xrjk1zynqyt8
SQL Execution ID : 50331648
Execution Started : 07/09/2020 09:19:39
First Refresh Time : 07/09/2020 09:19:39
Last Refresh Time : 07/09/2020 09:19:39
Duration : .002947s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
======================================
| Elapsed | Cpu | Fetch | Buffer |
| Time(s) | Time(s) | Calls | Gets |
======================================
| 0.00 | 0.00 | 1 | 1615 |
======================================
SQL Plan Monitoring Details (Plan Hash Value=2283604281)
==========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |
| 2 | NESTED LOOPS | | 8G | 27M | 1 | +0 | 1 | 496 | | |
| 3 | TABLE ACCESS BY INDEX ROWID | CNTRL_TAB | 1 | 1 | 1 | +0 | 1 | 1 | | |
| 4 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 1 | +0 | 1 | 1 | | |
| 5 | PARTITION LIST ALL | | 8G | 27M | 1 | +0 | 1 | 496 | | |
| 6 | FIRST ROW | | 8G | 27M | 1 | +0 | 579 | 496 | | |
| 7 | INDEX FULL SCAN (MIN/MAX) | TAB1_PK | 8G | 27M | 1 | +0 | 579 | 496 | | |
==========================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)
4 - access("CNTRL_TAB"."CNTRL_ID"=111)
********************* Tweaking the query using Exists Operator*********
SELECT MAX (TAB1.FCT_KEY) AS MX_VAL
FROM TAB1
where exists (select 1 from CNTRL_TAB
WHERE CNTRL_TAB.CNTRL_ID = 111
AND CNTRL_TAB.STATUS IS NOT NULL)
Execution Plan
----------------------------------------------------------
Plan hash value: 2799955177
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 1160 (0)| 00:00:14 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION LIST ALL | | 1 | 8 | 1159 (0)| 00:00:14 | 579 | 1 |
| 4 | FIRST ROW | | 1 | 8 | 1159 (0)| 00:00:14 | | |
| 5 | INDEX FULL SCAN (MIN/MAX)| TAB1_PK | 1 | 8 | 1159 (0)| 00:00:14 | 579 | 1 |
|* 6 | TABLE ACCESS BY INDEX ROWID| CNTRL_TAB | 1 | 6 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 0 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "USER1"."CNTRL_TAB" "CNTRL_TAB" WHERE
"CNTRL_TAB"."CNTRL_ID"=111 AND "CNTRL_TAB"."STATUS" IS NOT NULL))
6 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)
7 - access("CNTRL_TAB"."CNTRL_ID"=111)