oracle表分区

1.表空间及分区表的概念：

表空间：是一个或多个文件的集合，所有的数据对象都存放在指定的表空间中。

分区表：当表中的数据不断增大后就会导致查询的速度降低，影响整个应用程序的性能，这时就应该考虑对表进行分区。表分区后，逻辑上仍然是一张完整的表，只是表中的数据在物理上存放到多个表空间（物理文件）上，这样查询数据是，不至于每次都扫描整张表。

 

2.分区功能能将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可以集体管理，也可以单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，对于应用程序而言，分区后的表与分分区的表完全相同，使用SQL DML命令访问时无需修改。

 

3.什么情况下会使用表分区：

a.表的大小超过2GB.

b.表中包含历史数据，新的数据被增加到新的分区中。

 

4.表分区的几种类型及操作方法：

a.范围分区，考虑以下规则：

   1）每个分区都必须有一个VALUES LESS子句，指定上限值

   2）所有分区，除了第一个，都会有一个隐式的下限值，就是前一个分区的上限值

   3）在最高分区中，使用MAXVALUE代表一个不确定的值。

   例子一：

CREATE TABLE CUSTOMER(

 CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,

 ORDER_DATE    DATE,

 ...

)  

//按大小分区

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 

)

 //按时间分区

PARTITION BY RANGE(ORDER_DATE)

(

PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,

  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,

  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03

)

 

b.列表分区（某列的值只有几个）：

PARTITION  BY  LIST(area)

(

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

)

 

c.散列分区（当列的值没有合适的条件时）：

 PARTITION BY HASH(COL)

(

  PARTITION PART01 TABLESPACE HASH_TS01, 
  PARTITION PART02 TABLESPACE HASH_TS02, 
  PARTITION PART03 TABLESPACE HASH_TS03 

)

 d.组合分区

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

(

   PARTITIOON P1 VALUES LESS THEN(TO_DATE('2003-01-01','yyyy-MM-dd')) TABLESPACE rptfact209

   (

      SUBPARTITION P1SUB1 VALUES('ACTIVE') TABLESPACE rptfact209,

      ...

   ),

) 

 

有关表分区的一些维护操作：

一、添加分区：ALTER TABLE  TEST ADD PARTITION P3 VALUES LESS THEN(TO_DATE('2003-06-01','yyyy-MM-dd'));添加的分区应该高于最后一个分区界限。

二、删除分区：ALTER TBALE TEST DROP PARTITION P3;

三、截断分区：指删除某个分区中的数据，并不会删除分区

ALTER TABLE TEST TRUNCATE PARTITION P3;

四、合并分区：不能讲分区合并到界限低的分区

ALTER TABLE TEST MERGE PARTITION P1，P2 INTO PARTITION P2;

五、拆分分区：不能对HASH类型的分区进行拆分

ALTER TABLE TEST SBLIT   PARTITION P2 AT(TO_DATE('2013-01-01','yyyy-MM-dd'))  INTO (PARTITION P2S1,PARTITION P2S2);

六、重命名表分区：ALTER TABLE TEST RENAME PARTITION P2S1 TO P2;

七、相关查询：

查询表上有多少个分区：

SELECT * FROM useR_TAB_PARTITIONS WHERE TABEL_NAME = 'tableName';

查询数据库所有分区表的信息：

SELECT * FROM DBA_PART_TABLES

查询当前用户所有分区表的信息：

SELECT * FROM USER_PART_TABLES