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;