Oracle分区表

一、  分区表的概念

随着数据表中的数据量不断增大的过程，查询数据库数据的速度就会变慢，进而导致相关程序性能下降，这时候就应该考虑对数据表进行分区。分区表就是通过使用分区技术，将一张大表，拆分放到多个表分区（segment）中，从而提升数据访问性能。逻辑上表 依然是一张完整的表，只是将表中的数据在物理上存放到多个表空间中，但需要注意的是，这些表空间所使用的的块大小（block_size）须一致。另外，包含LONG以及LONG RAW字段的表无法使用分区（有待验证）。

二、  分区表的优点

1、 分区表对于用户是透明的，因此，不需要再分区后，对代码进行修改

2、 由于各个分区相对独立，当一个分区处于维护或者故障时，不会影响到其他分区的正常使用

3、 在面对用户请求时，只需要检索需要的分区，从而提升性能

4、 针对分区进行的数据库维护，能降低维护时长

 

三、  分区键

分区键就是决定表中的数据行应该属于哪一个分区的一组数据列。在执行DML操作时，Oracle会根据分区键选择分区。

分区表相关，已经存在的表没有方法可以直接转化为分区表。不过oracle提供了在线重定义表的功能（未验证过）。

 

四、  操作方法

1.    先建立（或确认）表空间（tablespace）

 

（网上的语句例子，建立了一个很具体的表空间，可以在PLSQL界面操作）

/*第1步：创建临时表空间  */
createtemporary tablespace yuhang_temp 
tempfile'D:\oracledata\yuhang_temp.dbf' 
size50m  
autoextendon  
next50m maxsize 20480m  
extentmanagement local;  
 
/*第2步：创建数据表空间  */
createtablespace yuhang_data  
logging  
datafile'D:\oracledata\yuhang_data.dbf' 
size50m  
autoextendon  
next50m maxsize 20480m  
extentmanagement local;  
 
/*第3步：创建用户并指定表空间  */
createuser yuhang identified by yuhang  
defaulttablespace yuhang_data  
temporarytablespace yuhang_temp;  
 
/*第4步：给用户授予权限  */
grantconnect,resource,dba to yuhang;

 

2.    使用建表语句建表，并指定分区方法

分区方法：

1.    范围分区

定义：根据分区键定义时给出的键值范围，根据实际的取值，进行分区的选择。一般可以用来处理键值是数字的情况。

规则：对每一个分区的定义使用values less than (value) 字句，（value）表示该分区的上限值。

最后的一个分区可以是values less than (maxvalue)，maxvalue表示该分区存储高于其他分区上限值的数据行，用于存放无法预先定义的键值的数据。（就是一开始不知道这个对象最高可能是多少）

语句：

createtable test

(       id number not null,

        first_name varchar2(30) not null,

        last_name varchar2(30) not null,

        phone varchar2(30) not null,

        email varchar2(80),

        status char(1),

        constraint test_id primary key (id)

)

partitionby range (id)

(       partition test_part1 values less than (100000) tablespace test_ts01,

        partition test_part2 values less than (200000) tablespace test_ts02,

        partition test_part3 values less than (maxvalue) tablespace test_ts03

);

 

 

2.    列分区

定义：列分区主要依据分区键定义时给出的取值列表，根据实际的取值，进行分区的选择，然后将数据存储到相应分区。一般用来存储某个字段比较固定，而且是字符（不方便比较）的情况（比如使用所属部门键、性别键来进行分区）

规则：对每一个分区的定义使用values (value) 字句，对最后一个分区（不确定键值的）可以使用values（default）字句

语句：

createtable problem_tickets

(       problem_id number(7) not null,

        description varchar2(2000),

        customer_id number(7) not null,

        date_entered date not null,

        status varchar2(20),

        constraint problem_tic_id primary key (problem_id)

)

partitionby list (status)

(       partition prob_active values ('active') tablespace test_ts04,

        partition prob_inactive values ('inactive') tablespace test_ts05,

        partition prob_other values(default) tablespace test_ts06

);

 

3.    Hash分区

定义：对键值进行hash计算后决定放到某个分区

规则：由hash算法决定分区分布，不能自己控制分区情况，语法是hash(col) subpartitions n(分区表数目)store in(x1,x2,tx3……xn)

语句：

createtable hash_table

(       col number(8),

        inf varchar2(100)

)

partitionby hash(col)

(       partition part01 tablespace test_ts04,

        partition part02 tablespace test_ts05,

        partition part03 tablespace test_ts06

);

 

4.    组合分区

Oracle11G中可以结合上面的方法进行组合分区。但是根分区只能是范围分区或者列分区，不能是hash分区。子分区方法可以是范围分区、列分区和hash分区

例子：

createtable sales

 

(        product_id varchar2(5),

 

         sales_date date,

 

         sales_cost number(10),

 

         status varchar2(30)

 

)

 

partitionby range (sales_date) subpartition by list (status)

 

(partitionp1 values less than(to_date('2016-06-01','yyyy-mm-dd')) tablespace test_ts07

(subpartition p1sub1 values ('active') tablespace test_ts07,

subpartition p1sub2 values('inactive') tablespace test_ts07

),

partitionp2 values less than(to_date('2016-07-01','yyyy-mm-dd')) tablespace test_ts08

 (subpartition p2sub1 values('active')tablespace test_ts08,

subpartition p2sub2 values('inactive') tablespace test_ts08

)

);

 

createtable dinya_test

 (     transaction_id number,

        item_id number(8) not null,

        item_description varchar2(300),

        transaction_date date,

        constraint dinya_test_id primary key (transaction_id)

 )

partitionby range(transaction_date) subpartition by hash(transaction_id) 

subpartitions3

storein (test_ts11,test_ts12,test_ts13)

 (partition part_01 values lessthan(to_date('2016-06-01','yyyy-mm-dd')),

partitionpart_02 values less than(to_date('2016-12-01','yyyy-mm-dd')),

partitionpart_03 values less than(maxvalue)

 );