oracle表分区实操训练

        关于网上oracle表分区介绍的的文档有很多（不过大概看了下基本都是一个模板刻出来的），看完后自己实际操练了下，虽然以前的项目中也用过，但是自己并没有参与弄过，今天就在本机的库上爽了一把。

操练环境：

一个表（电子签章人员信息表）有20多万条数据，重新建个一模一样的表，并对此新表进行表分区设计

操练技能：

主要是对复合分区的练习，首先对日期进行范围分区，再对分区进行列表字分区。

实操代码：

1、创建存量数据表（里面有实际数据）

-- Create table
create table ESSC_USER
(
  user_id      VARCHAR2(255) not null,
  aac002       VARCHAR2(22),
  aac003       VARCHAR2(255),
  aac067       VARCHAR2(255),
  sign_no      VARCHAR2(255),
  sign_seq     VARCHAR2(255),
  channel_no   VARCHAR2(255),
  aab301       VARCHAR2(255),
  sign_level   VARCHAR2(255),
  region_name  VARCHAR2(255),
  valid_date   VARCHAR2(10),
  sign_date    VARCHAR2(10),
  bind_channel VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column ESSC_USER.aac002
  is '身份证号码';
comment on column ESSC_USER.aac003
  is '姓名';
comment on column ESSC_USER.sign_no
  is '签发号';
comment on column ESSC_USER.sign_seq
  is '签发序列';
comment on column ESSC_USER.channel_no
  is '签发渠道';
comment on column ESSC_USER.aab301
  is '行政区划';
comment on column ESSC_USER.sign_level
  is '签发等级';
comment on column ESSC_USER.sign_date
  is '签发日期';
-- Create/Recreate indexes 
create index INDEX_AAB301_3 on ESSC_USER (AAB301, SIGN_DATE, CHANNEL_NO)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 8M
    next 1M
    minextents 1
    maxextents unlimited
  );
