非分区表变为分区表_Oracle数据库分区表整理笔记

关键词

partition 分区
subpartition 辅助分区
已经存在的表没有方法可以直接转化为分区表。
分区索引

一、分区表类型

1、范围分区

1-1、按指定要求划分

假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。

CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR2(30) NOT NULL,LAST_NAME VARCHAR2(30) NOT NULL,TEL VARCHAR2(15) NOT NULL,EMAIL VARCHAR2(80),STATUS CHAR(1) )PARTITION BY RANGE (CUSTOMER_ID) (PARTITION CUS_PART1VALUES LESS THAN (100000) TABLESPACE CUS_TS01,PARTITION CUS_PART2VALUES LESS THAN (200000) TABLESPACE CUS_TS02 );

1-2、按时间划分

CREATE TABLE ORDER_ACTIVITIES ( ORDER_ID      NUMBER(7) NOT NULL,     ORDER_DATE    DATE,     TOTAL_AMOUNT NUMBER,     CUSTOTMER_ID NUMBER(7),     PAID           CHAR(1) )  PARTITION BY RANGE (ORDER_DATE) (   PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')),   PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) ,   PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) );

1-3 MAXVALUE

CREATE TABLE RangeTable (   idd   INT PRIMARY KEY ,   iNAME VARCHAR(10),   grade INT ) PARTITION  BY  RANGE (grade) (       PARTITION  part1 VALUES  LESS  THEN (1000) ,       PARTITION  part2 VALUES  LESS  THEN (MAXVALUE) );

2、哈希分区

对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

CREATE TABLE HASH_TABLE(COL NUMBER(8),INF VARCHAR2(100))PARTITION BY HASH (COL)(PARTITION PART01 TABLESPACE HASH_TS01,PARTITION PART02 TABLESPACE HASH_TS02,PARTITION PART03 TABLESPACE HASH_TS03);

3、列表分区

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

create table graderecord  (    sno varchar2(10),    sname varchar2(20),    dormitory varchar2(3),    grade int  )  partition by list(dormitory)  (    partition d229 values('229'),    partition d228 values('228'),    partition d240 values('240')  )  ;

以上根据宿舍来进行列表分区，插入与范围分区实验相同的数据，做查询如下：

select * from graderecord partition(d229); select * from graderecord partition(d228);  select * from graderecord partition(d240);  

d229分区所得数据如下：

d228分区所得数据如下：

d240分区所得数据如下：

4、组合分区

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。在10g中组合分区有两种：range-hash，range-list。注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区。表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

4-1、基于范围分区和列表分区

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-2、基于范围分区和散列分区

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));

二、分区表的一些操作语句

1、查看分区情况

 select * from user_tab_partitions where table_name ='tableName';

2、查看分区数据

select * from tablename partiton(p1);

3、合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：

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

4、修改分区

添加：alter table tablename add partition p4 values less than(value);
删除：alter table tablename drop partiton p4;
截断分区 截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。
alter table tablename truncate partiton p2;

5、拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分
ALTER TABLE TABLENAME SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6、可移动分区

alter table tablename enable row movement;

三、分区索引

1、 Locally partitioned index(局部分区索引)

Ⅰ：局部前缀索引：以分区键作为索引定义的第一列
Ⅱ：局部非前缀索引：分区键没有作为索引定义的第一列

create table local_index_example( id number(2), name varchar2(50), sex varchar2(10))partition by range (id)(  partition part_1 values less than (5),  partition part_2 values less than (10))--创建局部前缀索引；分区键(id)作为索引定义的第一列create index local_prefixed_index on local_index_example (id, name) local;--创建局部非前缀索引；分区键未作为索引定义的第一列create index local_nonprefixed_index on local_index_example (name, id) local;

什么时候该使用前缀索引？什么时候该使用非前缀索引？

对于该使用前缀还是非前缀索引，这完全取决于你的实际需求，你应该尽量从实际角度出发选择合适的索引方式以充分利用到其分区消除的特性。

如果查询首先访问索引的话，它能否实现分区消除完全取决于查询中使用的谓词(即Where筛选条件)；

比如用上面的 local_index_example 表举例，现有两个查询：

①： select … from local_index_example where id = :id and name = :name;

②： select … from local_index_example where name = :name;

对于以上两个查询来说，如果查询第一步是走索引的话，则：

局部前缀索引 local_prefixed_index 只对 ① 有用；

局部非前缀索引 local_nonprefixed_index 则对 ① 和 ② 均有用；

如果你有多个类似 ① 和 ② 的查询的话，则可以考虑建立局部非前缀索引；如果平常多使用查询 ① 的话，则可以考虑建立局部前缀索引；

总之，重点是你要尽可能保证查询包含的谓词允许索引分区消除

      ***延伸阅读：绑定变量(bind variable)***

绑定变量是查询中的一个占位符，形如 :xxx 。

例如，要获取 emp 表中 empno 为 123 的记录，你可以执行如下两种查询：

①： select * from emp where empno = 123;

②： 先将绑定变量 :empno 的值设置为 123，再执行查询
select * from emp where empno = :empno;

第一种查询使用了 123 这样一个直接量(常量)，如果有多个这样的查询的话，则每一个查询对数据库来说都是一个全新的查询，Oracle每次都会对查询进行解析、限定(命名解析)、安全性检查、优化等(简单地讲，就是每次执行时都要先编译)；
第二种查询使用了 :empno 这样一个绑定变量，变量值在查询时动态指定，这个查询只会在第一次时编译，随后Oracle会把查询计划存储在一个共享池中方便以后重用，如此当以后再传入不同的 empno 值进行查询时，Oracle会直接调用第一次解析好的这个执行计划进行执行，这样查询效率将大幅提升

Oracle只保证索引分区内部的唯一性，跨分区的唯一性无法保证。

如果你想使用局部索引实现唯一性约束的话，则必须让分区键实现唯一性约束(UNIQUE 或 PRIMARY KEY)

2、Globally partitioned index(全局分区索引)

与局部索引不同，全局索引只有一类，即全局前缀索引(prefixed global index)，索引分区键必须作为索引定义的第一列，否则执行会报错。

--创建示例表，按id进行范围分区create table global_index_example( id number(2), name varchar2(50), age number(2))partition by range (id)(  partition part_1 values less than (5),  partition part_2 values less than (10))--创建按age进行范围分区的全局分区索引create index global_index on global_index_example(age) globalpartition by range (age) (  partition index_part_1 values less than (20),  partition index_part_2 values less than (maxvalue))

全局索引要求最高分区(即最后一个分区)必须有一个值为 maxvalue 的最大上限值，这样可以确保底层表的所有行都能放在这个索引中；

一般情况下，大多数分区操作(如删除一个旧分区)都会使全局索引无效，除非重建全局索引，否则无法使用。