学习ORACLE分区表-分区索引心得
来源:互联网 发布:华为手机服务软件 编辑:程序博客网 时间:2024/06/05 11:43
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;
- 学习ORACLE分区表-分区索引心得
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- oracle 分区表,分区索引
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- 简单ORACLE分区表、分区索引
- ava 动态代理机制分析及扩
- Java实践 — SSH远程执行Shell脚本
- C++ 空类默认产生的成员
- 基于xmpp实现android端实现即时通讯---思路(一)
- 无聊的递归过程
- 学习ORACLE分区表-分区索引心得
- poj1321棋盘问题
- ios设备获取存储空间
- 探索Google App Engine背后的奥秘(3)- Google App Engine的简介
- Request中 请求路径 的几种区别
- MFC 工具栏 Toolbar 自定义 位图
- Android新闻客户端开发4--显示新闻详细内容业务逻辑实现
- 水池问题的lua语言算法(面试题分析:我的Twitter技术面试失败了)
- oracle 常用sql函数