Oracle composite partition table 測試紀實 (range-list partition table)

Oracle 文檔寫的沒有那麼清楚, 所以花了點時間google, 稍微測試了一下:

create tablespace q1_2000 datafile '/u01/app/oracle/oradata/PROD/disk1/q1_2000.dbf' size 1M ;

create tablespace q2_2000 datafile '/u01/app/oracle/oradata/PROD/disk1/q2_2000.dbf' size 1M ;

create tablespace q3_2000 datafile '/u01/app/oracle/oradata/PROD/disk1/q3_2000.dbf' size 1M ;

create tablespace q4_2000 datafile '/u01/app/oracle/oradata/PROD/disk1/q4_2000.dbf' size 1M ;

CREATE TABLE SH.SALES_RL_TAB

(

divno VARCHAR2(12),

depno NUMBER,

itemno VARCHAR2(16),

accrual_date DATE,

sales_amount NUMBER,

TYPE VARCHAR2(4),

constraint pk_q_dvdno primary key(divno,depno)

) TABLESPACE users PARTITION BY RANGE (accrual_date) SUBPARTITION BY LIST ("TYPE")

(PARTITION q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')) tablespace q1_2000

(

SUBPARTITION q1_2000_D VALUES ('DIAL'),

SUBPARTITION q1_2000_A VALUES ('ADSL'),

SUBPARTITION q1_2000_W VALUES ('WIFI')

),

PARTITION q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')) tablespace q2_2000

(

SUBPARTITION q2_2000_D VALUES ('DIAL'),

SUBPARTITION q2_2000_A VALUES ('ADSL'),

SUBPARTITION q2_2000_W VALUES ('WIFI')

),

PARTITION q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')) tablespace q3_2000

(

SUBPARTITION q3_2000_D VALUES ('DIAL'),

SUBPARTITION q3_2000_A VALUES ('ADSL'),

SUBPARTITION q3_2000_W VALUES ('WIFI')

),

PARTITION q4_2000 VALUES LESS THAN ( TO_DATE('01-JAN-2001','DD-MON-YYYY')) tablespace q4_2000

(

SUBPARTITION q4_2000_D VALUES ('DIAL'),

SUBPARTITION q4_2000_A VALUES ('ADSL'),

SUBPARTITION q4_2000_W VALUES ('WIFI')

)

);

create index SH.SALES_RL_IDX on SH.SALES_RL_TAB(accrual_date) tablespace INDX

local

(

PARTITION q1_2000 tablespace INDX,

PARTITION q2_2000 tablespace INDX,

PARTITION q3_2000 tablespace INDX,

PARTITION q4_2000 tablespace INDX

);

#在下面新增兩個partition 分別是 q1_2001, q2_2001 測試:

SQL>

create tablespace q1_2001 datafile '/u01/app/oracle/oradata/PROD/disk1/q1_2001.dbf' size 1M ;

create tablespace q2_2001 datafile '/u01/app/oracle/oradata/PROD/disk1/q2_2001.dbf' size 1M ;

ALTER TABLE SH.SALES_RL_TAB

   ADD PARTITION q1_2001 VALUES LESS THAN (TO_DATE('01-APR-2001','DD-MON-YYYY'))

      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE q1_2001 NOLOGGING

         (

          SUBPARTITION q1_2001_D VALUES ('DIAL'),

          SUBPARTITION q1_2001_A VALUES ('ADSL'),

          SUBPARTITION q1_2001_W VALUES ('WIFI')

         );

ALTER TABLE SH.SALES_RL_TAB

   ADD PARTITION q2_2001 VALUES LESS THAN (TO_DATE('01-JUL-2001','DD-MON-YYYY'))

      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE q2_2001 NOLOGGING

         (

          SUBPARTITION q2_2001_D VALUES ('DIAL'),

          SUBPARTITION q2_2001_A VALUES ('ADSL'),

          SUBPARTITION q2_2001_W VALUES ('WIFI')

         );

#Add subpartition 後可以看到, index 的結構也會自動被放大

set pagesize 300

set long 999999999

select dbms_metadata.get_ddl('INDEX','SALES_RL_IDX','SH') from dual;

col index_name for a15

col index_owner for a8

col status for a6

col global_stats for a10

col partition_name for a10

select index_owner, index_name, status, PARTITION_NAME, GLOBAL_STATS from DBA_IND_SUBPARTITIONS where index_name='SALES_RL_IDX';

#如果要重新rebuild 特定的index partition

alter index SH.SALES_RL_IDX rebuild subpartition q1_2001_D;

#如果要分析該subpartition , 僅能分析所屬的母 partition q1_2001

exec dbms_stats.gather_index_stats(ownname=>'SH',indname=>'SALES_RL_IDX',partname=>'q1_2001', degree=> 3);

======================================================================

測試 drop subpartition

#q2_2001 有三個subpartition : q2_2001W, q2_2001_A, q2_2001_D

#先drop 第一個

alter table SH.SALES_RL_TAB drop SUBPARTITION q2_2001_W;

col index_name for a15

col index_owner for a8

col status for a6

col global_stats for a10

col partition_name for a10

select index_owner, index_name, status, PARTITION_NAME, GLOBAL_STATS from DBA_IND_SUBPARTITIONS where index_name='SALES_RL_IDX';

INDEX_OW INDEX_NAME      STATUS PARTITION_ GLOBAL_STA

-------- --------------- ------ ---------- ----------

SH       SALES_RL_IDX    USABLE Q1_2000    NO

SH       SALES_RL_IDX    USABLE Q1_2000    NO

SH       SALES_RL_IDX    USABLE Q1_2000    NO

SH       SALES_RL_IDX    USABLE Q2_2000    NO

SH       SALES_RL_IDX    USABLE Q2_2000    NO

SH       SALES_RL_IDX    USABLE Q2_2000    NO

SH       SALES_RL_IDX    USABLE Q3_2000    NO

SH       SALES_RL_IDX    USABLE Q3_2000    NO

SH       SALES_RL_IDX    USABLE Q3_2000    NO

SH       SALES_RL_IDX    USABLE Q4_2000    NO

SH       SALES_RL_IDX    USABLE Q4_2000    NO

SH       SALES_RL_IDX    USABLE Q4_2000    NO

SH       SALES_RL_IDX    USABLE Q1_2001    NO

SH       SALES_RL_IDX    USABLE Q1_2001    NO

SH       SALES_RL_IDX    USABLE Q1_2001    NO

SH       SALES_RL_IDX    USABLE Q2_2001    NO

SH       SALES_RL_IDX    USABLE Q2_2001    NO

不影響既有的index status , 仍然是 usable...

接著我把剩下的兩個subpartition 都drop:

#再drop 第二個

alter table SH.SALES_RL_TAB drop SUBPARTITION q2_2001_A update global indexes;

--剩最後一個subpartition的時候, 則母partition無法被drop

--alter table SH.SALES_RL_TAB drop SUBPARTITION q2_2001_D update global indexes; 此指令會fail

#drop 最後一個subpartition (其實是drop 母partition)

alter table SH.SALES_RL_TAB drop PARTITION q2_2001 update global indexes;

#做完記得釋放空間by drop tablespace

drop tablespace q2_2001 including contents and datafiles;

select index_owner, index_name, status, PARTITION_NAME, GLOBAL_STATS from DBA_IND_SUBPARTITIONS where index_name='SALES_RL_IDX';
INDEX_OW INDEX_NAME      STATUS PARTITION_ GLOBAL_STA
-------- --------------- ------ ---------- ----------
SH       SALES_RL_IDX    USABLE Q1_2000    NO
SH       SALES_RL_IDX    USABLE Q1_2000    NO
SH       SALES_RL_IDX    USABLE Q1_2000    NO
SH       SALES_RL_IDX    USABLE Q2_2000    NO
SH       SALES_RL_IDX    USABLE Q2_2000    NO
SH       SALES_RL_IDX    USABLE Q2_2000    NO
SH       SALES_RL_IDX    USABLE Q3_2000    NO
SH       SALES_RL_IDX    USABLE Q3_2000    NO
SH       SALES_RL_IDX    USABLE Q3_2000    NO
SH       SALES_RL_IDX    USABLE Q4_2000    NO
SH       SALES_RL_IDX    USABLE Q4_2000    NO
SH       SALES_RL_IDX    USABLE Q4_2000    NO
SH       SALES_RL_IDX    USABLE Q1_2001    NO
SH       SALES_RL_IDX    USABLE Q1_2001    NO
SH       SALES_RL_IDX    USABLE Q1_2001    NO

15 rows selected.

======================================================================

Useful command:

set pagesize 300

set long 999999999

select dbms_metadata.get_ddl('TABLE','SALES_RL_TAB','SH') from dual;

select dbms_metadata.get_ddl('INDEX','SALES_RL_IDX','SH') from dual;