范围分区跳过检查 [英] Range partition skip check

问题描述

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

We have large amount of data partitioned on year value using range partition in oracle. We have used range partition but each partition contains data only for one year. When we write a query targeting a specific year, oracle fetches the information from that partition but still checks if the year is what we have specified. Since this year column is not part of the index it fetches the year from table and compares it. We have seen that any time the query goes to fetch table data it is getting too slow.

我们能否以某种方式避免 oracle 比较年份值，因为我们肯定知道分区只包含一年的信息.

Can we somehow avoid oracle comparing the year values since we for sure know that the partition contains information for only one year.

更新:

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

1. The year data type on which partition is performed is of type number.

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

We are not selecting any additional columns. I am just performing a count(*) and no columns are being selected.

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

If we remove the condition and target the query to specific partition as select count(*) from table_name partition(part_2004)it is faster while select count(*) from table where year = 2004is way slower.

分区位于年份列上，该列是一个数字，并按如下方式完成

The partition is on year column which is a number and is done something like below

小于 2005 part_2004 的年份

year less than 2005 part_2004

小于 2006 part_2005 的年份

year less than 2006 part_2005

小于 2007 part_2006 的年份

year less than 2007 part_2006

...等等

推荐答案

如果没有解释计划或表定义，真的很难说清楚发生了什么.我的第一个猜测是您有没有 year 列的 LOCAL 分区索引.它们对分区上的 COUNT(*) 有帮助，但是当您查询一年(至少在 10.2.0.3 上)时，它们似乎没有被使用.

Without the explain plan or the table definition it's really hard to tell what goes on. My first guess is that you have LOCAL partitionned indexes without the year column. They help with the COUNT(*) on a partition, however they don't seem to be used when you query a single year (at least on 10.2.0.3).

这是一个重现您的发现(和解决方法)的小示例:

Here is a small example that reproduces your finding (and a workaround):

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

现在比较两个解释计划:

Now compare the two explain plans:

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 压缩第一列.生成的索引与原始索引的大小几乎相同(由于压缩)，因此不应影响性能.

As you can see the index is not used when you query the year directly. When you add the year to the LOCAL index it will be used. I used the COMPRESS 1 instruction to tell Oracle to compress the first column. The resulting index is nearly the same size as the original index (thanks to compression) so performance shouldn't be impacted.

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...)

这篇关于范围分区跳过检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！