Hi, I have composite partitoned table ( range ,hash). When we were designing the table we thought that the number of rows for one particular range partition will be pretty huge and the kind of queries which are going to hit that table will always include the column (col2). So we decided to hash each partition on column col2 . Now after we pushed the table to production and got the volume metrics for a week ,it looks like that number of rows are not that huge ..and we dont necesarily need to has each partition ...for the kind of volume we have range partition is sufficient .. Now I am left with two questions 1.If I leave the table as it is then whats the harm with having unnecessary subpartitions .. 2. If i choose to rearrange the table to exclude the supartition is there a way other than dbms_redefinition . I tried one approach for dropping the sub-partitions by altering the sub-partition template ..but its not giing me the desired result as it leaves the older partitions as it is.. Following is the test case I tried. SQL> DROP TABLE test PURGE 2 / Table dropped. SQL> CREATE TABLE test(col_date_part_key NUMBER NOT NULL 2 ,col2 VARCHAR2(2000) NOT NULL 3 ) 4 PARTITION BY RANGE(col_date_part_key) 5 SUBPARTITION BY HASH (col2) 6 SUBPARTITIONS 16 7 (PARTITION month_01 VALUES LESS THAN (201202) TABLESPACE USERS 8 ,PARTITION month_02 VALUES LESS THAN (201203) TABLESPACE USERS 9 ,PARTITION month_03 VALUES LESS THAN (201204) TABLESPACE USERS 10 ,PARTITION month_04 VALUES LESS THAN (201205) TABLESPACE USERS 11 ,PARTITION month_05 VALUES LESS THAN (201206) TABLESPACE USERS 12 ,PARTITION month_06 VALUES LESS THAN (201207) TABLESPACE USERS 13 ,PARTITION month_07 VALUES LESS THAN (201208) TABLESPACE USERS 14 ,PARTITION month_08 VALUES LESS THAN (201209) TABLESPACE USERS 15 ,PARTITION month_09 VALUES LESS THAN (201210) TABLESPACE USERS 16 ,PARTITION month_10 VALUES LESS THAN (201211) TABLESPACE USERS 17 ,PARTITION month_11 VALUES LESS THAN (201212) TABLESPACE USERS 18 ,PARTITION month_12 VALUES LESS THAN (201301) TABLESPACE USERS 19 ) 20 / Table created. SQL> begin 2 for i in 1 .. 1000 3 loop 4 5 INSERT INTO test(col_date_part_key,col2) 6 SELECT to_char(ADD_MONTHS(TO_DATE('11-DEC-2011','DD-MON-YYYY'),l),'YYYYMM') 7 , TRUNC(DBMS_RANDOM.VALUE(0, 100)) 8 FROM (SELECT level l FROM dual CONNECT BY level < 13); 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed. SQL> COMMIT 2 / Commit complete. SQL> begin 2 dbms_stats.gather_table_stats(user,'TEST',granularity=>'ALL'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> SELECT partition_name,count(*) 2 FROM user_tab_subpartitions 3 WHERE table_name = 'TEST' 4 group by partition_name 5 order by partition_name; PARTITION_NAME COUNT(*) ------------------------------ ---------- MONTH_01 16 MONTH_02 16 MONTH_03 16 MONTH_04 16 MONTH_05 16 MONTH_06 16 MONTH_07 16 MONTH_08 16 MONTH_09 16 MONTH_10 16 MONTH_11 16 PARTITION_NAME COUNT(*) ------------------------------ ---------- MONTH_12 16 12 rows selected. SQL> alter table test set subpartition template() ; Table altered. SQL> alter table test add partition month_201301 values less than (201302); Table altered. SQL> begin 2 for i in 1 .. 1000 3 loop 4 5 INSERT INTO test(col_date_part_key,col2) 6 SELECT to_char(ADD_MONTHS(TO_DATE('11-DEC-2011','DD-MON-YYYY'),l),'YYYYMM') 7 , TRUNC(DBMS_RANDOM.VALUE(0, 100)) 8 FROM (SELECT level l FROM dual CONNECT BY level < 14); 9 end loop; 10 commit; 11 end; 12 / PL/SQL procedure successfully completed. SQL> SELECT partition_name,count(*) 2 FROM user_tab_subpartitions 3 WHERE table_name = 'TEST' 4 group by partition_name 5 order by partition_name; PARTITION_NAME COUNT(*) ------------------------------ ---------- MONTH_01 16 MONTH_02 16 MONTH_03 16 MONTH_04 16 MONTH_05 16 MONTH_06 16 MONTH_07 16 MONTH_08 16 MONTH_09 16 MONTH_10 16 MONTH_11 16 PARTITION_NAME COUNT(*) ------------------------------ ---------- MONTH_12 16 MONTH_201301 1 13 rows selected. SQL> spool off Thanks, Anupam -- //www.freelists.org/webpage/oracle-l