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

分区表为解决大表数据查询效率问题，分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。

Oracle的分区表可以包括多个分区，每个分区都是-个独立的段（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;

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

转载于:http://blog.itpub.net/29827022/viewspace-1609283/