The virtual column is a metadata only column (i.e. no data is stored for it).
create table product( prod_id number, prod_code varchar2(3), release_date number, rel_date DATE as (to_date(to_char(release_date), 'yyyymmdd')) VIRTUAL ) partition by range(rel_date) interval(NUMTOYMINTERVAL (1,'MONTH')) ( partition p0 values less than (to_date('20120101', 'yyyymmdd')) )
insert into product (prod_id, prod_code, release_date) values (1,'abc', 20110502) insert into product (prod_id, prod_code, release_date) values (1,'abc', 20120502) -- this query does NOT prune select * from product where release_date < 20120101 | 0 | SELECT STATEMENT | | 1 | 38 | 4 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ALL| | 1 | 38 | 4 (0)| 00:00:01 | 1 |1048575| |* 2 | TABLE ACCESS FULL | PRODUCT | 1 | 38 | 4 (0)| 00:00:01 | 1 |1048575| -- this query DOES prune select * from product where rel_date < to_date('20120101', 'yyyymmdd') | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 38 | 3 (0)| 00:00:01 | 1 | 1 | |* 2 | TABLE ACCESS FULL | PRODUCT | 1 | 38 | 3 (0)| 00:00:01 | 1 | 1 |
Although you can use INTERVAL partition with NUMBER columns there is no way to express the number of days without using formulas to account for leap years.
PARTITION BY RANGE (release_date) ( partition p_prior_to_2012 less than (20120101), partition p_2012 values less than (20130101), partition p_2013 values less than (20140101), partition p_2014 values less than (20150101) )
846773 wrote:That's what happens when you store dates as numbers or strings. Change column release_date data type to DATE and you'll be able to use interval partitioning.
but i have to use release_date column in where clause.so that i can use partition prunning.i cannot use virtual column .Is there any other way to get the desired result
846773 wrote:Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints. Therefore you can't add/subtract any columns to it.
Can we add multiple columns in partition by reference clause?