table Partition when values are unknown [message #674139] |
Mon, 07 January 2019 03:39 |
kashifchughtai
Messages: 125 Registered: October 2007
|
Senior Member |
|
|
Dear forum members,
need a advise for the following scenario.
i have a table where data is stored with date field (start_date) and store_id, i need to partition the table in a way that start date is interval at day and storeid is added as subpartition. please note that i wanted to purge the data storeid wise for specific day only.
i tried the following but multiple store ids are getting in same subpartition which i dont want. Also note that store id is not fixed and can grow dynamically.
not sure if this is right approach , kindly suggest.
create table
pos_data (
start_date DATE not null,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE (start_date)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY HASH(store_id)
(
PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('31-12-2018', 'DD-MM-YYYY'))
);
thanks
|
|
|