Oracle中分区汇总

CREATE TABLE "VGOPDW"."TDW_READ_CHAPTER_VISIT_CDR_D"
   ( "STATIS_MONTH" NUMBER(6,0),
 "STATIS_DATE" NUMBER(8,0),
 "USER_SERV_NUMBER" NUMBER(12,0),
 "LAST_2_NUM" NUMBER(2,0) GENERATED ALWAYS AS (TO_NUMBER(SUBSTR(TO_CHAR("USER_SERV_NUMBER"),-2))) VIRTUAL VISIBLE  NOT NULL ENABLE
      --虚拟字段
   )
  TABLESPACE "TBS_DW"
  PARTITION BY LIST ("STATIS_DATE")
  SUBPARTITION BY LIST ("LAST_2_NUM")
  SUBPARTITION TEMPLATE ( SUBPARTITION "N_00" VALUES ( '0' ) )
 (PARTITION "D_VISIT_CDR_D_20140708"  VALUES (20140708)
 ,
 PARTITION "D_VISIT_CDR_D_20140709"  VALUES (20140709)
,
 PARTITION "D_VISIT_CDR_D_20140710"  VALUES (20140710)
 )
;

4）组合分区（partition by xxx subpartition by xxx）
    假如我们现在有10W本月刊类杂志提供给消费者查看和购买，我们如何做分区合适呢？首先我们可以先将图书根据种类进行分区，
然后再对每个分区进行月份的分区，这样对于大量的图书而言，我们就可以根据实际的业务需要，将一大堆数据通过分区拆解为若
干个小标进行管理，从而降低了管理的粒度，做到“精细化管理”。

    下面是一个进行组合分区的例子：
--删除测试表
DROP TABLE PART_BOOK_CHENZW PURGE;

--创建列表-范围组合分区
CREATE TABLE PART_BOOK_CHENZW(
       BOOK_ID NUMBER(20) PRIMARY KEY,
       BOOK_DATE DATE,
       BOOK_TYPE NUMBER(2) NOT NULL,
       BOOK_DESC VARCHAR2(20)
)
PARTITION BY LIST(BOOK_TYPE)
SUBPARTITION BY RANGE(BOOK_DATE)
SUBPARTITION TEMPLATE
(
 SUBPARTITION PART_LOG_01 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD')) TABLESPACE DATA,
 SUBPARTITION PART_LOG_02 VALUES LESS THAN (TO_DATE('2013-03-01','YYYY-MM-DD')) TABLESPACE DATA,
 SUBPARTITION PART_LOG_03 VALUES LESS THAN (TO_DATE('2013-05-01','YYYY-MM-DD')) TABLESPACE DATA,
 SUBPARTITION PART_LOG_04 VALUES LESS THAN (TO_DATE('2013-07-01','YYYY-MM-DD')) TABLESPACE DATA,
 SUBPARTITION PART_LOG_05 VALUES LESS THAN (TO_DATE('2013-09-01','YYYY-MM-DD')) TABLESPACE DATA,
 SUBPARTITION PART_LOG_06 VALUES LESS THAN (TO_DATE('2013-10-01','YYYY-MM-DD')) TABLESPACE DATA,
 SUBPARTITION PART_LOG_07 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA
)
(
 PARTITION PART_BOOK_01 VALUES(0) TABLESPACE DATA,
 PARTITION PART_BOOK_02 VALUES(1) TABLESPACE DATA,
 PARTITION PART_BOOK_03 VALUES(2) TABLESPACE DATA,
 PARTITION PART_BOOK_04 VALUES(3) TABLESPACE DATA
);

添加子分区：
ALTER TABLE vgopdw.TDW_MDO_USER_VISIT_D MODIFY PARTITION M_MDO_USER_VISIT_201407 ADD SUBPARTITION D_MDO_USER_VISIT_20140702 VALUES (20140702);

清空分区：
ALTER TABLE A TRUNCATE PARTITION P_A ;

清空子分区：
alter table vgopdw.TDW_MM_USE_MUSIC_BUSI_D truncate subpartition D_USE_MUSIC_BUSI_20140801;

删除子分区：
ALTER TABLE vgopdw.TDW_MDO_USER_VISIT_D DROP SUBPARTITION D_MDO_USER_VISIT_20140702;

重命名分区：
ALTER TABLE VGOPDW.TDW_MDO_USER_VISIT_D RENAME PARTITION M_USER_VISIT_20140701 TO M_MDO_USER_VISIT_201407;
ALTER TABLE VGOPDW.TDW_MDO_USER_ORDER_APP_D RENAME SUBPARTITION M_USER_VISIT_20140701 TO D_MDO_USER_ORDER_APP_20140701;