oracle 多列索引 - 仅对一列进行查询时可以吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1865423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
index with multiple columns - ok when doing query on only one column?
提问by svrist
If I have an table
如果我有一张桌子
create table sv ( id integer, data text )
and an index:
和一个索引：
create index myindex_idx on sv (id,text)
would this still be usefull if I did a query
如果我进行查询，这仍然有用吗
select * from sv where id = 10
My reason for asking is that i'm looking through a set of tables with out any indexes, and seeing different combinations of select queries. Some uses just one column other has more than one. Do I need to have indexes for both sets or is an all-inclusive-index ok? I am adding the indexes for faster lookups than full table scans.
我提出这个问题的原因是我正在查看一组没有任何索引的表，并看到了选择查询的不同组合。有些只使用一列，其他的不止一列。我是否需要为两个集合都有索引，还是一个全包索引可以？我正在添加索引以实现比全表扫描更快的查找。
Example (based on the answer by Matt Huggins):
示例（基于 Matt Huggins 的回答）：
select * from table where col1 = 10
select * from table where col1 = 10 and col2=12
select * from table where col1 = 10 and col2=12 and col3 = 16
could all be covered by index table (co1l1,col2,col3) but
都可以被索引表 (co1l1,col2,col3) 覆盖，但是
select * from table where col2=12
would need another index?
需要另一个索引吗？
回答by Matt Huggins
It should be useful since an index on (id, text) first indexes by id, then text respectively.
它应该很有用，因为 (id, text) 上的索引首先按 id 索引，然后分别按文本索引。
- If you query by id, this index will be used.
- If you query by id & text, this index will be used.
- If you query by text, this index will NOT be used.
- 如果按 id 查询，将使用此索引。
- 如果您按 id & text 查询，将使用此索引。
- 如果您按文本查询，则不会使用此索引。
Edit:when I say it's "useful", I mean it's useful in terms of query speed/optimization. As Sune Rievers pointed out, it will not mean you will get a unique record given just ID (unless you specify ID as unique in your table definition).
编辑：当我说它“有用”时，我的意思是它在查询速度/优化方面很有用。正如 Sune Rievers 指出的那样，这并不意味着您将获得仅给定 ID 的唯一记录（除非您在表定义中指定 ID 为唯一记录）。
回答by David Aldridge
Oracle supports a number of ways of using an index, and you ought to start by understanding all of them so have a quick read here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#sthref973
Oracle 支持多种使用索引的方式，您应该首先了解所有这些方式，因此请快速阅读此处：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14211/ optimops.htm#sthref973
Your query select * from table where col2=12could usefully leverage an index skip scan if the leading column is of very low cardinality, or a fast full index scan if it is not. These would probably be fine for running reports, however for an OLTP query it is likely that you would do better to create an index with col2 as the leading column.
select * from table where col2=12如果前导列的基数非常低，您的查询可以有效地利用索引跳过扫描，如果不是，则可以利用快速完整索引扫描。这些对于运行报告可能很好，但是对于 OLTP 查询，您可能最好创建一个以 col2 作为前导列的索引。
回答by Nick Pierpoint
If the driver behind your question is that you have a table with several columns and any combination of these columns may be used in a query, then you should look at BITMAP indexes.
如果您的问题背后的驱动因素是您有一个包含多个列的表，并且这些列的任意组合可以在查询中使用，那么您应该查看 BITMAP 索引。
Looking at your example:
看看你的例子：
select * from mytable where col1 = 10 and col2=12 and col3 = 16
You could create 3 bitmap indexes:
您可以创建 3 个位图索引：
create bitmap index ix_mytable_col1 on mytable(col1);
create bitmap index ix_mytable_col2 on mytable(col2);
create bitmap index ix_mytable_col3 on mytable(col3);
These bitmap indexes have the great benefit that they can be combinedas required.
这些位图索引有很大的好处，它们可以根据需要进行组合。
So, each of the following queries would use one or more of the indexes:
因此，以下每个查询都将使用一个或多个索引：
select * from mytable where col1 = 10;
select * from mytable where col2 = 10 and col3 = 16;
select * from mytable where col3 = 16;
So, bitmap indexes may be an option for you. However, as David Aldridge pointed out, depending on your particular data set a single index on (col1,col2,col3) might be preferable. As ever, it depends. Take a look at your data, the likely queries against that data, and make sure your statistics are up to date.
因此，位图索引可能是您的一个选择。但是，正如 David Aldridge 指出的那样，根据您的特定数据集，(col1,col2,col3) 上的单个索引可能更可取。一如既往，这取决于。查看您的数据以及对该数据的可能查询，并确保您的统计数据是最新的。
Hope this helps.
希望这可以帮助。
回答by Sune Rievers
I assume idis primary key. There is no point in adding a primary key to the index, as this will always be unique. Adding something unique to something else will also be unique.
我假设id是主键。向索引添加主键没有意义，因为它始终是唯一的。将独特的东西添加到其他东西中也将是独一无二的。
Add a unique index to text, if you really need it, otherwise just use idis uniqueness for the table.
text如果您确实需要，则将唯一索引添加到，否则只使用id表的唯一性。
If idis not your primary key, then you will not be guaranteed to get a unique result from your query.
如果id不是您的主键，则不能保证您从查询中获得唯一的结果。
Regarding your last example with lookup on col2, I think you could need another index. Indexes are not a cure-all solution for performance problems though, sometimes your database design or your queries needs to be optimized, for instance rewritten into stored procedures (while I'm not totally sure Oracle has them, I'm sure there's an Oracle equivalent).
关于上一个查找的示例col2，我认为您可能需要另一个索引。索引并不是解决性能问题的万能解决方案，有时您的数据库设计或查询需要优化，例如重写为存储过程（虽然我不完全确定 Oracle 有它们，但我确定有一个 Oracle相等的）。
