Oracle分区表

      在大数据量访问面前，查询性能无疑是重要因素。对Oracle来说，一般的表都是单表单段的，即一个表中对应一个表段，所有跟此表相关的信息都会存储在此表段上。为了在大数据量面前提升性能，Oracle又提供了一种单表多段的表，这种表可以将一张表的数据，按照不同的组织策略，将数据组织在不同的表段上，而且不同的表段也可以放在不同的表空间中，这样在查询的时候，只要通过对应的策略就能找到数据所在的表段，而无需查找其他表段上的数据，这无疑节省了很多查询的时间，这种表就是分区表。

      下面举个具体例子，来看看分区表有哪几种，如何使用。

 

首先我们先要用管理员用户来创建几个表空间。

 

 

CREATE TABLESPACE exercise DATAFILE 'E:\OracleDataFile\exercise.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
CREATE TABLESPACE exercise1 DATAFILE 'E:\OracleDataFile\TS1\exercise1.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
CREATE TABLESPACE exercise2 DATAFILE 'E:\OracleDataFile\TS2\exercise2.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
CREATE TABLESPACE exercise3 DATAFILE 'E:\OracleDataFile\TS3\exercise3.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
CREATE TABLESPACE exercise4 DATAFILE 'E:\OracleDataFile\TS4\exercise4.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

 

然后我们需要创建一个用户，并给与权限

 

 

CREATE USER usera IDENTIFIED BY 123 DEFAULT TABLESPACE exercise TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON exercise ACCOUNT UNLOCK;
GRANT CONNECT TO usera;
GRANT RESOURCE TO usera;

 

接下来介绍几种常用的分区表

 

1.范围分区：根据数据表的某个字段的范围来划分表段。创建代码如下：

 

 

CREATE TABLE table1
(
  tid VARCHAR2(18) NOT NULL PRIMARY KEY,
  tname VARCHAR2(20) NOT NULL,
  tage INT NOT NULL 
)
PARTITION  BY RANGE (tage)
(
  PARTITION AGE1 VALUES LESS THAN (1) TABLESPACE exercise1,
  PARTITION AGE2 VALUES LESS THAN (18) TABLESPACE exercise1,
  PARTITION AGE3 VALUES LESS THAN (45) TABLESPACE exercise1,
  PARTITION AGE4 VALUES LESS THAN (MAXVALUE) TABLESPACE exercise1
);

 

 上面的语句是根据特定的4个特定的年龄段进行分区，这样在查询的时候根据年龄值的大小，oracle会自动的锁定对应的分区进行查找。

 如果是按照时间进行范围分区，则代码为：

 

 

CREATE TABLE table2
(
  tid VARCHAR2(18) NOT NULL PRIMARY KEY,
  tname VARCHAR2(20) NOT NULL,
  tdate DATE DEFAULT SYSDATE
)
PARTITION BY RANGE (tdate)
(
  PARTITION DATE1 VALUES LESS THAN(DATE '2012-1-1'),
  PARTITION DATE2 VALUES LESS THAN(DATE '2012-3-1'),
  PARTITION DATE3 VALUES LESS THAN(DATE '2012-6-1'),
  PARTITION DATE4 VALUES LESS THAN(DATE '2012-9-1')
);

 

 

2.哈希分区：根据Oracle提供的哈希算法，来对某一个字段进行分区。创建代码如下：

 

 

CREATE TABLE table3
(
  tid VARCHAR2(18) NOT NULL PRIMARY KEY,
  tname VARCHAR2(20) NOT NULL,
  tdate DATE DEFAULT SYSDATE
)
PARTITION BY HASH(tdate)
(
  PARTITION T1 TABLESPACE exercise1,
  PARTITION T2 TABLESPACE exercise2,
  PARTITION T3 TABLESPACE exercise3,
  PARTITION T4 TABLESPACE exercise4
);

 

 

3.列表分区：根据数据表中某一字段的具体值来划分表段。创建代码如下：

 

 

CREATE TABLE table4
(
  tid VARCHAR2(18) NOT NULL PRIMARY KEY,
  tname VARCHAR2(20) NOT NULL,
  tsex CHAR(2) CHECK(tsex in ('男','女'))
)
PARTITION BY LIST(tsex)
(
  PARTITION man VALUES ('男'),
  PARTITION woman VALUES ('女')
);

 

 

4.复合分区：上述三种分区混合使用。创建代码如下：

 

CREATE TABLE table5
(
  tid VARCHAR2(18) NOT NULL PRIMARY KEY,
  tname VARCHAR2(20) NOT NULL,
  tage INT NOT NULL,
  tdate DATE DEFAULT SYSDATE
)
PARTITION BY RANGE (tage)
SUBPARTITION BY HASH (tdate)
SUBPARTITIONS 4
(
  PARTITION AGE1 VALUES LESS THAN (1) TABLESPACE exercise1,
  PARTITION AGE2 VALUES LESS THAN (18) TABLESPACE exercise1,
  PARTITION AGE3 VALUES LESS THAN (45) TABLESPACE exercise1,
  PARTITION AGE4 VALUES LESS THAN (MAXVALUE) TABLESPACE exercise1
);