Oracle Subpartitioning 10G (10.2.0.5)

shanikawm
shanikawm used Ask the Experts™
on
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.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Commented:
When you create a new partition for a range-partitioned table, the corresponding local index partitions are created automatically for you.  There is no need to manually rebuild these new index partitions since there is no data in those partitions yet, and Oracle creates those new index partitions with a status of "USABLE".

Unfortunately, you cannot add sub-partitions to an existing partitioned table that does not include sub-partitions now.  The only way to get sub-partitions is to create a new table that includes subpartitions, then copy all of your data to the new table, then drop the original table.  Sorry, that is not fun, quick or easy.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial