Oracle10g 表分区

时间:2024-10-24 12:37:08

1.分区的原因

(1)Tables greater than 2GB should always be considered for partitioning.

(2)Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current                   month's data is updatable and the other 11 months are read only.

2.分区的优点

  • 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度;
  • 增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
  • 维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
  • 均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。

3.分区类型

  • 范围分区(range);
  • 哈希分区(hash);
  • 列表分区(list);
  • 范围-哈希复合分区(range-hash);
  • 范围-列表复合分区(range-list)。

3.1 范围分区(range)

语法:      可以有多个列,都小于时数据 插入该分区;如果第一列值相等则看第二列的值是否小于,小于则插入;值1值2都是该分区的上限同是也是上个相邻分区的上限。

  PARTITION BY RANGE (列1,列2。。。)

  (//PARTITION _Name---分区名称,TABLESPACE_Name---表空间名称(可以不指定,不指定就是在当前的表空间)

    PARTITION     PARTITION _Name     VALUES LESS THAN (值1,值2,...)  TABLESPACE  TABLESPACE_Name,

    PARTITION     PARTITION _Name     VALUES LESS THAN (值1,值2,...)  TABLESPACE  TABLESPACE_Name,

    PARTITION     PARTITION _Name     VALUES LESS THAN (maxvalue,maxvalue。。。)  TABLESPACE  TABLESPACE_Name

  )

CREATE TABLE CUSTOMER 

    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONEVARCHAR2(15) NOT NULL, 
    EMAILVARCHAR2(80), 
    STATUS       CHAR(1) 

PARTITION BY RANGE (CUSTOMER_ID) 

    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 
)

3.2哈希分区(hash)

语法://可以有多个列,Oracle管理的算法,我们无需关心

partition by hash(列名1,列名2)

  (

       partition   PARTITION _Name    tablespace    TABLESPACE_Name,

       partition   PARTITION _Name    tablespace    TABLESPACE_Name,

       partition   PARTITION _Name    tablespace    TABLESPACE_Name

  );

create table test

(

transaction_id number primary key,

item_id number(8) not null

)

partition by hash(transaction_id)

(

partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03

);

3.2 列表分区(list)

语法://只能一个列   ===将该列中值等于values中的记录放于对应的分区中。

  PARTITION  BY  LIST (列) 
  ( //values--该列中对应的值
          PARTITION  PARTITION _Name   VALUES ('guangdong','beijing')  TABLESPACE    TABLESPACE_Name, 
          PARTITION  PARTITION _Name   VALUES ('shanghai','nanjing')     TABLESPACE    TABLESPACE_Name 
  );

CREATE  TABLE  ListTable

    id    INT  PRIMARY  KEY , 
    name  VARCHAR (20), 
    area  VARCHAR (10) 

PARTITION  BY  LIST (area) 

    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb, 
    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb 
);

3.4 范围-哈希复合分区(range-hash)

语法://hash也是可以指定tablespace的,都符合自己单独语法

   partition by range(transaction_date) subpartition by hash(transaction_id)  subpartitions 3 store in ( PARTITION _Name , PARTITION _Name , PARTITION _Name ) 
   ( 
       partition   PARTITION _Name   values less than(to_date(‘2006-01-01','yyyy-mm-dd'))  TABLESPACE  TABLESPACE_Name, 
       partition   PARTITION _Name   values less than(to_date(‘2010-01-01','yyyy-mm-dd'))  TABLESPACE  TABLESPACE_Name, 
       partition   PARTITION _Name   values less than(maxvalue)  TABLESPACE  TABLESPACE_Name
   );

create table dinya_test 
 ( 
   transaction_id number primary key, 
   item_id number(8) not null, 
   item_description varchar2(300), 
   transaction_date date 
 ) 
 partition by range(transaction_date) subpartition by hash(transaction_id)  subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03) 
 ( 
     partition part_01 values less than(to_date(‘2006-01-01','yyyy-mm-dd')), 
     partition part_02 values less than(to_date(‘2010-01-01','yyyy-mm-dd')), 
     partition part_03 values less than(maxvalue) 
 );

3.5 范围-列表复合分区(range-list)

语法://先range然后再list--各自符合自己独自的语法

  PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
  (
     PARTITION PARTITION _Name  VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE  TABLESPACE_Name
     ( 
        SUBPARTITION   PARTITION _Name    VALUES ('ACTIVE')    TABLESPACE      TABLESPACE_Name, 
        SUBPARTITION   PARTITION _Name    VALUES ('INACTIVE')    TABLESPACE     TABLESPACE_Name
     ), 
     PARTITION PARTITION _Name  VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE    TABLESPACE_Name
     ( 
        SUBPARTITION   PARTITION _Name  VALUES ('ACTIVE')       TABLESPACE     TABLESPACE_Name, 
        SUBPARTITION   PARTITION _Name  VALUES ('INACTIVE')     TABLESPACE      TABLESPACE_Name
     ) 
  )

CREATE TABLE SALES 
 (
  PRODUCT_ID VARCHAR2(5),
  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('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009 
   ( 
      SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
      SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
   ), 
   PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 
   ( 
      SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009, 
      SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009 
    ) 
)

4.分区维护

4.1添加分区

  ALTER TABLE tableName ADD PARTITION   PARTITION _Name   VALUES  LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

  PS:以上添加的分区界限应该高于最后一个分区界限。

4.2添加子分区

  ALTER TABLE SALES MODIFY PARTITION  father_PARTITION_Name  ADD   SUBPARTITION   son_PARTITION _Name   VALUES('COMPLETE');

4.3删除分区与子分区

  ALTER TABLE SALES DROP PARTITION   PARTITION_Name;

  ALTER TABLE SALES DROP SUBPARTITION  PARTITION_Name;

PS:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除

4.4截断分区与子分区

  ALTER TABLE SALES TRUNCATE PARTITION  PARTITION_Name;

  ALTER TABLE SALES TRUNCATE SUBPARTITION  PARTITION_Name;

  PS:截断某个分区是指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。

4.5重命名表分区

  ALTER TABLE SALES RENAME PARTITION old_PARTITION_Name   TO   new_PARTITION_Name;

4.6拆分分区

  ALTER TABLE SALES SBLIT PARTITION  PARTITION_Name  AT(TO_DATE('2003-02-01','YYYY-MM-DD'))  INTO (PARTITION                          PARTITION_Name1,PARTITION  PARTITION_Name2);

PS:拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分

4.7多分区联合查询----单个分区的也可以查看了吧!

  select sum( *) from
  (select count(*) cn from t_table_SS PARTITION (PARTITION_Name1)
  union all
  select count(*) cn from t_table_SS PARTITION (PARTITION_Name2)
  );

4.8合并分区

  ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

 ps:合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。

--显示数据库所有分区表的信息:
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 p5;

http://www.blogjava.net/rabbit/archive/2013/01/08/393955.html     ============分区中索引的应用