在三列复合索引中搜索两列 - Oracle

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

提问by Tam

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!

我在其中一张表的三列上有一个复合索引。它可以查找我的查询的 where close 中是否有三列。当我的搜索查询只有三分之二时，事情似乎不再那么快了！

Do you know how can go around this?

你知道如何解决这个问题吗？

Thanks,

谢谢，

Tam

谭

P.S. The table

PS 表

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):

在 (current_appl_id, event_name & generic_method_name) 上有复合索引 这一个花了 2 分钟（使用复合索引中的所有列）：

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

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-name ='generic_method' 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 <=9 to_08'-24:3 ,'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):

这个运行了 12 分钟（在复合索引中只使用了三分之二）：

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

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-Ceed_40p'ANDp_intereProduct 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

采纳答案by Justin Cave

• 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_1and column_2as it could for queries against all three. There is (at a minimum) a huge drop-off in efficiency if you are searching on column_2and 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.

• 您知道要搜索的三个术语中的哪两个吗？为了获得最大效率，Oracle 将仅在您查询索引的前导列时使用索引。也就是说，在（索引column_1，column_2，column_3）一样可以有效地用于查询上column_1，并column_2因为它可以针对所有三个查询。如果您在column_2和上搜索，效率（至少）会大幅下降column_3，但是，因为它们不是前导列。
• 您使用的是哪个版本的 Oracle？根据 Oracle 版本和前导列的选择性，如果您的查询不包括索引的前导列，则 Oracle 可能能够使用所谓的“索引跳过扫描”来利用索引。这将比常规索引范围扫描效率低，但可能比表扫描效率更高。
• 对于没有您希望的那么快的查询，查询计划是什么？我们可以猜测优化器可能会做什么，但查看实际的查询计划更有用。

回答by Gary Myers

"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.

如果您必须拨打电话簿中所有的“史密斯，约翰”，您可能有 50 个条目，需要 5 个小时。如果您按电话簿中的所有“Smith”条目响铃，您可能有 500 个条目，而且需要十倍的时间。

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.

查看查询，一种选择可能是考虑单独或与其他列组合索引cover_period_start_ts 和/或appl_perf_interval_typ_id。我怀疑很多时间都浪费在查找与索引条件匹配的行、转到表然后拒绝该行上，因为时间戳或间隔类型不符合指定的条件。

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

附注。如果您查看查询，您会看到所有三列都在 where 子句的 != 'NULL' 部分中指定，因此从技术上讲，它使用索引的所有三列，但两列作为 access_predicate，另一列作为一个 filter_predicate