I have a very big table partitioned by weeks, having 16 partitions, and each partition having over 300 Millions of data. Tables has two local indexes also. All the partitioned and indexes are in different 32 tablespaces. At the beginning of a new week I drop the oldest partition and add new partition using the dropped partition's tablespace. It's like,
ALTER TABLE "SELLING" DROP PARTITION "BF_20110131";
ALTER TABLE "SELLING" ADD PARTITION "BF_20110523" VALUES LESS THAN (TO_DATE('20110523','YYYYMMDD')) TABLESPACE "SD_12";
ALTER INDEX "SELLING_IDX1" REBUILD PARTITION "BF_20110523" TABLESPACE "SI_12";
ALTER INDEX "SELLING_IDX2" REBUILD PARTITION "BF_20110523" TABLESPACE "SI_12";
ANALYZE TABLE "SELLING" PARTITION("BF_20110523") ESTIMATE STATISTICS SAMPLE 15 PERCENT;
ALTER TABLE "SELLING" DROP PARTITION "BF_20110207";
ALTER TABLE "SELLING" ADD PARTITION "BF_20110530" VALUES LESS THAN (TO_DATE('20110530',,'YYYYMMDD')) TABLESPACE "SD_13";
ALTER INDEX "SELLING_IDX1" REBUILD PARTITION "BF_20110530" TABLESPACE "SI_13";
ALTER INDEX "SELLING_IDX2" REBUILD PARTITION "BF_20110530" TABLESPACE "SI_13";
ANALYZE TABLE "SELLING" PARTITION("BF_20110530") ESTIMATE STATISTICS SAMPLE 15 PERCENT;
( Create table statement is,
CREATE TABLE (ID NUMBER, SELLDATE DATE ......) PARTITION BY RANGE (SELLDATE) (PARTITION "BF_20110207" VALUE LESS THAN.....)
)
Since I have to analyze data by day (not by weeks) I decide to subpartition the tables as Sunday, Monday, .... Staturday)... Or directly by date. I.e. each partition has 7 subpartitions.
So what will be the next week SQL script. Please modify the above.
And what about the indexes? I think I don't need to worry about it.
Thanks.
ASKER