Home » RDBMS Server » Performance Tuning » table Partition when values are unknown (11g)
table Partition when values are unknown [message #674139] Mon, 07 January 2019 03:39 Go to previous message
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
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: how to avoid window sort?
Next Topic: driving table and join order
Goto Forum:
  


Current Time: Mon Sep 21 02:16:13 CDT 2020