分区操作

 

 

#######################################################

建分区

CREATE TABLESPACE  rptfact201001 LOGGING DATAFILE 'E:\oracle\tblspace\rptfact201001.ora' SIZE 1M ;

CREATE TABLESPACE  rptfact201001a LOGGING DATAFILE 'E:\oracle\tblspace\rptfact201001a.ora' SIZE 1M ;

CREATE TABLESPACE  rptfact201001i LOGGING DATAFILE 'E:\oracle\tblspace\rptfact201001i.ora' SIZE 1M ;

CREATE TABLESPACE  rptfact201002 LOGGING DATAFILE 'E:\oracle\tblspace\rptfact201002.ora' SIZE 1M ;

CREATE TABLESPACE  rptfact201002a LOGGING DATAFILE 'E:\oracle\tblspace\rptfact201002a.ora' SIZE 1M ;

CREATE TABLESPACE  rptfact201002i LOGGING DATAFILE 'E:\oracle\tblspace\rptfact201002i.ora' SIZE 1M ;

 

 

CREATE TABLE SALES

(

    PRODUCT_ID CHAR(5) PRIMARY KEY ,

    SALES_DATE DATE,

    SALES_COST NUMBER(10),

    STATUS VARCHAR2(20)

)   //下面先按范围分区，再按列表分区

PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)

(

   PARTITION P1 VALUES LESS THAN(TO_DATE('2010-04-01','YYYY-MM-DD'))

   (

      SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact201001a,

      SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact201001i

   ),

   PARTITION P2 VALUES LESS THAN (TO_DATE('2010-07-01','YYYY-MM-DD'))

   (

      SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact201002a,

      SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact201002i

   )

);

 

insert into sales(PRODUCT_ID,SALES_DATE,SALES_COST,STATUS) values('001',TO_DATE('2010-03-01','YYYY-MM-DD'),101,'ACTIVE');

insert into sales(PRODUCT_ID,SALES_DATE,SALES_COST,STATUS) values('002',TO_DATE('2010-03-01','YYYY-MM-DD'),102,'INACTIVE');

insert into sales(PRODUCT_ID,SALES_DATE,SALES_COST,STATUS) values('003',TO_DATE('2010-03-01','YYYY-MM-DD'),103,'a');

 

insert into sales(PRODUCT_ID,SALES_DATE,SALES_COST,STATUS) values('101',TO_DATE('2010-05-01','YYYY-MM-DD'),201,'ACTIVE');

insert into sales(PRODUCT_ID,SALES_DATE,SALES_COST,STATUS) values('102',TO_DATE('2010-05-01','YYYY-MM-DD'),202,'INACTIVE');

insert into sales(PRODUCT_ID,SALES_DATE,SALES_COST,STATUS) values('103',TO_DATE('2010-05-01','YYYY-MM-DD'),203,'a');

 

#######################################################

 

CREATE TABLESPACE  ts_space01 LOGGING DATAFILE 'E:\oracle\tblspace\ts_space01.ora' SIZE 1M ;

CREATE TABLESPACE  ts_space02 LOGGING DATAFILE 'E:\oracle\tblspace\ts_space02.ora' SIZE 1M ;

CREATE TABLESPACE  ts_space03 LOGGING DATAFILE 'E:\oracle\tblspace\ts_space03.ora' SIZE 1M ;

 

//先按范围，后按散列分区

create table ts_test

(

  ts_id number primary key,

  item_id number(8) not null,

  item_desc varchar2(300),

  ts_date date

)

partition by range(ts_date)

subpartition by hash(ts_id)

subpartitions 3 store in (ts_space01,ts_space02,ts_space03)

(

    partition part_01 values less than(to_date('2010-01-01','yyyy-mm-dd')),

    partition part_02 values less than(to_date('2011-01-01','yyyy-mm-dd')),

    partition part_03 values less than(maxvalue)

);

 

insert into ts_test(ts_id,item_id,item_desc,ts_date) values(1,10,'A00',to_date('2009-01-01','yyyy-mm-dd') );

insert into ts_test(ts_id,item_id,item_desc,ts_date) values(2,20,'B00',to_date('2010-01-01','yyyy-mm-dd') );

insert into ts_test(ts_id,item_id,item_desc,ts_date) values(3,30,'C00',to_date('2011-01-01','yyyy-mm-dd') );

 

查看表空间的名字和所属物理文件地址

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

 

 

//分析表，这样索引才能生效

analyze table ts_test compute statistics;

 

截断分区

ALTER TABLE SALES TRUNCATE PARTITION P1;

ALTER TABLE SALES TRUNCATE SUBPARTITION P1SUB1;

ALTER TABLE ts_test TRUNCATE PARTITION part_01;

删除分区

alter table sales drop SUBPARTITION P2SUB3;  //删除子分区

 

//查看分区信息

select PARTITION_NAME from USER_TAB_PARTITIONS;

select PARTITION_NAME, SUBPARTITION_NAME from USER_TAB_SUBPARTITIONS ;

 

 

//给一个父分区上增加一个子分区（列表分区）

ALTER TABLE SALES MODIFY PARTITION P1 ADD SUBPARTITION P1SUB3 VALUES ('a') TABLESPACE rptfact201001a;

ALTER TABLE SALES MODIFY PARTITION P2 ADD SUBPARTITION P2SUB3 VALUES ('a') TABLESPACE rptfact201002a;