sub-partition drop

From: Anupam Pandey <my.oralce_at_gmail.com>
Date: Fri, 9 Mar 2012 00:07:15 +0530
Message-ID: <CA+BMZqYyiYYdNGSQZaTA5VzoJrDmH=w0MMxGfBdrUrhN5PZHKA_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 08 2012 - 12:37:15 CST