Seaching for two columns in three-column composite index - Oracle

Question

Hello,

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!

Do you know how can go around this?

Thanks,

Tam

P.S. The table APPL_PERF_STATS has composite index on (current_appl_id, event_name & generic_method_name) This one took 2 minutes(using all columns in the composite index):

SELECT * FROM APPL_PERF_STATS WHERE (GENERIC_METHOD_NAME != 'NULL' AND CURRENT_APPL_ID != 'NULL' AND EVENT_NAME != 'NULL') AND ROWNUM < 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 <= to_date('19-08-2009 14:36','dd-mm-yyyy HH24:mi') ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME, CREATE_TS

This one took 12 minutes to run(using only 2 out of three in the composite index):

SELECT * FROM APPL_PERF_STATS WHERE (GENERIC_METHOD_NAME != 'NULL' AND CURRENT_APPL_ID != 'NULL' AND EVENT_NAME != 'NULL') AND ROWNUM < 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 <= to_date('19-08-2009 14:36','dd-mm-yyyy HH24:mi') ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME, CREATE_TS

Answer 1

• Do you know which two of the three terms you are going to search by? For maximum efficiency, Oracle will only use indexes where you are querying on the leading columns of an index. That is, an index on (column_1, column_2, column_3) can be used just as efficiently for queries on column_1 and column_2 as it could for queries against all three. There is (at a minimum) a huge drop-off in efficiency if you are searching on column_2 and column_3, however, because those are not the leading columns.
• What version of Oracle are you using? Depending on the Oracle version and the selectivity of the leading column, Oracle may be able to use what are called "index skip scans" to utilize the index if your queries do not include the leading column(s) of the index. This will be less efficient than a regular index range scan, but it may be more efficient than a table scan.
• What is the query plan on the queries that are not as quick as you'd hope? We can guess at what the optimizer might do, but seeing the actual query plan is far more useful.

Answer 2

"When my search query has only two out of three, things don't seem to be as fast any more!" And you expected something else ?

If you had to ring all the "Smith, John" in the phone book, you may have 50 entries and it takes 5 hours. If you ring all the "Smith" entries in the phone book, you may have 500 entries and it takes ten times as long.

The real question is, with the data volumes you are expecting to process, is the performance reasonable/acceptable.

Looking at the query, one option may be to consider indexing cover_period_start_ts and/or appl_perf_interval_typ_id, either separately or in combination with the other columns. I suspect a lot of the time is wasted in finding a row matching the index criteria, going to the table and then rejecting the row because the timestamp or interval type doesn't meet the specified criteria.

PS. if you look at the query, you'll see that all three columns are specified in the != 'NULL' part of the where clause, so technically it is using all three columns of the index, but two as an access_predicate and one as a filter_predicate