主页 / dba / 问题 / 65430
Accepted
wrschneider
Asked: 2014-05-20 18:45:57 +0800 CST

Oracle 在完全扫描时在分区表上使用索引会更好

  • 772

试图从 Oracle 中的这个观察中弄清楚一些意义。

询问：SELECT ... FROM MY_TABLE WHERE PART_KEY=x and DT_KEY between 20120101 and 20120731

在 PART_KEY/DT_KEY 上使用索引扫描，即使自动跟踪显示索引扫描命中的块（一次一个，单块读取）比完整扫描命中的块（在单个多块读取中）更多。统计数据是最新的。

奇怪的是，如果我尝试在不分区的情况下使用同一个表和索引的副本，在使用索引之前似乎有更高的选择性阈值——我上面给出的范围进行了全面扫描，而且只有一个非常窄的范围的值DT_KEY between d1 and d2将使用索引。我验证了该索引也将用于非分区情况，但 Oracle 似乎更倾向于进行全面扫描。

Oracle 如何决定使用索引而不是全扫描，我还应该查看什么？统计数据是最新的，因为我立即收集了它们。

表结构

CREATE TABLE MY_TABLE (
   PART_KEY NUMBER(10) NOT NULL,
   DT_KEY NUMBER(8) NOT NULL,
   ...
) 
PARTITION BY LIST(PART_KEY) (
  PARTITION P1 ...
)
AS SELECT .... FROM [source table]

CREATE INDEX MY_INDEX ON MY_TABLE(PART_KEY, DT_KEY) LOCAL;

EXEC DBMS_STATS.GATHER_TABLE_STATS('[USERNAME]', 'MY_TABLE');
oracle performance

1 个回答

  1. Best Answer

    您需要执行 10053 跟踪才能准确了解 Oracle 如何计算其估计的基数。不过，我假设 Oracle 对该特定范围的基数估计太低，导致它使用分区扫描实际上更有效的索引。假设是这种情况，我猜想问题出在以数字数据类型存储日期数据。

    如果 Oracle 知道某个特定分区的 amin(dt_key)为 20100101 和max(dt_key)20140101，它将相信存在 40,000 个值的范围。如果没有直方图，而您要求的范围介于 20120101 和 20120731 之间，则优化器会期望您将检索 40,000 个可能值中的 730 个或 1.825%。但是，如果您使用 adate来存储日期数据，Oracle 会知道只有 4*365=1460 个值，而您实际上正在检索其中的 212 个或 14.5%。在不知道实际范围的情况下，这些只是示例计算，当然，但它们表明了使用错误的数据类型会导致优化器感到困惑的原理。

    假设这个推测是正确的，您可以通过更改dt_key为date. 您也可以通过在 上收集直方图来解决它dt_key。

    • 2

相关问题