2 Replies Latest reply on Dec 23, 2013 3:27 PM by Mohamed Houri

    Partition Pruning on Interval Range Partitioned Table not happening when SYSDATE used in Where Clause

    jimd

      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