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;
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;