Oracle：利用两个索引 [英] Oracle: take advantage of using two index

问题描述

正如您所看到的，Oracle可以利用两个索引并解决整个查询而无需通过rowid访问该表？

As you can see Oracle could exploit two indexes and solve the entire query without accessing the table by rowid?

SELECT  'Scarti letture GAS' tipo, campo47 pdf, COUNT (1) n
  FROM out.CONSUMI_GEE_LC_T500_00 v
  WHERE stato = 'SC'
  AND stato is not null
  AND campo47 is not null
  GROUP BY 'Scarti letture GAS', campo47;

我做了一个测试，将campo47字段添加到STATO索引中。性能从1 '49'上升到0,6s。

I've made a test adding the field campo47 to the STATO index. Performance boosts from 1' 49'' to 0,6s.

stato上的索引不是选择性的。 campo47上的索引（表示field47）非常具有选择性。

Index on stato is not selective. Index on campo47 (it means field47) is really selective.

推荐答案

你说CAMPO47是高度选择性的。但是你只是在IS NOT NULL上过滤。因此，它具有多少不同的值并不重要，优化器不会将其用作入口点。

You say CAMPO47 is highly selective. But you are only filtering on IS NOT NULL. So it doesn't matter how many distinct values it has, the optimizer isn't going to use it as the entry point.

它的选择性如何？从解释计划中的基数可以看出，选择STATO ='SC'会在表中找到12856行。这些行中有12702个明显具有CAMPO47值，因此只有154行被无效测试过滤掉。如果优化器已经为CAMPO47上的索引填满了多少行将返回？可能还有很多。

And how selective is it? As you can see from the cardinalities in the explain plan, selecting on STATO='SC' finds 12856 rows in your table. 12702 of those rows obvious have CAMPO47 with a value, so only 154 rows are filtered out by the test for nullity. If the optimizer had plumped for the index on CAMPO47 how many rows would that have returned? Probably a lot more.

优化器只能使用一个堆索引来访问表上的行。 （当应用星形转换时，位图索引的机制不同）。因此，如果您认为额外的表访问是一个令人难以忍受的负担，那么您有一个选项：复合索引。如果STATO真的是非选择性的（相对较少的行），那么你可以安全地用一个on（STATO，CAMPO47）替换现有的索引。

The optimizer can only use one heap index to access rows on a table. (The mechanism is different for bitmap indexes when they are applying a star transformation). So, if you think the additional table accesses are an insufferable burden then you have one option: a compound index. If STATO is truly unselective (relatively few rows) then you are probably safe in replacing the existing index with one on (STATO, CAMPO47).

有一个老技巧，用于将数据库轻推到使用索引来访问IS NOT NULL操作，这就是使用一个操作数，该操作数只能在列包含值的情况下为true。例如，对于字符串列这样的东西（我假设一个名为CAMPO47的东西只是一个字符串）：

There is an old trick for nudging the database into using an index to access IS NOT NULL operations, and that is to use an operand which can only be true where the column contains a value. For instance, something like this for string columns (I'm assuming something called CAMPO47 just hase to be a string):

AND campo47 >= chr(0)

这将匹配包含一个或多个ascii字符的任何列。不确定它是否会导致您描述的两个索引优化，但它值得一试。 （我会自己测试一下，但我现在无法访问Oracle数据库，当我试图查看解释计划时，SQL Fiddle投掷了）

That will match any column which contains one or more ascii characters. Not sure whether it will lead to the "two index" optimisation you describe but it's worth a shot. (I would test this myself but I don't have access to an Oracle database right now, and SQL Fiddle hurled when I tried to look at the Explain Plan)

这篇关于Oracle：利用两个索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！