StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStrange speed changes with sql query
text
Body
copied!<p>I'm having trouble getting the correct query (oracle) for what I'm looking for. Essentially what I want is:</p> <pre><code>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 </code></pre> <p>Explain: <pre> | Id | Operation | Name | Rows | Bytes | Cost | | 0 | SELECT STATEMENT | | 1 | 14 | 3080| | 1 | SORT AGGREGATE | | 1 | 14 | | | 2 | TABLE ACCESS BY INDEX ROWID| CLAIM_KEY | 6531 | 91434 | 3080| | 3 | INDEX SKIP SCAN | I_CLAIM_KEY_001 | 1306K| | 2813| </pre></p> <p>This query gets me what I want (the result on average being 20), but takes 10 minutes to run.</p> <p>The following query is not quite complete, but runs much faster:</p> <pre><code>SELECT count(ck.id) FROM claim_key ck WHERE (ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY)) AND ck.clm_type = 5 </code></pre> <p>Explain:</p> <pre> | Id | Operation | Name | Rows | Bytes | Cost | | 0 | SELECT STATEMENT | | 1 | 11 | 9195 | | 1 | SORT AGGREGATE | | 1 | 11 | | | 2 | TABLE ACCESS FULL | CLAIM_KEY | 19592 | 210K| 9195 | </pre> <p>This returns about 20 as well, though this is just by fluke and I can't rely on it, I need to include the prgrm_id. The thing is that it only take 20 seconds.</p> <p>The following query is not what I'm looking for but gives an idea of the performance:</p> <pre><code>SELECT count(ck.id) FROM claim_key ck WHERE (ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY)) </code></pre> <pre> | Id | Operation | Name | Rows | Bytes | Cost | | 0 | SELECT STATEMENT | | 1 | 8 | 4 | | 1 | SORT AGGREGATE | | 1 | 8 | | | 2 | INDEX FAST FULL SCAN| I_CLAIM_KEY_002 | 195K| 1530K| 4 | </pre> <p>This also takes 20 seconds, but it returns on average 700 records. The table claim_key is about 25 million rows.</p> <p>There are multiple indexes on this table. They are:</p> <pre> IX_CLAIM_KEY_CREATED: CREATED_ON I_CLAIM_KEY_001: CLNC_STE_ID, PRVDR_ID, PRGRM_ID, UPDATED_ON I_CLAIM_KEY_002: SRCE_ID, PRVDR_ID, CLNC_ID, DTE_OF_SRVCE, PRGRM_ID I_CLAIM_KEY_003: CLNT_ID, DTE_OF_SRVCE I_CLAIM_KEY_004: TRNSMSN_ID, CLM_STTS I_CLAIM_KEY_005: UPDATED_ON I_CLAIM_KEY_006: PRVDR_ID, CMN_SRCE_ID PK_CLAIM_ID: ID </pre> <p>What I want to know is why is adding the prgrm_id slowing it down so much? I would have expected it to be pretty quick since it would only have to search through the 700 rows specified by <code>(ck.dte_of_srvce > (SYSDATE - INTERVAL '30' DAY))</code>. Is that an incorrect assumption?</p> <p><strong><em>Edit</em></strong></p> <p>Using the hint <code>/*+ FULL(ck) */</code> on the first query, its execution time drops and it generates the following plan.</p> <pre> | Id | Operation | Name | Rows | Bytes | Cost | | 0 | SELECT STATEMENT | | 1 | 14 | 9195 | | 1 | SORT AGGREGATE | | 1 | 14 | | | 2 | TABLE ACCESS FULL | CLAIM_KEY | 6531 | 91434 | 9195 | </pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload