We help IT Professionals succeed at work.
Get Started

Oracle Subpartitioning 10G (10.2.0.5)

shanikawm
shanikawm asked
on
831 Views
Last Modified: 2012-05-11
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
Database Administrator, retired
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE