Hi Tom,
i have updated the plan.i was unaware how to submit the further information on the questions asked, thats why put the questions again.
this is with reference to question asked at below link
https://asktom.oracle.com/pls/apex/asktom.search?tag=index-scan-on-timestamp-column Attaching the explain of actual query i am using after changing the index order as you suggested.
WITH LABEL_COUNT AS (
select /*+ gather_plan_statistics */ CLPT.CLIENT_ID AS CLIENT_ID , CLPT.GROWER_ID AS CGRW_ID,CLPT.SHIPPER_LOT_ID,SUM(CLPT.LABEL_COUNT) AS VALUE
FROM T_CASE_LABEL_PRINT_TRNS CLPT
WHERE CLPT.CLIENT_ID = 3104 and CLPT.STATUS = 1
AND CLPT.CREATED_ON >= TO_TIMESTAMP('01/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/29/2018 23:59:59', 'MM/DD/YYYY HH24:MI:SS')
GROUP BY CLPT.CLIENT_ID ,CLPT.GROWER_ID,CLPT.SHIPPER_LOT_ID
)
SELECT NVL(SLGRWR.ID, 0) AS entityId, NVL(SLGRWR.NAME, 'Unspecified') AS label , SUM(LC.VALUE)
FROM LABEL_COUNT LC
INNER JOIN T_SHIPPER_LOTS SL ON LC.SHIPPER_LOT_ID = SL.ID AND LC.CLIENT_ID = SL.CLIENT_ID
LEFT JOIN T_GROWERS SLGRWR ON (LC.CGRW_ID = SLGRWR.ID OR SL.GROWER_ID = SLGRWR.ID ) AND LC.CLIENT_ID=SLGRWR.CLIENT_ID
GROUP BY NVL(SLGRWR.ID, 0), NVL(SLGRWR.NAME, 'Unspecified')
ORDER BY SUM(LC.VALUE) DESC
FETCH FIRST 10 ROWS ONLY;
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
SQL_ID 5vw2uz51qv2w9, child number 1
-------------------------------------
WITH LABEL_COUNT AS ( select /*+
gather_plan_statistics */ CLPT.CLIENT_ID AS CLIENT_ID ,
CLPT.GROWER_ID AS CGRW_ID,CLPT.SHIPPER_LOT_ID,SUM(CLPT.LABEL_COUNT) AS
VALUE FROM T_CASE_LABEL_PRINT_TRNS CLPT WHERE
CLPT.CLIENT_ID = 3104 and CLPT.STATUS = 1 AND CLPT.CREATED_ON
>= TO_TIMESTAMP('01/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/29/2018 23:59:59',
'MM/DD/YYYY HH24:MI:SS') GROUP BY CLPT.CLIENT_ID
,CLPT.GROWER_ID,CLPT.SHIPPER_LOT_ID ) SELECT
NVL(SLGRWR.ID, 0) AS entityId, NVL(SLGRWR.NAME, 'Unspecified') AS label
, SUM(LC.VALUE) FROM LABEL_COUNT LC INNER JOIN
T_SHIPPER_LOTS SL ON LC.SHIPPER_LOT_ID = SL.ID AND LC.CLIENT_ID =
SL.CLIENT_ID LEFT JOIN T_GROWERS SLGRWR ON (LC.CGRW_ID =
SLGRWR.ID OR SL.GROWER_ID = SLGRWR.ID ) AND
LC.CLIENT_ID=SLGRWR.CLIENT_ID GROUP BY NVL(SLGRWR.ID, 0),
NVL(SLGRWR.NAME, 'Unspecified') ORDER
Plan hash value: 3231218950
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:17.98 | 139K| 122K| | | |
| 1 | SORT ORDER BY | | 1 | 10 | 10 |00:00:17.98 | 139K| 122K| 2048 | 2048 | 2048 (0)|
|* 2 | VIEW | | 1 | 10 | 10 |00:00:17.98 | 139K| 122K| | | |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 3 | 10 |00:00:17.98 | 139K| 122K| 2048 | 2048 | 2048 (0)|
| 4 | HASH GROUP BY | | 1 | 3 | 78 |00:00:17.98 | 139K| 122K| 898K| 898K| 1361K (0)|
| 5 | NESTED LOOPS OUTER | | 1 | 16538 | 16538 |00:00:17.54 | 139K| 122K| | | |
|* 6 | HASH JOIN | | 1 | 16538 | 16538 |00:00:17.31 | 125K| 122K| 2055K| 1934K| 1768K (0)|
| 7 | VIEW | | 1 | 16539 | 16539 |00:00:17.06 | 122K| 122K| | | |
| 8 | HASH GROUP BY | | 1 | 16539 | 16539 |00:00:17.04 | 122K| 122K| 2064K| 1501K| 1594K (0)|
|* 9 | TABLE ACCESS FULL | T_CASE_LABEL_PRINT_TRNS | 1 | 184K| 168K|00:00:19.50 | 122K| 122K| | | |
| 10 | INDEX FAST FULL SCAN | INDX_SLOT_ID_CLN_GID | 1 | 741K| 741K|00:00:00.42 | 2264 | 0 | | | |
| 11 | VIEW | VW_LAT_A8413AFA | 16538 | 1 | 2384 |00:00:00.29 | 14306 | 0 | | | |
| 12 | CONCATENATION | | 16538 | | 2384 |00:00:00.25 | 14306 | 0 | | | |
|* 13 | FILTER | | 16538 | | 2384 |00:00:00.06 | 7153 | 0 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| T_GROWERS | 2384 | 1 | 2384 |00:00:00.03 | 7153 | 0 | | | |
|* 15 | INDEX UNIQUE SCAN | PK_GROWER | 2384 | 1 | 2384 |00:00:00.01 | 4769 | 0 | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | T_GROWERS | 16538 | 1 | 0 |00:00:00.11 | 7153 | 0 | | | |
|* 17 | INDEX UNIQUE SCAN | PK_GROWER | 16538 | 1 | 2384 |00:00:00.04 | 4769 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("from$_subquery$_007"."rowlimit_$$_rownumber"<=10)
3 - filter(ROW_NUMBER() OVER ( ORDER BY SUM("LC"."VALUE") DESC )<=10)
6 - access("LC"."SHIPPER_LOT_ID"="SL"."ID" AND "LC"."CLIENT_ID"="SL"."CLIENT_ID")
9 - filter(("CLPT"."CREATED_ON">=TIMESTAMP' 2018-01-01 00:00:00.000000000' AND "CLPT"."CLIENT_ID"=3104 AND "CLPT"."CREATED_ON"<=TIMESTAMP'
2018-12-29 23:59:59.000000000' AND "CLPT"."STATUS"=1))
13 - filter("SL"."GROWER_ID" IS NOT NULL)
14 - filter("LC"."CLIENT_ID"="SLGRWR"."CLIENT_ID")
15 - access("SL"."GROWER_ID"="SLGRWR"."ID")
16 - filter(("LC"."CLIENT_ID"="SLGRWR"."CLIENT_ID" AND (LNNVL("SL"."GROWER_ID" IS NOT NULL) OR LNNVL("SL"."GROWER_ID"="SLGRWR"."ID"))))
17 - access("LC"."CGRW_ID"="SLGRWR"."ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- statistics feedback used for this statement
- 1 Sql Plan Directive used for this statement
total count in CLPT table with the date and status filter is :168031.
-- IF I USE SKIP SCAN ON INDEX: IDX_CSLBLPRNTTRX_CIDDIDCON(CLIENT_ID, DEVICE_ID, CREATED_ON) AS A HINTS IT WORKS FINE IN TERMS OF RESPONSE TIME BUT FOR FEW CLIENTS COST IS MUCH MORE. I AM NOT ABLE TO DECIDE WEATHER TO USE SKIP SCAN HINT OR NOT.
To clarify, you created an index on:
(CLIENT_ID,STATUS,CREATED_ON)
right?
Pretty much all the time is going on full scanning T_CASE_LABEL_PRINT_TRNS. The row estimates for this are accurate enough (expecting 184K rows and getting 168K).
So the optimizer thinks that the full table scan is the fastest way to do this. If the index is in fact faster, you want to help the optimizer discover this.
You may be able to make the index on (CLIENT_ID,STATUS,CREATED_ON) more attractive to the optimizer by:
- Compressing the leading columns (alter index ... rebuild compress 2)
- Setting TABLE_CACHED_BLOCKS parameter to a value ~16 and regathering stats:
exec dbms_stats.set_table_prefs(user, 'T_CASE_LABEL_PRINT_TRNS', pname=>'TABLE_CACHED_BLOCKS', pvalue=>16);
This will help the optimizer spot if the index is "mostly clustered".
I AM NOT ABLE TO DECIDE WEATHER TO USE SKIP SCAN HINT OR NOT. You should avoid hints as much as possible. Particularly if it's fast for some values and slow for others.