3 Replies Latest reply: Dec 11, 2012 4:10 AM by vrbcik RSS

    partition by virtual column, select - sinlgle partition on two columns

    vrbcik
      Pls, I have a question - we have table with COLUMNs:

      -- TIME_KEY - julian - date value (to_char(sysdate, 'J')) - we would like to have virtual date value:
      -- TIME_ID - virtual column with interval partitioning (great!)

      CREATE TABLE lsd_cntr_pokus
      ( time_key NUMBER not NULL
      , time_ID AS (to_date(time_key,'J'))
      , cntr_key NUMBER not NULL
      ---...etc
      )
      PARTITION BY RANGE (time_ID) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
      (PARTITION part_2007 VALUES LESS THAN
      (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
      )
      ;

      But then - is it possible to somehow define this table,
      so both selects:

      SELECT * FROM lsd_cntr_pokus
      WHERE time_key = to_char(TO_DATE('01.01.2007', 'DD.MM.YYYY'),'j') ;

      SELECT * FROM lsd_cntr_pokus
      WHERE time_id = TO_DATE('01.01.2007', 'DD.MM.YYYY') ;

      use PARTITION RANGE SINGLE?

      Thanks for any help,

      Regards

      Edited by: vrbcik on Dec 10, 2012 3:39 PM
        • 1. Re: partition by virtual column, select - sinlgle partition on two columns
          AlbertoFaenza
          vrbcik wrote:
          Pls, I have a question - we have table with COLUMNs:

          -- TIME_KEY - julian - date value (to_char(sysdate, 'J')) - we would like to have virtual date value:
          -- TIME_ID - virtual column with interval partitioning (great!)

          CREATE TABLE lsd_cntr_pokus
          ( time_key NUMBER not NULL
          , time_ID AS (to_date(time_key,'J'))
          , cntr_key NUMBER not NULL
          ---...etc
          )
          PARTITION BY RANGE (time_ID) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
          (PARTITION part_2007 VALUES LESS THAN
          (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
          )
          ;

          But then - is it possible to somehow define this table,
          so both selects:

          SELECT * FROM lsd_cntr_pokus
          WHERE time_key = to_char(TO_DATE('01.01.2007', 'DD.MM.YYYY'),'j') ;

          SELECT * FROM lsd_cntr_pokus
          WHERE time_id = TO_DATE('01.01.2007', 'DD.MM.YYYY') ;

          use PARTITION RANGE SINGLE?

          Thanks for any help,

          Regards

          Edited by: vrbcik on Dec 10, 2012 3:39 PM
          Hi,

          I'm afraid that, with this partition definition, partition pruning is working only when you use your virtual column time_id in your query.

          According to documentation Information That Can Be Used for Partition Pruning:
          Virtual column-based partitioned tables benefit from partition pruning for statements that use the virtual column-defining expression in the SQL statement.
          Maybe some tuning expert can confirm this or give another advice.

          Regards.
          Al
          • 2. Re: partition by virtual column, select - sinlgle partition on two columns
            rp0428
            >
            But then - is it possible to somehow define this table,
            so both selects:

            SELECT * FROM lsd_cntr_pokus
            WHERE time_key = to_char(TO_DATE('01.01.2007', 'DD.MM.YYYY'),'j') ;

            SELECT * FROM lsd_cntr_pokus
            WHERE time_id = TO_DATE('01.01.2007', 'DD.MM.YYYY') ;

            use PARTITION RANGE SINGLE?
            >
            No - you have partitioned on the virtual date column not the time_key column.

            Oracle can't prune partitions if the partitioning key isn't part of the query.

            Don't be afraid of breaking Oracle by actually testing things.
            • 3. Re: partition by virtual column, select - sinlgle partition on two columns
              vrbcik
              I have tried this method without interval partitioning, with multiple column partitioning:

              CREATE TABLE lsd_cntr_pokus2
              ( time_key NUMBER not NULL
              , time_key_date AS (to_date(time_key,'J'))
              , cntr_key NUMBER not NULL
              , cntr_on_balance_lcy NUMBER
              , cntr_off_balance_lcy NUMBER
              )
              PARTITION BY RANGE (time_key_date, time_key) --INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
              (
              PARTITION sales_before_2007 VALUES LESS THAN (TO_DATE('01-01-2007','dd-Mm-yyyy'),
              2454102),
              PARTITION sales_200701 VALUES LESS THAN (TO_DATE('01-02-2007','dd-Mm-yyyy'),
              2454133),--to_number(to_char(TO_DATE('01-02-2007','dd-Mm-yyyy'),'J'))),
              PARTITION sales_200702 VALUES LESS THAN (TO_DATE('01-03-2007','dd-Mm-yyyy'),
              2454161)--to_number(to_char(TO_DATE('01-03-2007','dd-Mm-yyyy'),'J')))
              )
              ;

              - it makes some overhead with managing partitions (but not so much more than the old way partitioning)
              + time_key can be selected with explain plan - "PARTITION RANGE MULTI-COLUMN" - is it faster than usual "PARTITION RANGE ALL LOCAL INDEX"?

              SELECT * FROM lsd_cntr_pokus2
              WHERE time_key = 2454102
              ;