create index INDEX_AAC002 on ESSC_USER (AAC002)
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 7M
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table ESSC_USER
  add primary key (USER_ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

2、创建实操训练表

（1）范围分区

//根据签发日期来分区
CREATE TABLE essc_user_copy PARTITION BY RANGE (SIGN_DATE) 
      (

      PARTITION date_01 VALUES LESS THAN (20181001) TABLESPACE CP3,

      PARTITION date_02 VALUES LESS THAN (20191001) TABLESPACE CP3,

      PARTITION date_03 VALUES LESS THAN (MAXVALUE) TABLESPACE CP3

       )
      AS SELECT * FROM essc_user where 1=2 

注意：由于本表中的SIGN_DATE字段是varchar2类型的，所以直接写日期格式，如果各位童鞋的类型是日期型则使用下列的方式转换一下：TO_DATE('20180101','YYYYMMDD')

CREATE TABLE essc_user_copy PARTITION BY RANGE (SIGN_DATE) 
      (

      PARTITION date_01 VALUES LESS THAN (TO_DATE('20180101','YYYYMMDD')) TABLESPACE CP3,

      PARTITION date_02 VALUES LESS THAN (TO_DATE('20190101','YYYYMMDD')) TABLESPACE CP3,

      PARTITION date_03 VALUES LESS THAN (MAXVALUE) TABLESPACE CP3

       )
      AS SELECT * FROM essc_user where 1=2 

（2）列表分区

//根据签发等级来分区
 CREATE TABLE essc_user_copy partition by list(sign_level)
    (
     partition pur120000 values ('1'),
     partition pur150000 values ('2')
    )
     AS SELECT * FROM essc_user where 1=2

（3）组合分区

//先根据日期范围分区，在根据签发登记进行子分区
CREATE TABLE essc_user_copy PARTITION BY RANGE(SIGN_DATE) SUBPARTITION BY LIST (sign_level)
        (

           PARTITION date_01 VALUES LESS THAN(20181001) 
                  ( 
                      SUBPARTITION SIGN_LEVEL1 VALUES ('1'), 
                      SUBPARTITION SIGN_LEVEL2 VALUES ('2') 
                  ), 
           PARTITION date_02 VALUES LESS THAN (20191001)  
                  ( 
                      SUBPARTITION SIGN_LEVEL3 VALUES ('1'), 
                      SUBPARTITION SIGN_LEVEL4 VALUES ('2') 
                  ), 
           PARTITION date_03 VALUES LESS THAN (MAXVALUE) 
                 ( 
                  SUBPARTITION SIGN_LEVEL5 VALUES ('1'), 
                  SUBPARTITION SIGN_LEVEL6 VALUES ('2') 
                 ) 
        )
     AS SELECT * FROM essc_user where 1=2
注意：对于日期范围分区的时候根据其类型进行相应的转换

3、注意项

（1）各分区的名字不能相同，否则会报错。

（2）根据设定的分区，插入的数据必须在此分区范围内，如果不在则报错如下：

（3）表分区和表空间是两个不同的概念，在分区过程中每个分区默认可以写该表所在的表空间，也可写不同的表空间，表空间是实际的物理分配。如果不写则默认会有一个表空间USER

4、表分区后的常用查询操作

（1）查询相应分区的数据

//全表查询
SELECT COUNT(USER_ID) FROM essc_user_copy;
//分区查询
SELECT COUNT(USER_ID) FROM essc_user_copy PARTITION(date_02);
//子分区查询
SELECT COUNT(USER_ID) FROM essc_user_copy SUBPARTITION(SIGN_LEVEL6);

（2）查询分区情况

/*查询表上有多少分区*/
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='essc_user_copy'      
/*显示数据库所有分区表的信息：*/
SELECT * FROM DBA_PART_TABLES
/*显示当前用户可访问的所有分区表信息:*/
SELECT * FROM ALL_PART_TABLES
/*显示当前用户所有分区表的信息：*/
SELECT * FROM USER_PART_TABLES
/*显示表分区信息 显示数据库所有分区表的详细分区信息：*/
SELECT * FROM DBA_TAB_PARTITIONS
/*显示当前用户可访问的所有分区表的详细分区信息：*/
SELECT * FROM ALL_TAB_PARTITIONS
/*显示当前用户所有分区表的详细分区信息：*/
SELECT * FROM USER_TAB_PARTITIONS
/*显示子分区信息 显示数据库所有组合分区表的子分区信息：*/
SELECT * FROM DBA_TAB_SUBPARTITIONS
/*显示当前用户可访问的所有组合分区表的子分区信息：*/
SELECT * FROM ALL_TAB_SUBPARTITIONS
/*显示当前用户所有组合分区表的子分区信息：*/
SELECT * FROM USER_TAB_SUBPARTITIONS
/*显示分区列 显示数据库所有分区表的分区列信息：*/
SELECT * FROM DBA_PART_KEY_COLUMNS
/*显示当前用户可访问的所有分区表的分区列信息：*/
SELECT * FROM ALL_PART_KEY_COLUMNS
/*显示当前用户所有分区表的分区列信息：*/
SELECT * FROM USER_PART_KEY_COLUMNS
/*显示子分区列 显示数据库所有分区表的子分区列信息：*/
SELECT * FROM DBA_SUBPART_KEY_COLUMNS
/*显示当前用户可访问的所有分区表的子分区列信息：*/
SELECT * FROM ALL_SUBPART_KEY_COLUMNS
/*显示当前用户所有分区表的子分区列信息：*/
SELECT * FROM USER_SUBPART_KEY_COLUMNS
/*怎样查询出oracle数据库中所有的的分区表*/
SELECT * FROM user_tables a where a.partitioned='YES'
/*删除一个表的数据*/
truncate table table_name;
/*删除分区表一个分区的数据*/
alter table table_name truncate partition partDemo;