oracle分表优化,oracle分区表和索引创建性能优化及分区add,drop,split,merge

的分区表可以包括多个分区，每个分区都是-个独立的段(SEGMENT)，可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

分区类型有范围分区(range)，哈希分区(hash)，列表分区(list)，范围－哈希复合分区(range-hash)，范围－列表复合分区(range-list)。

此处仅以range分区举例：

1.创建分区表

SQL>create table aa_part(

task_id varchar2(21) not null,

create_date date

)

partition by range(create_date)

(

partition p_1 values less than(to_date('2010-01-01','yyyy-mm-dd')),

partition p_2 values less than(to_date('2011-01-01','yyyy-mm-dd')),

partition p_3 values less than(to_date('2012-01-01','yyyy-mm-dd'))

);

SQL>table created

使用：select * from aa_part;

select * from aa_part partition (p_1);

查看表分区

select * from user_segments where segment_name='AA_PART';

SEGAMENT_NAME PARTITION_NAME

AA_PART P_1

AA_PART P_2

AA_PART P_3

省略插入数据过程........

通过表分析还可以看到各分区的数据量,可通过此方法定点trucate或者delete分区表(如这p_2)较大的历史数据,配合merge into历史分区达到性能优化效果，及新增分区解决新时期数据。

begin

execdbms_stats.gather_schema_stats( ownname => 'SCOTT', tablename=> AA_PART', cascade=>true,estimate_percent => 100,degree => 8);

end;

select * from dba_part_col_statistics where table_name ='AA_PART' and column_name='TASK_ID';

TABLE_NAME PARTITION_NAME COLUMN_NAME NUM_DISTINCT

AA_PART       P_1                       TASK_ID         100

AA_PART       P_2                       TASK_ID         100000000

AA_PART       P_3                       TASK_ID         100

2.建索引

全局索引和普通表索引类似

create index idx_task_id on aa_part(task_id);

重建：alter index idx_task_id rebuild;

分区索引

create index idx_create_date on aa_part(create_date) local;

重建：alter index  idx_create_date rebuild partition p_X;

查看表有哪些分区及是否可用及是否分区

select * from all_indexes where table_name='AA_PART';

TABLE_NAME INDEX_NAME STATUS  NUM_ROWS

AA_PART  IDX_TASK_ID      VALID             100000

AA_PART  IDX_CREATE_DATE  N/A            0

如果status为VALID说明此索引为全局索引，N/A则表示为分区索引索要进一步查询

select * from user_ind_partitions where index_name='IDX_CREATE_DATE';

INDEX_NAME          PARTITION_NAME STATUS

IDX_CREATE_DATE P_1 USABLE

IDX_CREATE_DATE P_2 USABLE

IDX_CREATE_DATE P_3 USABLE

查看某一分区有多少本地索引是否生效

select * from user_ind_partitions where partition_name='P_1';

INDEX_NAME PARTITION_NAME STATUS

IDX_CREATE_DATE P_1 USABLE

索引失效可以重建

alter index idx_task_id rebuild;

alter index idx_create_date rebuild partition p_X;

3.分区操作

注意分区操作可能造成索引失效，可以加上update global indexes子句。

truncate:

alter table aa_part truncate partition p_1 update global indexes;

新增

alter table aa_part add partition p_4 values less than (to_date('2013-01-01','yyyy-mm-dd'));

删除

alter table aa_part drop partition p_4 update global indexes;

分离

alter table aa_part split partition p_3 at (to_date('2013-01-01','yyyy-mm-dd')) into (partition p_4,p_3);

合并(只能两两合并，超出两个要报错http://blog.itpub.net/29827022/viewspace-1608486/)

alter table aa_part merge partition p_1,p_2 into partition p_2 update global indexes;