partition table and partition index

Normal 0 0 2 false false false MicrosoftInternetExplorer4 使用分区有很多优点：

l         增加可用性，一个分区有问题，不影响其他的分区

l         减少维护工作量

l         均衡I/O，减少争用。

l         提高查询速度

l         分区对user是透明

Table

1).range partition

create table part1(

id number primary key,

name varchar2(10),

dt date

)

partition by range(dt)

(

partition p1 values less than (to_date('2012/01/01','yyyy/mm/dd')) tablespace users,

partition p2 values less than (to_date('2013/01/01','yyyy/mm/dd')) tablespace test,

partition p3 values less than (maxvalue) tablespace users

)

 

SQL> insert into part1 values(4,'a',sysdate);

已建立 1 個資料列.

SQL> insert into part1 values(5,'b',sysdate-200);

已建立 1 個資料列.

SQL> insert into part1 values(6,'c',sysdate+200);

已建立 1 個資料列.

SQL> commit;

確認完成.

SQL> select * from part1 partition(p2);

        ID NAME                 DT

---------- -------------------- --------------

         4 a                    01-7月 -12

         1 a                    01-7月 -12

 

Range partition可以是两个字段或多个字段

create table part2(

id number primary key,

dt date,

des varchar2(20)

)

partition by range(id,dt)

(

partition p1 values less than (1,to_date('2012/01/01','yyyy/mm/dd')) tablespace users,

partition p2 values less than (10,to_date('2013/01/01','yyyy/mm/dd')) tablespace test,

partition p3 values less than (maxvalue,maxvalue) tablespace users

)

 

 

2).hash 分区      à散列分区

主要用去取值不确定的情况下采用的方法

create table part_hash(

id number primary key,

name varchar2(20),

dt date)

partition by hash(id)

(

partition p1 tablespace users,

partition p2 tablespace test

)

SQL> select * from part_hash partition (p1);

        ID NAME                                     DT

---------- ---------------------------------------- --------------

         2 b                                        30-6月 -12

SQL> select * from part_hash partition (p2);

        ID NAME                                     DT

---------- ---------------------------------------- --------------

         1 a                                        01-7月 -12

         3 c                                        28-6月 -12

或者采用如下方法：

create table part_hash1(

id number primary key,

name varchar2(20),

dt date)

storage(initial 1024k)              --表空间的初始分配1024k

partition by hash(id)

partitions 3

store in (users,test,system);

 

 

3).list partition  à列表分区

主要是用于某个列的值是可以枚举的

create table part4

(id number(1),

 name varchar2(10)

 )

 partition by list(id)

 (partition p1 values (1) tablespace users,

  partition p2 values (2) tablespace test,

  partition p3 values (default) tablespace users

 )

 

insert into part4 values(1,'A')

insert into part4 values(2,'B')

insert into part4 values(3,'B')

 

select * from part4 partition (p3)

 

 

4).组合分区

a.Range-hash partition

b.range-list partition

c.range-range partition

d.list-range partition

e.list-list partition

f.list-hash partition

eg:

create table part5

(id int,

 name varchar2(10)

)

partition by range(id)

subpartition by hash(name)

subpartitions 2 store in (users,test)

(partition p1 values less than (10) tablespace users,

 partition p2 values less than (20) tablespace test,

 partition p3 values less than (maxvalue) tablespace users

)

先进行range partition，然后子分区按照hash进行。

 

 

5).interval分区(11g新增)

Interval分区是range分区的一种加强版本。可以实现equi-sized范围分区的自动化。随着数据的增加会分配更多的分区，并自动创建新的分区和本地索引。

create table part6

(id int,

 name varchar2(20),

 dt date

 )

partition by range(dt)

interval (numtoyminterval(1,'month'))

(

partition p1 values less than(to_date('2012/01/01','yyyy/mm/dd'))

)

 

6).外键分区(11g新增)

SQL> create table part1

  2  (id int,name varchar2(10))

  3  partition by range(id)

  4  (partition p1 values less than (100),

  5   partition p2 values less than (200),

  6   partition p3 values less than (maxvalue)

  7  );

 

SQL> alter table part1 add primary key(id);

 

Table altered.

 

SQL> create table part7

  2  (id int not null,                      à这个必须要，否则出现ora-14652

  3   name varchar2(20),

  4   constraint part7_fk foreign key(id) references part1(id)

  5  )

  6  partition by reference (part7_fk)

  7  /

 

Table created.

 

7).虚拟列分区

11g以前的版本，只有分区键存在于表中才可以对该表实施分区操作。而11g则不需要。

create table part3

(id int,

 name varchar2(20),

 id1 int generated always as (to_number(substr(to_char(id),1,1)))

)

partition by list(id1)

( partition p1 values (3),

  partition p2 values (5),

  partition p3 values (default)

)

 

 

 

 

 

8).表分区的管理

Range partition:

增加partition:

	Range Partition	Hash Partition	Add Partition	组合 Partition
增加分区	Add partition	Add partition	Add partition	Add partition
合并分区		Coalesce Partition		Modify partition Coalesce Subpartition…
删除分区	Drop partition		Drop Partition	Drop partition
交换分区	Exchange Partition	Exchange Partition	Exchange Partition	Exchange Partition Exchange Subpartition
融合分区	Merge partitions		Merge Partitions	Merge partitions
修改分区增加值			Modify partition Add values
修改分区删除值			Modify Partition Drop values
移动分区	Move partition	Move partition	Move partition	Move partition
更名分区	Rename partition	Rename partition	Rename partition	Rename partition
分割分区	Split partition		Splite partition	Split partition
舍弃分区	Truncate partition	Truncate partition	Truncate partition	Truncate partition Truncate subpartition

 

增加分区

SQL> alter table part1 add partition p3 values less than (to_date('2014/01/01','yyyy/mm/dd')) [tablespace xxx];

 

已更改表格.

 

SQL> alter table part1 add partition p4 values less than (maxvalue) [tablespace xxx];

 

合并分区(hash partition)

SQL> alter table part_hash coalesce partition;

SQL> alter table part5 modify partition p1 coalesce subpartition;

 

删除分区

SQL> alter table part1 drop partition p3;

如果有全局index，删除partition之后，index会标识为unusable，需要重建index。Alter index xxx rebuild

如果有约束：alter table xxx disable constraints cons_xxx

Alter table xxxx drop partition px;

Alter table xxx enable constraints cons_xxx

 

融合分区

create index part1_index on part1(id,name)

 local

 (partition p1,

  partition p2,

  partition p3,

  partition p4

 )

SQL> alter table part1 merge partitions p3,p4 into partition p5;

索引变成无效

SQL> select status from user_indexes where index_name='PART1_INDEX'

  2  ;

 

STATUS

----------------

N/A

SQL> alter table part1 modify partition p5 rebuild unusable local indexes;

 

 

 

 

 

非分区表转换为分区表

1).检测是否可以转换

SQL> exec dbms_redefinition.can_redef_table('scott','test')

 

已順利完成 PL/SQL 程序.

2).创建临时表并化分区

create table test_temp

(owner varchar2(30) not null,

 object_name varchar2(30) not null,

 object_id number not null,

 object_type varchar2(30)

 )

partition by range(object_id)

(partition p1 values less than (400) tablespace users,

 partition p2 values less than (950) tablespace test,

 partition p3 values less than (maxvalue) tablespace users

)

3).开始重定义表

SQL> exec dbms_redefinition.start_redef_table('scott','test','test_temp')

 

已順利完成 PL/SQL 程序.

4).创建索引限制及触发器

SQL> var v_num number

SQL> exec dbms_redefinition.copy_table_dependents('scott','test','test_temp',dbms_redefinition.cons_orig_params,true,true,true,true,:v_num,true);

 

已順利完成 PL/SQL 程序.

http://yangtingkun.itpub.net/post/468/494878 (其中遇见问题参考)

5).同步表内容

如果同步的数据很大，则可以考虑通过prebuilt table的方法建立物化视图

Exec dbms_redefinition.sync_interim_table(‘scott’,’test’,’test_temp’)

6).结束转换过程

SQL> exec dbms_redefinition.finish_redef_table('scott','test','test_temp')

 

已順利完成 PL/SQL 程序.

 

7).删除临时表test_temp

Drop table test_temp

 

其中可以使用dbms_redefinition.abort_redef_table(‘scott’,’test’,’test_temp’)中途退出该过程。

 

 

 

 

 

 

Index

Local index:

l         本地索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区数目，本地索引的分区机制和表的分区机制一样。

l         如果本地索引的的索引列伊分区键开头，则称之为前缀索引

l         如果本地索引的索引列不是以分区键开头，不包含分区键之，称之为非前缀索引

l         位图索引只能为本地分区索引

l         本地所有多应用于数据仓库环境中olap。

l         本地所有只支持分区内的唯一性，无法支持表上的唯一性，如果要使用本地索引给表做唯一性约束，则约束中必须要包括分区键列。

