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?
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!