范围分区跳过检查

Question

我们有大量的数据使用 oracle 中的范围分区按年份值进行分区。我们使用了范围分区，但每个分区仅包含一年的数据。当我们编写针对特定年份的查询时，Oracle 会从该分区中获取信息，但仍会检查该年份是否是我们指定的年份。由于今年列不是索引的一部分，因此它从表中获取年份并进行比较。我们已经看到，每当查询获取表数据时，它都会变得太慢。

我们能否以某种方式避免 Oracle 比较年份值，因为我们确信分区只包含一年的信息。

更新：

1. 执行分区的年份数据类型为number类型。

2. 我们不会选择任何其他列。我只是执行 count(*) 并且没有选择任何列。

3. 如果我们删除条件并将查询定位到特定分区，如下所示 select count(*) from table_name partition(part_2004)速度更快 尽管 从表中选择计数(*) 其中year = 2004要慢得多。

4. 分区位于年份列上，该列是一个数字，其操作如下

   年份小于2005年part_2004

   年份小于2006年part_2005

   年份小于2007年part_2006

...依此类推

Answer 1

如果没有解释计划或表定义，真的很难判断发生了什么。我的第一个猜测是您有 LOCAL 分区索引，没有 year 列。它们有助于分区上的 COUNT(*)，但是当您查询单个年份时（至少在 10.2.0.3 上），它们似乎不会被使用。

下面是一个重现您的发现（和解决方法）的小示例：

SQL> CREATE TABLE DATA (
  2     YEAR NUMBER NOT NULL,
  3     ID NUMBER NOT NULL,
  4     extra CHAR(1000)
  5  ) PARTITION BY RANGE (YEAR) (
  6     PARTITION part1 VALUES LESS THAN (2010),
  7     PARTITION part2 VALUES LESS THAN (2011)
  8  );
Table created

SQL> CREATE INDEX ix_id ON DATA  (ID) LOCAL;
Index created

SQL> INSERT INTO DATA 
  2  (SELECT 2009+MOD(ROWNUM, 2), ROWNUM, 'A' FROM DUAL CONNECT BY LEVEL <=1e4);

10000 rows inserted

SQL> EXEC dbms_stats.gather_table_stats(USER, 'DATA', CASCADE=>TRUE);

PL/SQL procedure successfully completed

现在比较两个解释计划：

SQL> SELECT COUNT(*) FROM DATA WHERE YEAR=2010;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=197 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (SINGLE) (Cost=197 Card=5000 Bytes=20000)
   3    2       TABLE ACCESS (FULL) OF 'DATA' (TABLE) (Cost=197 Card=5000...)

SQL> SELECT COUNT(*) FROM DATA PARTITION (part1);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (SINGLE) (Cost=11 Card=5000)
   3    2       INDEX (FULL SCAN) OF 'IX_ID' (INDEX) (Cost=11 Card=5000)

如您所见，当您直接查询年份时，不使用索引。当您将年份添加到 LOCAL 索引时，将使用它。我使用 COMPRESS 1 指令告诉 Oracle 压缩第一列。生成的索引几乎与原始索引大小相同（由于压缩），因此性能不会受到影响。

SQL> DROP INDEX ix_id;
 Index dropped

SQL> CREATE INDEX ix_id ON DATA (year, ID) LOCAL COMPRESS 1;
Index created

SQL> SELECT COUNT(*) FROM DATA WHERE YEAR=2010;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=12 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     PARTITION RANGE (SINGLE) (Cost=12 Card=5000 Bytes=20000)
   3    2       INDEX (RANGE SCAN) OF 'IX_ID' (INDEX) (Cost=12 Card=5000...)

Answer 2

您确定它进入表只是为了检查年份吗？也许还涉及其他专栏？

该查询是否应该仅适用于（分区）索引？

如果无论如何都需要访问表，那么额外的检查不会花费太多（如果分区正确的话）。

你能发布查询和执行计划吗？