We have tables that are interval range partitioned on a DATE column, with a partition for each day - all very standard and straight out of Oracle doc.
A 3rd party application queries the tables to find number of rows based on date range that is on the column used for the partition key.
This application uses date range specified relative to current date - i.e. for last two days would be "..startdate > SYSDATE -2 " - but partition pruning does not take place and the explain plan shows that every partition is included.
By presenting the query using the date in a variable partition pruning does table place, and query obviously performs much better.
DB is 11.2.0.3 on RHEL6, and default parameters set - i.e. nothing changed that would influence optimizer behavior to something unusual.
I can't work out why this would be so. It very easy to reproduce with simple test case below.
I'd be very interested to hear any thoughts on why it is this way and whether anything can be done to permit the partition pruning to work with a query including SYSDATE as it would be difficult to get the application code changed.
Furthermore to make a case to change the code I would need an explanation of why querying using SYSDATE is not good practice, and I don't know of any such information.
1) Create simple partitioned table
CREATETABLE part_test
(id NUMBER NOT NULL,
starttime DATE NOT NULL,
CONSTRAINT pk_part_test PRIMARY KEY (id))
PARTITION BY RANGE (starttime) INTERVAL (NUMTODSINTERVAL(1,'day')) (PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2013','DD-MM-YYYY')));
2) Populate table 1million rows spread between 10 partitions
BEGIN
FOR i IN 1..1000000
LOOP
INSERT INTO part_test (id, starttime) VALUES (i, SYSDATE - DBMS_RANDOM.value(low => 1, high => 10));
END LOOP;
END;
/
EXEC dbms_stats.gather_table_stats('SUPER_CONF','PART_TEST');
3) Query the Table for data from last 2 days using SYSDATE in clause
EXPLAIN PLAN FOR
SELECT count(*)
FROM part_test
WHERE starttime >= SYSDATE - 2;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7895 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 111K| 867K| 7895 (1)| 00:00:01 | KEY |1048575|
|* 3 | TABLE ACCESS FULL | PART_TEST | 111K| 867K| 7895 (1)| 00:00:01 | KEY |1048575|
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("STARTTIME">=SYSDATE@!-2)
4) Now do the same query but with SYSDATE - 2 presented as a literal value.
This query returns the same answer but very different cost.
EXPLAIN PLAN FOR
SELECT count(*)
FROM part_test
WHERE starttime >= (to_date('23122013:0950','DDMMYYYY:HH24MI'))-2;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 131 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 111K| 867K| 131 (0)| 00:00:01 | 356 |1048575|
|* 3 | TABLE ACCESS FULL | PART_TEST | 111K| 867K| 131 (0)| 00:00:01 | 356 |1048575|
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("STARTTIME">=TO_DATE(' 2013-12-21 09:50:00', 'syyyy-mm-dd hh24:mi:ss'))
thanks in anticipation
Jim