Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, manisha.

Asked: May 20, 2020 - 6:11 am UTC

Answered by: Chris Saxon - Last updated: May 20, 2020 - 9:42 am UTC

Category: Database Development - Version: 12c

Viewed 100+ times

You Asked

Hi Tom,

PFB query structure:

select /*+ gather_plan_statistics  */    count(*)
FROM  CLPT INNER JOIN  SL 
ON CLPT.SHIPPER_LOT_ID = SL.ID 
AND CLPT.CLIENT_ID = SL.CLIENT_ID
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/16/2018 23:59:59', 'MM/DD/YYYY HH24:MI:SS') ;


for the above query , sometimes the index skip scan happened on the created index on (CLIENT_ID, DEVICE_ID, CREATED_ON),
sometimes not. sometimes it goes for Range scan of created index on (CREATED_ON,CLIENT_ID,STATUS) which is costly and response time is also much more.

=> when optimiser is using skip scan, response time is acceptable. but skip scan not working for all clients, mostly it goes for Full table scan.

=> if i use skip scan in hints then response time is better but it is costly for other client_id.

=> SL table also goes for FTS.


DATE IS A TIMESTAMP COLUMN in CLPT.

TOTAL COUNT IN CLPT :4727930
TOTAL COUNT WITH CLIENT_ID,STATUS AND DATE FILTER as in above query: 168025

INDEX ON CLPT IS 1. (CREATED_ON,CLIENT_ID,STATUS)
2. (CLIENT_ID, DEVICE_ID, CREATED_ON)
indexes on the others columns too but here it is expected to use between above two.



INDEX ON SL : UNIQUE INDEX ON 1. ID
2. CLIENT_ID
3. (ID,CLIENT_ID,GROWER_ID)
indexes on the others columns too but here it is expected to use between above three.


please help to do proper indexing or is there any issue related to timestamp column?


and we said...

Indexes work with timestamp columns. The only potential issue I see is you're selecting about a year's worth of data. This could have to read a lot of rows!

To really understand what's going on here, we need to see the execution plan!

If you get the plans using this method:

set serveroutput off

select /*+ gather_plan_statistics  */ ...

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


And post them here we can provide more effective help.

That said, some observations:

Indexes are most effective when you have equality (=) conditions on their leading columns. So reordering the columns on index 1 for CLTP to:

(CLIENT_ID,STATUS,CREATED_ON)


May be more effective than your current index.

Also - if you're only counting the rows and not fetching any columns - an index on some combination of:

(CLIENT_ID,STATUS,CREATED_ON,SHIPPER_LOT_ID )


Should enable an index-only scan. Avoiding the table should make the query faster.

Any of the three indexes you've listed for SL are candidates for this query. This makes it harder for the optimizer; out-of-date stats may lead it to choose the "wrong" index.

The index on (ID,CLIENT_ID,GROWER_ID) should enable an index-only scan. But as this includes the unnecessary GROWER_ID column, it's possible it doesn't. Again, seeing your plans will help us here!

More to Explore

Performance

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