Databases
StackOverflow2013
Postsdbo
POSeaching for two columns in three-column composite index - Oracle
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSeaching for two columns in three-column composite index - Oracle
    text
    copied!<p>I have a composite index on three column in one of my table. It works find if I have the three columns in the where close of my query. When my search query has only two out of three, things don't seem to be as fast any more!</p> <p>Do you know how can go around this?</p> <p>Thanks,</p> <p>Tam</p> <p>P.S. The table </p> <pre><code>APPL_PERF_STATS </code></pre> <p>has composite index on (current_appl_id, event_name &amp; generic_method_name) This one took 2 minutes(using all columns in the composite index):</p> <blockquote> <p>SELECT * FROM APPL_PERF_STATS WHERE (GENERIC_METHOD_NAME != 'NULL' AND CURRENT_APPL_ID != 'NULL' AND EVENT_NAME != 'NULL') AND ROWNUM &lt; 502 AND current_appl_id = 'OMS' AND event_name = 'OMS-CeaseProduct' AND generic_method_name = 'CE CallForwardFixedCOProduct' AND appl_perf_interval_typ_id = 1440 AND cover_period_start_ts >= to_date('20-07-2008 14:36','dd-mm-yyyy HH24:mi') AND cover_period_start_ts &lt;= to_date('19-08-2009 14:36','dd-mm-yyyy HH24:mi') ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME, CREATE_TS</p> </blockquote> <p>This one took 12 minutes to run(using only 2 out of three in the composite index):</p> <blockquote> <p>SELECT * FROM APPL_PERF_STATS WHERE (GENERIC_METHOD_NAME != 'NULL' AND CURRENT_APPL_ID != 'NULL' AND EVENT_NAME != 'NULL') AND ROWNUM &lt; 502 AND current_appl_id = 'OMS' AND event_name = 'OMS-CeaseProduct' AND appl_perf_interval_typ_id = 1440 AND cover_period_start_ts >= to_date('20-07-2008 14:36','dd-mm-yyyy HH24:mi') AND cover_period_start_ts &lt;= to_date('19-08-2009 14:36','dd-mm-yyyy HH24:mi') ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME, CREATE_TS</p> </blockquote>
 

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