Strange speed changes with sql query

Answer 1

Looks to me like none of the indexes shown in your post are very useful. I'd expect it to do a full table scan no matter what.

If you can, add an index on

DTE_OF_SRVCE, CLM_TYPE, PRGRM_ID

and see if that helps. If you want to try for an index-only retrieval, add ID to the end of the index (so it'd be DTE_OF_SRVCE, CLM_TYPE, PRGRM_ID, ID).

Share and enjoy.

Answer 2

Maybe it's because cInt_id is only on your third index. And else it would use the second one

Answer 3

To better understand what's going on, try this:

explain plan set statement_id = 'query1' for
SELECT  count(ck.id)
FROM    claim_key ck
WHERE   (ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY))
     AND ck.clm_type = 5
     AND ck.prgrm_id = 1;

and then:

select *
from table(dbms_xplan.display(statement_id=>'query1'));

I'm guessing you'll see a line indicating TABLE ACCESS FULL on claim_key.

Then try:

explain plan set statement_id = 'query2' for
SELECT  count(ck.id)
FROM    claim_key ck
WHERE   (ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY))
     AND ck.clm_type = 5;

select *
from table(dbms_xplan.display(statement_id=>'query2'));

and check to see what index it (presumably) using. That should give you an idea of what the database is doing which helps to figure out why it's doing it.

---

Ok, given your explain plans, it's a classic example of "indexes are not always good, tables scans are not always bad".

The INDEX SKIP SCAN is where the database can try to use an index even though the leading column of the index is not even used. Basically if your index looked like this (overly simplified):

COL1   COL2   ROWID
A      X      1        <--
A      Y      2
A      Z      3
B      X      4        <--
B      Y      5
B      Z      6 

and your condition was WHERE col2 = 'X' the index skip scan says look through each combination in COL1 for where col2 = 'X'. It "skips" the values in col1 once it's found a match (e.g. col1 = A, col2 = X) down to where the value changes (col1 = B, then col1 = C, etc.) and looks for more matches.

The catch is that indexes (generally!) work like this: 1) find the next rowid in the index where the value was found 2) go to the table block with that rowid (TABLE ACCESS BY INDEX ROWID) 3) repeat until no more matches are found.

(For the skip scan, it would also incur the cost of finding out where the next change of value is for the leading columns.)

This is all well and good for a small number of rows, but suffers from the law of diminishing returns; it's not that great when you've got a large number of rows. That's because it has to read an index block, then a table block, then an index block, a table block (even if the table block was previously read.)

The full table scan just "plows" through the data thanks in part to...multiblock reads. The database can read many blocks from disk in a single read and doesn't read the same block more than once.

The INDEX FAST FULL SCAN is basically treating the I_CLAIM_KEY_002 as a table. All of what you need in the query can be answered by the index alone; no TABLE ACCESS is required. (I'm guessing I_CLAIM_KEY_002 is defined as clnt_id, dte_of_srvce and either clnt_id or dte_of_srvce is not nullable. Since ck.id should be a not null attribute, a count on ck.id is the same as a count on ck.clnt_id.)

So as for your initial query, unless you want to rejig your indexes, try this:

SELECT  /*+ FULL(ck) */ count(ck.id)
FROM    claim_key ck
WHERE   (ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY))
     AND ck.clm_type = 5
     AND ck.prgrm_id = 1

which will force a full table scan on claim_key (ck) and you could see similar performance as the other two. (Check that this is the case first prefixing the query with "explain plan set statement_id = 'query_hint' for" and running the dbms_xplan query before you run it.)

(Now you'll ask "do I want to put in hints like that all the time"? Please don't. This is for a test only. This is just to check to see if a FTS is better than the INDEX SKIP SCAN. If it is, then you need to find out why. :)

Anyways...I hope that made snese..I mean sense.

Answer 4

Perhaps stating the obvious here, but are these results repeatable, or did you try this once, in the order specified in your question? If so then block caching could explain the differences.

Answer 5

What happens if you try something like...

SELECT COUNT(*)
    FROM (SELECT ck.id,
                 ck.prgrm_id AS prgrm_id
              FROM claim_key ck
              WHERE (ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY)) AND
                    ck.clm_type = 5) AS sq
    WHERE sq.prgrm_id = 1;

I can't try this sort of thing at home so it may be no good, but it may help.