学习ORACLE分区表-分区索引心得

1.创建范围分区表：
create table txn_current(id number,name varchar2(10),txn_date date)
partition by range(txn_date)
(partition p1 values less than(to_date('2013-10-01','yyyy-mm-dd')),
 partition p2 values less than(to_date('2013-11-01','yyyy-mm-dd')),
 partition p3 values less than(to_date('2013-12-01','yyyy-mm-dd')),
 partition p4 values less than(to_date('2014-01-01','yyyy-mm-dd'))
);


2.创建局部前缀索引：
create index idx_txn_current_1 on txn_current(txn_date) local;


3.查看一下执行计划：
set autotrace on


select * from txn_current where txn_date=to_date('2013-11-01','yyyy-mm-dd');


4.创建局部复合前缀索引：
create index idx_txn_current_2 on txn_current(txn_date,name) local;


5.查看一下执行计划：
select * from txn_current where txn_date=to_date('2013-11-01','yyyy-mm-dd') and name='test';


6.创建全局范围分区索引：
create index idx_txn_current_3 on txn_current(name)
global partition by range(name)
(partition ip1 values less than('a'),
 partition ip2 values less than('g'),
 partition ip3 values less than('o'),
 partition ip4 values less than('z'),
 partition ip5 values less than(MAXVALUE)
);


7.创建全局HASH分区索引：
create index idx_txn_current_4 on txn_current(name)
global partition by hash(name)
partitions 6;


8.创建局部非前缀分区索引：
create index idx_txn_current_5 on txn_current(name) local;




-------------------------------
在线重定义，普通表定义为分区表：
create table t(id number primary key,na varchar2(10),da date);


begin
  dbms_redefinition.can_redef_table('scott','t',dbms_redefinition.cons_use_pk);
end;
/


create table t_t(id number,na varchar2(10),da date)
partition by range(da)
(partition p1 values less than(to_date('2013-10-01','yyyy-mm-dd')),
 partition p2 values less than(to_date('2013-11-01','yyyy-mm-dd')),
 partition p3 values less than(to_date('2013-12-01','yyyy-mm-dd')),
 partition p4 values less than(to_date('2014-01-01','yyyy-mm-dd'))
);


begin
  dbms_redefinition.start_redef_table('scott','t','t_t',
                                      'ID ID,NA NA,DA DA',
                                      dbms_redefinition.cons_use_pk);
end;
/


declare
  num_errors PLS_INTEGER;
begin
  dbms_redefinition.copy_table_dependents('scott','t','t_t',
                                          dbms_redefinition.cons_orig_params,
                                          TRUE,TRUE,TRUE,TRUE,num_errors);
end;
/


select object_name,base_table_name,ddl_txt from dba_redefinition_errors;


begin
  dbms_redefinition.sync_interim_table('scott','t','t_t');
end;
/


begin
  dbms_redefinition.finish_redef_table('scott','t','t_t');
end;
/


drop table t_t purge;