Avatar of shanikawm
shanikawmFlag for Sri Lanka

asked on 

Oracle Subpartitioning 10G (10.2.0.5)

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.


Oracle Database

Avatar of undefined
Last Comment
shanikawm
ASKER CERTIFIED SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of shanikawm
shanikawm
Flag of Sri Lanka image

ASKER

Thanks
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo