Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, manisha.

Asked: May 25, 2020 - 9:00 am UTC

Answered by: Chris Saxon - Last updated: May 27, 2020 - 1:19 pm UTC

Category: Database Development - Version: 12c

Viewed 100+ times

You Asked

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.

and we said...

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.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.