oracle oracle选择查询-多列索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2713438/
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
oracle select query - index on multiple columns
提问by CC.
I'm working on a sql query, and trying to optimise it, because it takes too long to execute.
我正在处理一个 sql 查询，并试图对其进行优化，因为执行时间太长。
I have a few select and UNION between. Every select is on the same table but with different condition in WHERE clause. Basically I have allways something like :
我有几个select和UNION之间。每个选择都在同一个表上，但在 WHERE 子句中具有不同的条件。基本上我总是像这样：
select * from A
where field1 <=TO_DATE ('01/01/2010', 'DD/MM/YYYY')
AND field1 >= TO_DATE(some date)
and field2 IN (...)
UNION
select * from A
where field1 <=TO_DATE ('01/01/2010', 'DD/MM/YYYY')
AND field1 >= TO_DATE(some date2)
and field2 =(...)
UNION
....
I have a index on field1 (it a date field, and field2 is a number). Now, when I do the select and if I put only
我在 field1 上有一个索引（它是一个日期字段，field2 是一个数字）。现在，当我做选择时，如果我只放
WHERE field1 <TO_DATE ('01/01/2010', 'DD/MM/YYYY')
it does not use the index. I'm using Toad to see the explain plain and it said:
它不使用索引。我正在使用 Toad 来查看解释，它说：
SELECT STAITEMENT Optimiser Mode = CHOOSE
TABLE ACCESS FULL
It is a huge table, and the index on this column is there.
这是一个巨大的表，该列上的索引就在那里。
Any idea about this optimiser ? And why it does not uses the index ?
对这个优化器有什么想法吗？为什么它不使用索引？
Another question is , if I have where clause on field1 and field2 , I have to create only one index, or one index for each field ?
另一个问题是，如果我在 field1 和 field2 上有 where 子句，我必须只创建一个索引，或者为每个字段创建一个索引？
回答by Aaron Smith
Wouldn't you be better off without the Union and using OR's between your different clauses instead?
如果没有 Union 并且在不同的子句之间使用 OR 代替，你会不会过得更好？
select * from A
where (
field1 <"toto"
and field2 IN (...)
)
OR
(
field1 >"toto2"
and field2 IN (...)
)
OR
....
It is also possible to have an index on 2 columns.
也可以在 2 列上建立索引。
CREATE INDEX index_name
ON A (field1, field2);
回答by MJB
You can create one index on both columns. But your question treats field1like a string above and then like a date below. Is that intentional? Also, the optimizer chooses based on what it thinks. So are your stats up to date? If not, it might think that a FTS is the best way to find the records you want.
您可以在两列上创建一个索引。但是你的问题field1就像上面的字符串，然后像下面的日期。这是故意的吗？此外，优化器根据它的想法进行选择。那么你的统计数据是最新的吗？如果没有，它可能会认为 FTS 是查找所需记录的最佳方式。
回答by Quassnoi
Any idea about this optimiser ? And why it does not uses the index ?
对这个优化器有什么想法吗？为什么它不使用索引？
Most probably because this condition
很可能是因为这种情况
field1 < '12/12/2010'
returns all or almost all rows.
返回所有或几乎所有行。
In this case, FULL TABLE SCANis better.
在这种情况下，FULL TABLE SCAN更好。
Another question is , if I have where clause on field1 and field2 , I have to create only one index, or one index for each field ?
另一个问题是，如果我在 field1 和 field2 上有 where 子句，我必须只创建一个索引，或者为每个字段创建一个索引？
For this query:
对于此查询：
select * from A
where field1 <"toto"
and field2 IN (...)
, you need to create a composite index:
，需要创建复合索引：
CREATE INDEX ix_a_2_1 ON A (field2, field1)
This will use an INLIST ITERATORto split the query into a number of continuous ranges and use the INDEX RANGE SCANto return the values from each range.
这将使用INLIST ITERATOR将查询拆分为多个连续范围，并使用INDEX RANGE SCAN返回每个范围的值。
回答by Rich Adams
Since you're dealing with dates for field1, you want to use TO_DATE. For example,
WHERE field1 < TO_DATE('2010/12/12 12:00:00', 'yyyy/mm/dd hh24:mi:ss').What type of index does the field have? I'm assuming a b-tree index, in which case the less-than operator is OK, but the optimiser may decide not to use it depending on the current table statistics. If you want to force the index to be used, you can specify it in a hint and see how that affects the performance.
SELECT /*+ index(tbl.INDEX_NAME) */
tbl.*
FROM A tbl WHERE field1 < TO_DATE('2010/12/12 12:00:00','yyyy/mm/dd hh24:mi:ss');As for your final question, you can create an index on each column individually, or a composite index using both columns. If you're always going to be selecting based on values from both columns, then a composite index over both of those columns would probably be better.
由于您正在处理 field1 的日期，因此您想使用TO_DATE。例如，
WHERE field1 < TO_DATE('2010/12/12 12:00:00', 'yyyy/mm/dd hh24:mi:ss')。该字段有什么类型的索引？我假设是一个 b 树索引，在这种情况下小于运算符是可以的，但是优化器可能会根据当前表的统计信息决定不使用它。如果你想强制使用索引，你可以在一个提示中指定它，看看它如何影响性能。
SELECT /*+ index(tbl.INDEX_NAME) */
tbl.*
FROM A tbl WHERE field1 < TO_DATE('2010/12/12 12:00:00','yyyy/mm/dd hh24:mi:ss');至于您的最后一个问题，您可以单独在每列上创建索引，也可以使用两列创建复合索引。如果您总是要根据两列的值进行选择，那么对这两列的复合索引可能会更好。
回答by brydgesk
I'm not the biggest Oracle expert, but I imagine that you cannot get the advantage of an index when using '>' or '<' operators. Indexing provides a quick way to look up specific values, not a range of values. So when you look for values "less than" a specific value, the index does nothing.
我不是最大的 Oracle 专家，但我想您在使用“>”或“<”运算符时无法获得索引的优势。索引提供了一种查找特定值而不是值范围的快速方法。因此，当您查找“小于”特定值的值时，索引不会执行任何操作。
