Range partition skip check

Question

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.

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

Update:

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

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

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

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

   year less than 2005 part_2004

   year less than 2006 part_2005

   year less than 2007 part_2006

...so on

Answer 1

Are you sure that it goes to the table just for checking the year? Maybe there are other columns involved?

Was the query supposed to work only on (partitioned) indexes?

If it needs to go to the table anyway, that extra check is not costing much (if the partition is right).

Can you post the query and execution plan?

Answer 2

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)

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