troubleshooting Question

Oracle Subpartitioning 10G (10.2.0.5)

Avatar of shanikawm
shanikawmFlag for Sri Lanka asked on
Oracle Database
2 Comments1 Solution835 ViewsLast Modified:
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 CERTIFIED SOLUTION
Mark Geerlings
Database Administrator, retired

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros