oracle的partition,oracle partitionTable and partitionIndex

分区表： 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个“表空间”(物理文件上)，这样查询数据时，不至于每次都扫描整张表而只是从当前的分区查到所要的数据大大提高了数据查询的速度。

表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。 分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

什么时候使用分区表：

1、表的大小超过2GB。

2、表中包含历史数据，新的数据被增加到新的分区中

表分区有以下优点： 1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

4、均衡I/O：可以把不同的分区映射到不同磁盘以平衡I/O，改善整个系统性能

/**

? The optimizer eliminates (prunes) partitions that do

not need to be scanned.

? Maintenance commands can be applied at the partition

level.

? Join operations can be optimized to join “by the

partition.”

? Partitions can be load-balanced across physical

devices.

? A partition can be moved from one tablespace to

another.

? A partition can be divided at a user-defined value.

? Partitioning can isolate subsets of rows that must be

treated individually.

? A partition can be dropped, added, or truncated.

? SELECT, UPDATE, INSERT, and DELETE operations can

be applied on a partition level instead of a table level

**/

分区的条件及特性

共性：不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等，

个性：各个分区可以有不同的物理属性，比如pctfree, pctused, and tablespaces.

分区独立性：即使某些分区不可用，其他分区仍然可用。

特殊性：含有LONG、LONGRAW数据类型的表不能进行分区

/**

? Logical attributes:

– Normal table structure (columns, constraints)

– Partition type

– Keys and values

– Row movement 行移动是表级别的

? Physical attributes:

– Tablespace

– Extent sizes, block attributes

**/

oracle Rolling Window Operations

当你只保留最近一年的数据的时候按月分区 当第二年的第一个月的分区开始的时候删除第一年的第一个月的数据

分区的方式

? Range partition

? Hash partition.

? List partition.

? Interval partition 自动创建的范围　　数据库在指定的时间间隔。

? System partition 允许应用程序显式行映射到任意分区

? 虚拟列分区

? 组合分区

如果插入的数据不满足分区规则，会报ORA-14400错误

Partition Key：

行映射到单个分区的分区键

一张分区表或分区索引只可以有一个，但是并不代表他只允许有一个列，可以是多列的值最多不允许超过16个

系统分区不使用分区键

Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

行映射到基于列值范围的分区

使用Range 分区的时候，要记住几条规则：

每个分区都包含VALUES LESS THAN字名，定义了分区的上层边界。任何等于和大于分区键值的二进制值都被添加到下一个高层分区中。

所有的分区，除了第一个，如果低于VALUES LESS THAN所定义的下层边界，都放在前面的分区中。

MAXVALUE可以用来定义最高层的分区。MAXVALUE表示了虚拟的无限值

create table t_rang (id number, time date)

partition by range (time)

(

partition p1 values less than (to_date(‘2010-10-1’, ‘yyyy-mm-dd’)),

partition p2 values less than (to_date(‘2010-11-1’, ‘yyyy-mm-dd’)),

partition p3 values less than (to_date(‘2010-12-1’, ‘yyyy-mm-dd’)),

partition p4 values less than (maxvalue)

);

注意：than(括号里面只允许有to_date一个表达式 别的例如1+1都不行)

比如:

create table t_rang (id number, time date)

partition by range (id)

(

partition p1 values less than (10+50),

partition p4 values less than (maxvalue)

);

ORA-00907: 缺失右括号

如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

HASH：

创建散列分区时，必须指定以下信息

分区方法：hash

分区列

分区数量或单独的分区描述

当事先不知道需要将多少数据映射到给定范围的时候

分区的范围大小很难确定，或者很难平衡的时候

Range分区使数据得到不希望的聚集时

性能特性，如并行DML、分区剪枝和分区连接很重要的时候

这些情况下，使用hash分区比range分区更好

对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，

列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列

1、分区键值尽量不重复。

2、分区的个数为2的幂。

/**

? Partition may be defined, or just quantified

? NULL is placed in the first partition

? Number should be power of two

**/

创建hash分区有两种方法：一种方法是指定分区数量，另一种方法是指定分区的名字，

但两者不能同时指定。

1.

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

);

2.

create table dept8 (deptno number,deptname varchar2(32))

partition by hash(deptno) partitions 4 STORE IN (tablespace01,tablespace02,tablespace03);

在这里，我们指定了每个分区的表空间

Hash分区不能应用于分裂、删除和合并分区，但是Hash分区能够合并和添加(后面会有例子)

List分区

也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个

在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，

因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

null可以被指定在一个分区

List分区可以控制如何将行映射到分区中去。可以在每个分区的键上定义离散的值

不同于Range分区和Hash分区，

Range分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值的范围以外的分区组织到一起。

hash分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据的。

List分区的优点在于按照自然的方式将无序和不相关的数据集合分组。

List分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。

Range分区和Hash分区可以对多列进行分区。

create table custaddr(

id varchar2(15 byte) not null,

areacode varchar2(4 byte))

partition by list (areacode)

(

partition t_list025 values (’02’,’111′),

partition t_list372 values (‘372’),

partition t_list510 values (‘510′),

partition p_other values (default)

)

Interval(间隔)分区

间隔分区也是range分区的扩展

必须创建至少一个范围分区

其特点是自动创建范围分区

CREATE TABLE SALES_INTERVAL(ID NUMBER,time_id DATE)

PARTITION BY RANGE (time_id)

INTERVAL (NUMTOYMINTERVAL(1,’month’)) –STORE IN (tbs1,tbs2,tbs3,tbs4)

(

PARTITION P1 values less than (TO_DATE(‘1-1-2002′,’dd-mm-yyyy’)),

PARTITION P2 values less than (TO_DATE(‘1-1-2003′,’dd-mm-yyyy’)),

PARTITION P3 values less than (TO_DATE(‘1-1-2004′,’dd-mm-yyyy’)))

注意 当你Insert了数据但是rollback了 分区结构不会恢复到没有

SELECT table_name,partition_name FROM User_Tab_Partitions WHERE table_name= ‘SALES_INTERVAL’

SALES_INTERVAL P1

SALES_INTERVAL P2

SALES_INTERVAL P3

INSERT INTO SALES_INTERVAL VALUES (1,TO_DATE(‘1-2-2004′,’dd-mm-yyyy’))

COMMIT;

SELECT table_name,partition_name FROM User_Tab_Partitions WHERE table_name= ‘SALES_INTERVAL’

SALES_INTERVAL P1

SALES_INTERVAL P2

SALES_INTERVAL P3

SALES_INTERVAL SYS_P49

同时使用interval分区会带来一个问题 导致不知道分区的名字以后做分区的DML时不好维护管理 这个时候可以使用for子句，for() 括号里面的值在哪个分区就象征着那个分区

alter table orders_interval merge partitions

for(TO_DATE(’15-10-2007′,’dd-mm-yyyy’)),for(TO_DATE(’15-11-2007′,’dd-mm-yyyy’))

into partition sys_p5z;

虚拟列分区：

CREATE TABLE employees

(employee_id number(6) not null,

salary NUMBER,

commission_pct NUMBER,

total_compensation as (salary *( 1+commission_pct))

)

INSERT INTO employees VALUES(1,2,3)–ORA-00947: 没有足够的值

INSERT INTO employees VALUES(1,2,3,4)–ORA-54013: 不允许对虚拟列执行 INSERT 操作

INSERT INTO employees(employee_id,salary,commission_pct) VALUES(1,2,3);–ok

SELECT * FROM employees;

1 2 3 8

–这个时候8就是自动算出来的，然而也是不能做DML的 不可以修改的

total_compensation 可以被当做普通的列当做分区表的分区键

例如：

CREATE TABLE employees

(employee_id number(6) not null, first_name varchar2(30),

last_name varchar2(40) not null, emailvarchar2(25),

phone_number varchar2(20), hire_date date not null,

job_id varchar2(10) not null, salary number(8,2),

commission_pct number(2,2), manager_id number(6),

department_id number(4),

total_compensation as (salary *( 1+commission_pct))

)

PARTITION BY RANGE (total_compensation)

(

PARTITION p1 VALUES LESS THAN (50000),

PARTITION p2 VALUES LESS THAN (100000),

PARTITION p3 VALUES LESS THAN (150000),

PARTITION p4 VALUES LESS THAN (MAXVALUE)

);

system分区：

System partitioning:

? Enables application-controlled partitioning for

selected tables

? Provides the benefits of partitioning but the

partitioning and data placement are controlled by the

application

? Does not employ partitioning keys like other

partitioning methods

? Does not support partition pruning in the traditional

sense

CREATE TABLE systab (c1 integer, c2 integer)

PARTITION BY SYSTEM

(

PARTITION p1 TABLESPACE tbs_1,

PARTITION p2 TABLESPACE tbs_2,

PARTITION p3 TABLESPACE tbs_3,

PARTITION p4 TABLESPACE tbs_4

);

CREATE TABLE systab (c1 integer, c2 integer)

PARTITION BY SYSTEM

(

PARTITION p1 TABLESPACE tbs_1,

PARTITION p2 TABLESPACE tbs_2,

PARTITION p3 TABLESPACE tbs_3,

PARTITION p4 TABLESPACE tbs_4

);

INSERT INTO systab PARTITION (p1) VALUES (4,5);

INSERT INTO systab PARTITION (p2) VALUES (150,2);

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

组合分区在10g中有两种：range-hash，range-list。注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区

在11g有了增强

? Range top level:

– Range-Range

– Range-Hash

– Range-List

? List top level:

– List-Range

– List-Hash

– List-List

? Interval top level:

– Interval-Range

– Interval-Hash

– Interval-List

这里我只举一个简单的例子 如果有需要可以自行深入研究

create table test

(

transaction_id number primary key,

transaction_date date

)

partition by range(transaction_date) subpartition by hash(transaction_id)

subpartitions 3 store in (tablespace01,tablespace02,tablespace03)

(

partition part_01 values less than(to_date(’2009-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)

);

使用模板的复合分区：–DBA_SUBPARTITION_TEMPLATES

CREATE TABLE quarterly_regional_sales (

deptno NUMBER, item VARCHAR2(20), d DATE,

amount NUMBER, state VARCHAR2(2) , details CLOB)

PARTITION BY RANGE (d) SUBPARTITION BY LIST (state)

SUBPARTITION TEMPLATE (

SUBPARTITION northwest VALUES (‘OR’,’WA’) TABLESPACE

tbs1

LOB (details) STORE AS details1 (TABLESPACE tbs3),

SUBPARTITION others VALUES (DEFAULT) TABLESPACE

tbs2

LOB (details) STORE AS details7 (TABLESPACE tbs4)

) –这个时候每个大的分区下的子分区方式都是一样的。都是来自模板的

(

PARTITION q1_2002 VALUES LESS THAN (’01-APR-2002′),

PARTITION q2_2002 VALUES LESS THAN (’01-JUL-2002′),

PARTITION q3_2002 VALUES LESS THAN (’01-OCT-2002′),

PARTITION q4_2002 VALUES LESS THAN (’01-JAN-2003′)

);

Equipartitioning

/**

If two tables have the same partition method, partition

keys, and partition key boundaries, they are

equipartitioned.

? For composite partitioning, only one method needs to

apply.

? This is useful for tables with a common key, such as

master-detail relationships.

? A partitionwise join operation requires

equipartitioning.

? An index can be equipartitioned with the table

**/

Join Benefits:

Full Partitionwise Joins 和 Partial Partitionwise Joins

FULL partitionwise JOIN 会把较大的全表关联改写成较小的分区上的关联 发生的前提是两张表equipartitioned(有相同的Partition key)

SELECT c.cust_last_name, COUNT(*)

FROM sales s, customers c

WHERE s.cust_id = c.cust_id AND s.time_id BETWEEN

TO_DATE(’01-JUL-1999′, ‘DD-MON-YYYY’) AND

(TO_DATE(’01-OCT-1999′, ‘DD-MON-YYYY’))

GROUP BY c.cust_last_name HAVING COUNT(*) > 100;

Partial Partitionwise Joins 发生在分区表和一终身非分区表做关联 且关联条件是partition KEY 这个时候分区表叫做reference table

非分区表会参照分区表进行动态划分分区 这种情况只有在加了并行之后会出现– parallel.

1.添加分区

alter table T_TRACK add partition P_2005_04

values less than(to_date(‘2005-05-01′,’yyyy-MM-dd’))

(

subpartition P_2005_04_P1013 values(‘P1013’),

subpartition P_2005_04_P1013 values(‘P1014’),

subpartition P_2005_04_P1013 values(‘P1015’),

subpartition P_2005_04_P1013 values(‘P1016’)

)

2.删除分区

alter table T_TRACK drop partition p_2005_04;

3.添加子分区

alter table T_TRACK

modify partition P_2005_01

add subpartition P_2005_01_P1017 values(‘P1017′);

4.删除子分区

alter table T_TRACK drop subpartition p_2005_01_p1017;

5.截断一个分区表中的一个分区的数据：

alter table sales3 truncate partition sp1

这种方式会使全局分区索引无效

alter table sales3 truncate partition sp1 update indexes

这种方式全局分区索引不会无效

6.截断分区表的子分区

alter table comp truncate subpartition sub1

7.截断带有约束的分区表

a、禁用约束

alter table sales disable constraint dname_sales1

b、截断分区

alter table sales truncate partitoin dec

c、启用约束

alter table sales enable constraint dname_sales1

8.查看一个表是不是分区表

select table_name,partitioned from user_tables;

TABLE_NAME PAR

—————————— —

DEPT NO

DEPT3 YES

9.将一个表的分区从一个表空间移动到另一个表空间

a、查看分区在哪个表空间

SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,

SUBPARTITION_COUNT

FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER=’SCOTT’;

b、移动分区

alter table sales move partiton sp1 tablespace tp;

c、检查是否移动成功

SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,

SUBPARTITION_COUNT

FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER=’SCOTT’;

移动表空间后，要重建索引，否则索引会变得无效

alter index xxx rebuild

10.合并分区：

alter table sales3 merge partitons sp1,sp3 into partition sp3

合并后的分区名，不能是边界值较低的那

11. PARTITION KEY 也是可以维护的

ALTER TABLE simple MODIFY PARTITION p_high ADD VALUES ( ‘ULTRA’, ‘EXTREME’ ) ;

ALTER TABLE simple MODIFY PARTITION p_high DROP VALUES ( ‘ULTRA’ ) ;

与分区表相关的数据字典视图：

DBA_TAB_PARTITIONS

DBA_IND_PARTITIONS

DBA_TAB_SUBPARTITIONS

DBA_IND_SUBPARTITIONS

分区表，分区索引 ，普通表，普通索引可以相互组合：

一个非分区表可以有分区或非分区索引

一个分区表也可以有分区或非分区索引

/**

? Indexes can be partitioned similarly as tables.

? A partitioned index can exist on a nonpartitioned table.

? A global partitioned index uses a different partition

strategy than that of the table.

? A local partitioned index follows the strategy of the

table.

**/

Prefixed or Nonprefixed

索引的分类：

1、global

2、local，它又分成2类：

2.1、prefix：索引的第一个列等于表的分区列。

2.2、non-prefix：索引的第一个列不等于表的分区列

所以一共有三类分区索引：

global + prefix

–global + nonprefix: 不支持

local + prefix

local + nonprefix

(partition key) = (sort key)

(partition key) != (sort key)

/**

In a prefixed index, all leftmost columns of the index key

are the same as all the columns in the partition key of the

index.**/

特点：

local分区索引使用与底层表相同的机制分区;global分区索引使用与底层表不同的机制进行分区，索引按区间或散列对索引进行分区，一个全局索引分区可能指向任何表分区或所有表分区。

对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。

对于global索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。

当对分区进行DDL时，通常会导致全局索引的INVALID，必须在执行完操作后REBUILD。

局部索引比全局索引容易管理,而全局索引比较快。

局部索引多应用于数据仓库环境中，而全局索引多应用于oltp系统中

global索引分为两种：range (一定要有maxvalue的分区)和hash。

CREATE INDEX idx ON emp ( first_name )

GLOBAL PARTITION BY RANGE ( first_name )

( PARTITION x1 VALUES LESS THAN ( ‘H’ )

TABLESPACE users

, PARTITION x2 VALUES LESS THAN ( MAXVALUE )

) ;

local的可以指定表空间 也可以不指定

CREATE INDEX idx ON hr_emp( first_name )

LOCAL ;

或

CREATE INDEX idx ON hr_emp( first_name )

TABLESPACE indx04

LOCAL

( PARTITION ex1 TABLESPACE indx01

, PARTITION ex2 TABLESPACE indx02

, PARTITION ex3

);

上面已经说过 当做DDL时索引会UNUSABLE

(local的只有被DDL的分区UNUSABLE global或者非分区的则是全部UNUSABLE) 可以：

ALTER INDEX s_glo REBUILD PARTITION sg_1 TABLESPACE data03；

或者

alter table t_range move partition p1 update global indexes;–9i的时候已经支持了

/**

A table partition can be altered:

? With DML – The index is maintained.

? With DDL – The index might become UNUSABLE.

– Usually only one partition for local indexes

– The whole index for global or nonpartitioned indexes

**/

分区索引什么时候用什么样子的？根据where条件来决定建立什么样的分区索引：

1、如果where条件总是出现分区字段，比如where createdate=’xxx’。那建议建立local prefix索引。

2、如果where条件总是类似：where createdate=’xxx’ and cust_id=’xxx’，那建议在cust_id上建立local nonprefix索引。

3、如果where条件不出现分区字段，比如where cust_id=’xxx’，那只能在cust_id上建立global prefix索引。

查询索引的分区信息可以通过user_part_indexes、user_ind_partitions两个数据字典

Exchanging a Partition with a TABLE

分区和普通表交换表内数据 –仅限hash range

本质上只是换了名字 rowid没有发生改变 两个hash分区也是可以交换的

要求非分区表必须有和分区表一样的表结构。

可以对校验对partition key是否满足

create table range_t(id number, createdate date)

partition by range(createdate)

(

partition p1 values less than (to_date(‘2014-04-01′,’yyyy-mm-dd’)),

partition p2 values less than (to_date(‘2014-07-01′,’yyyy-mm-dd’)),

partition p3 values less than (to_date(‘2014-10-01′,’yyyy-mm-dd’)),

partition p4 values less than (to_date(‘2015-01-01′,’yyyy-mm-dd’))

);

insert into range_t values(1,to_date(‘2014-02-13′,’yyyy-mm-dd’));

insert into range_t values(2,to_date(‘2014-05-23′,’yyyy-mm-dd’));

insert into range_t values(3,to_date(‘2014-09-16′,’yyyy-mm-dd’));

insert into range_t values(4,to_date(‘2014-11-02′,’yyyy-mm-dd’));

commit;

SELECT * FROM range_t;

alter table range_t add partition p_max values less than(maxvalue);

–給range_t添加一个2015年1季度的分区：

alter table range_t split partition p_max

at (to_date(‘2015-04-01′,’yyyy-mm-dd’)) into (partition p5, partition p_max);

–创建一张非分区表

create table current_t as select * from range_t where 1=2;

insert into current_t select rownum, to_date(‘2015-01-01′,’yyyy-mm-dd’)+trunc(dbms_random.value(1,90)) from dba_objects where rownum<=100;

commit;

update current_t set createdate=to_date(‘2015-04-05′,’yyyy-mm-dd’) where id=100;

commit;

–2015年第一季度里没有数据

select rowid, id, createdate from range_t partition (p5);

–非分区表里有100行记录

select rowid, id, createdate from current_t;

ALTER TABLE range_t

EXCHANGE PARTITION p5

WITH TABLE current_t

WITHOUT VALIDATION ; –如果分区中有不符合条件的会报 ORA-14099: 未对指定分区限定表中的所有行 但是without validation子句就可以不校验了

–可以发现100行记录到了p5里 非分区表里没有了数据

select rowid, id, createdate from range_t partition (p5);

select rowid, id, createdate from current_t;

–再交换一下 就又回来了

ALTER TABLE range_t

EXCHANGE PARTITION p5

WITH TABLE current_t

WITHOUT VALIDATION ;

–把p5分区里不属于该分区的记录找出来：

需要借助到一张表：INVALID_ROWS

@?/rdbms/admin/utlvalid.sql

ANALYZE TABLE range_t

PARTITION (p5)

VALIDATE STRUCTURE

INTO INVALID_ROWS ;

SELECT * FROM INVALID_ROWS

可以找到不满足分区条件的记录行的ROWID 可以通过ROWID进行删除或者转移到其它的分区。