This discussion is archived
3 Replies Latest reply: Dec 11, 2012 2:10 AM by vrbcik RSS

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

vrbcik Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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
    ;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points