create index test_index on test(object_id)

local

(

partition p1 tablespace users,

partition p2 tablespace test,

partition p3 tablespace tbs

)

alter table test add primary key(object_Id) using index test_index

 

或者:

 

create index test_index1 on test(object_id) local

   前缀索引，索引中包含分区键

select dbms_metadata.get_ddl('INDEX','TEST_INDEX1','SCOTT')  from dual

 

create index test_index2 on test(object_type) local

非前缀索引，索引中不包含分区键

select dbms_metadata.get_ddl('INDEX','TEST_INDEX2','SCOTT')  from dual

 

SQL> select index_name,table_name,partitioning_type,locality,alignment from user_part_indexes where table_name='TEST';

 

INDEX_NAME      TABLE_NAME           PARTITIONING_T LOCALITY     ALIGNMENT

--------------- -------------------- -------------- ------------ ------------------------

TEST_INDEX1     TEST                 RANGE          LOCAL        PREFIXED

TEST_INDEX2     TEST                 RANGE          LOCAL        NON_PREFIXED

 

 

 

 

 

 

 

 

 

 

Global index:

l         全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。

l         全局索引可以分区，也可以不分区索引。全局索引必须是前缀索引，即全局索引的索引列必须是一索引分区键作为前几列。

l         全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebuild若干个分区甚至是整个索引。

l         全局索引多用于oltp系统中。

l         全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。

l         9i后对分区表组move或者truncate时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换可用性。

l         全局分区索引只能是B树索引，而且只能是前缀索引。

Eg:

create table test1

(id int,

 name varchar2(20),

 desc1 varchar2(20)

)

partition by range(id)

(

partition p1 values less than (1000) tablespace users,

partition p2 values less than (5000) tablespace test,

partition p3 values less than (maxvalue) tablespace tbs

)

创建索引:

create index test1_global_index on test1(id) global

partition by range(id)    

(partition p1 values less than (4000) tablespace users,

 partition p2 values less than (maxvalue) tablespace test

 )

 

SQL> alter table test1 drop partition p3;

 

已更改表格.

索引的状态发生变化

SQL> select index_name,partition_name,status from user_ind_partitions where index_name='TEST1_GLOBAL_INDEX';

 

INDEX_NAME           PARTITION_ STATUS

-------------------- ---------- ----------------

TEST1_GLOBAL_INDEX   P1         UNUSABLE

TEST1_GLOBAL_INDEX   P2         UNUSABLE

 

必须加上前置码

SQL> l

  1  create index test1_global_index1 on test1(object_type) global

  2  partition by range(id)

  3  (partition p1 values less than (4000),

  4   paritition p2 less than (maxvalues)

  5* )

SQL> /

partition by range(id)

                     *

 ERROR 在行 2:

ORA-14038: GLOBAL 分割索引必須加上前置碼

 

不能和位图索引使用

SQL> l

  1  create bitmap index test1_global_index1 on test1(object_type) global

  2  partition by range(id)

  3  (partition p1 values less than (4000),

  4   paritition p2 less than (maxvalues)

  5* )

SQL> /

create bitmap index test1_global_index1 on test1(object_type) global

*

 ERROR 在行 1:

ORA-25113: GLOBAL 可能無法與點陣圖索引一起使用

 

 

 

 

 

 

 

操作索引部分

	索引类型	range	hash/list	组合
删除索引分区	global	drop partition
	local
重建索引分区	global	rebuild partition
	local	rebuild partition	rebuild partition	rebuild subpartition
重命名索引分区	global	rename partition
	local	rename partition	rename partition	rename subpartition
分割索引分区	global	split partition
	local

 

 

 

 

SQL> alter index test1_global_index drop partition p1;

 

已更改索引.

 

SQL> alter index test1_global_index rebuild partition p2;

 

已更改索引.

 

SQL> alter index test1_global_index rename partition p2 to p1;

 

已更改索引.

 

user_part_tables           分区表信息

user_tab_partitions        分区分层信息

user_tab_subpartitions     子分区分层信息

user_part_key_columns       分区表的分区键列

user_subpart_key_columns    组合分区表的子分区键列

user_part_col_statistics    表的分区的列统计信息和直方图

user_part_col_statistics

user_part_histograms       表的分区上的直方图的直方图的直方图数据

user_subpart_histograms

user_part_indexes           分区索引的分区信息

user_ind_partitions     分区层次的分区信息，分区的存储参数

user_ind_subpartitions

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-734534/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24237320/viewspace-734534/