Oracle 12C 新特性之非分区表转分区表online clause(不停业务+索引有效)
来源:互联网 发布:淘宝上的衣服干净吗 编辑:程序博客网 时间:2024/06/05 16:14
12c以前非分区表需要转换为分区, 如果不停业务的话可以使用在线重定义,只有在表进行切换的时候会有短暂的锁表。 12c 中alter table online clause 实现了表上现有的索引有效,又不停业务。
测试一:非分区表转分区表,索引转换为oracle内部规则。
-- 建非分区表
create table andy_clause as select * from user_objects where object_id is not null;
--创建非分区表索引
create index idx_oid on andy_clause( object_id );
create index idx_ctime_oname on andy_clause( created, object_name );
create bitmap index idx_b_otype on andy_clause(object_type);
--查看表索引
SQL>
col column_name for a40
SELECT index_name, column_name, descend,COLUMN_POSITION FROM user_ind_columns WHERE table_name = 'ANDY_CLAUSE';
INDEX_NAME COLUMN_NAME DESC COLUMN_POSITION
------------------------- ---------------------------------------- ---- ---------------
IDX_OID OBJECT_ID ASC 1
IDX_CTIME_ONAME CREATED ASC 1
IDX_CTIME_ONAME OBJECT_NAME ASC 2
IDX_B_OTYPE OBJECT_TYPE ASC 1
-- 查看索引状态
SQL>
col index_name for a25
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where table_name ='ANDY_CLAUSE';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
ANDY_CLAUSE IDX_OID VALID 0 1
ANDY_CLAUSE IDX_CTIME_ONAME VALID 0 1
ANDY_CLAUSE IDX_B_OTYPE VALID 0 1
--转换当前表为分区以 object_id 字段, interval分区表,直接 update index,使用ORACLE有自己的索引转换规则。
SQL>
alter table andy_clause modify
partition by range (object_id) interval (5)
(
partition p1 values less than (73527)
) online
update indexes;
Table altered.
说明:update index ,没有指定写local 或global选项, ORACLE有自己的索引转换规则
--查看表索引
SQL>
col column_name for a40
SELECT index_name, column_name, descend,COLUMN_POSITION FROM user_ind_columns WHERE table_name = 'ANDY_CLAUSE';
INDEX_NAME COLUMN_NAME DESC COLUMN_POSITION
------------------------- ---------------------------------------- ---- ---------------
IDX_OID OBJECT_ID ASC 1
IDX_CTIME_ONAME CREATED ASC 1
IDX_CTIME_ONAME OBJECT_NAME ASC 2
IDX_B_OTYPE OBJECT_TYPE ASC 1
-- 查看索引状态
SQL>
col TABLE_NAME for a25
col index_name for a25
select table_name,index_name,INDEX_TYPE,status,blevel,leaf_blocks from user_Indexes where table_name ='ANDY_CLAUSE';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
ANDY_CLAUSE IDX_OID N/A 0 4
ANDY_CLAUSE IDX_CTIME_ONAME VALID 0 1
ANDY_CLAUSE IDX_B_OTYPE N/A 0 4
SQL> COL INDEX_OWNER FOR A20;
SQL> select INDEX_OWNER,index_name,status,blevel,leaf_blocks from dba_ind_partitions where INDEX_OWNER='C##ANDY'and Index_name='IDX_OID';
INDEX_OWNER INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
-------------------- ------------------------- -------- ---------- -----------
C##ANDY IDX_OID USABLE 0 1
C##ANDY IDX_OID USABLE 0 1
C##ANDY IDX_OID USABLE 0 1
C##ANDY IDX_OID USABLE 0 1
SQL>
COL INDEX_OWNER FOR A20;
col index_name for a25
select INDEX_OWNER,index_name,status,blevel,leaf_blocks from dba_ind_partitions where INDEX_OWNER='C##ANDY'and Index_name='IDX_B_OTYPE';
INDEX_OWNER INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
-------------------- ------------------------- -------- ---------- -----------
C##ANDY IDX_B_OTYPE USABLE 0 1
C##ANDY IDX_B_OTYPE USABLE 0 1
C##ANDY IDX_B_OTYPE USABLE 0 1
C##ANDY IDX_B_OTYPE USABLE 0 1
说明:user_Indexes 是主要计量一个index的相关信息的,如果分区索引为local index 它不能记录状态,就为 N/A ,分区 local index要用视图 dba_ind_partitions 查状态。
-- 查看分区情况
SQL>
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='ANDY_CLAUSE';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ -----------
ANDY_CLAUSE P1 1 USERS 73527
ANDY_CLAUSE SYS_P341 2 USERS 73532
ANDY_CLAUSE SYS_P343 3 USERS 73592
ANDY_CLAUSE SYS_P342 4 USERS 73597
测试二:非分区表转分区表,索引转换为自己设定规则。
-- 建非分区表
SQL> create table andy_clause02 as select * from user_objects where object_id is not null;
--创建非分区表索引
SQL>
create index idx_oid on andy_clause02( object_id );
create index idx_ctime_oname on andy_clause02( created, object_name );
create bitmap index idx_b_otype on andy_clause02(object_type);
--转换当前表为分区以 object_id 字段, interval分区表,update indexes 时,自己指定local 或global选项
SQL>
alter table andy_clause02 modify
partition by range (object_id) interval (5)
(
partition p1 values less than (73527)
) online
update indexes
( idx_oid local,
idx_ctime_oname global partition by range (created)
(
partition ix2_p1 values less than (date '2017-05-01'),
partition ix2_p2 values less than (maxvalue)
)
);
Table altered.
--查看表索引
SQL>
col index_name for a25
col COLUMN_NAME for a25
SELECT index_name, column_name, descend,COLUMN_POSITION FROM user_ind_columns WHERE table_name = 'ANDY_CLAUSE02';
INDEX_NAME COLUMN_NAME DESC COLUMN_POSITION
------------------------- ------------------------- ---- ---------------
IDX_OID OBJECT_ID ASC 1
IDX_CTIME_ONAME CREATED ASC 1
IDX_CTIME_ONAME OBJECT_NAME ASC 2
IDX_B_OTYPE OBJECT_TYPE ASC 1
-- 查看索引状态
SQL>
col index_name for a25
col TABLE_NAME for a25
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where table_name ='ANDY_CLAUSE02';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
ANDY_CLAUSE02 IDX_OID N/A 1 113
ANDY_CLAUSE02 IDX_CTIME_ONAME N/A 2 432
ANDY_CLAUSE02 IDX_B_OTYPE N/A 1 3
测试一:非分区表转分区表,索引转换为oracle内部规则。
-- 建非分区表
create table andy_clause as select * from user_objects where object_id is not null;
--创建非分区表索引
create index idx_oid on andy_clause( object_id );
create index idx_ctime_oname on andy_clause( created, object_name );
create bitmap index idx_b_otype on andy_clause(object_type);
--查看表索引
SQL>
col column_name for a40
SELECT index_name, column_name, descend,COLUMN_POSITION FROM user_ind_columns WHERE table_name = 'ANDY_CLAUSE';
INDEX_NAME COLUMN_NAME DESC COLUMN_POSITION
------------------------- ---------------------------------------- ---- ---------------
IDX_OID OBJECT_ID ASC 1
IDX_CTIME_ONAME CREATED ASC 1
IDX_CTIME_ONAME OBJECT_NAME ASC 2
IDX_B_OTYPE OBJECT_TYPE ASC 1
-- 查看索引状态
SQL>
col index_name for a25
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where table_name ='ANDY_CLAUSE';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
ANDY_CLAUSE IDX_OID VALID 0 1
ANDY_CLAUSE IDX_CTIME_ONAME VALID 0 1
ANDY_CLAUSE IDX_B_OTYPE VALID 0 1
--转换当前表为分区以 object_id 字段, interval分区表,直接 update index,使用ORACLE有自己的索引转换规则。
SQL>
alter table andy_clause modify
partition by range (object_id) interval (5)
(
partition p1 values less than (73527)
) online
update indexes;
Table altered.
说明:update index ,没有指定写local 或global选项, ORACLE有自己的索引转换规则
--查看表索引
SQL>
col column_name for a40
SELECT index_name, column_name, descend,COLUMN_POSITION FROM user_ind_columns WHERE table_name = 'ANDY_CLAUSE';
INDEX_NAME COLUMN_NAME DESC COLUMN_POSITION
------------------------- ---------------------------------------- ---- ---------------
IDX_OID OBJECT_ID ASC 1
IDX_CTIME_ONAME CREATED ASC 1
IDX_CTIME_ONAME OBJECT_NAME ASC 2
IDX_B_OTYPE OBJECT_TYPE ASC 1
-- 查看索引状态
SQL>
col TABLE_NAME for a25
col index_name for a25
select table_name,index_name,INDEX_TYPE,status,blevel,leaf_blocks from user_Indexes where table_name ='ANDY_CLAUSE';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
ANDY_CLAUSE IDX_OID N/A 0 4
ANDY_CLAUSE IDX_CTIME_ONAME VALID 0 1
ANDY_CLAUSE IDX_B_OTYPE N/A 0 4
SQL> COL INDEX_OWNER FOR A20;
SQL> select INDEX_OWNER,index_name,status,blevel,leaf_blocks from dba_ind_partitions where INDEX_OWNER='C##ANDY'and Index_name='IDX_OID';
INDEX_OWNER INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
-------------------- ------------------------- -------- ---------- -----------
C##ANDY IDX_OID USABLE 0 1
C##ANDY IDX_OID USABLE 0 1
C##ANDY IDX_OID USABLE 0 1
C##ANDY IDX_OID USABLE 0 1
SQL>
COL INDEX_OWNER FOR A20;
col index_name for a25
select INDEX_OWNER,index_name,status,blevel,leaf_blocks from dba_ind_partitions where INDEX_OWNER='C##ANDY'and Index_name='IDX_B_OTYPE';
INDEX_OWNER INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
-------------------- ------------------------- -------- ---------- -----------
C##ANDY IDX_B_OTYPE USABLE 0 1
C##ANDY IDX_B_OTYPE USABLE 0 1
C##ANDY IDX_B_OTYPE USABLE 0 1
C##ANDY IDX_B_OTYPE USABLE 0 1
说明:user_Indexes 是主要计量一个index的相关信息的,如果分区索引为local index 它不能记录状态,就为 N/A ,分区 local index要用视图 dba_ind_partitions 查状态。
-- 查看分区情况
SQL>
select table_name,partition_name,PARTITION_POSITION,tablespace_name,HIGH_VALUE from user_tab_partitions where table_name='ANDY_CLAUSE';
TABLE_NAME PARTITION_NAME PARTITION_POSITION TABLESPACE_NAME HIGH_VALUE
------------------------- ------------------------- ------------------ ------------------------------ -----------
ANDY_CLAUSE P1 1 USERS 73527
ANDY_CLAUSE SYS_P341 2 USERS 73532
ANDY_CLAUSE SYS_P343 3 USERS 73592
ANDY_CLAUSE SYS_P342 4 USERS 73597
测试二:非分区表转分区表,索引转换为自己设定规则。
-- 建非分区表
SQL> create table andy_clause02 as select * from user_objects where object_id is not null;
--创建非分区表索引
SQL>
create index idx_oid on andy_clause02( object_id );
create index idx_ctime_oname on andy_clause02( created, object_name );
create bitmap index idx_b_otype on andy_clause02(object_type);
--转换当前表为分区以 object_id 字段, interval分区表,update indexes 时,自己指定local 或global选项
SQL>
alter table andy_clause02 modify
partition by range (object_id) interval (5)
(
partition p1 values less than (73527)
) online
update indexes
( idx_oid local,
idx_ctime_oname global partition by range (created)
(
partition ix2_p1 values less than (date '2017-05-01'),
partition ix2_p2 values less than (maxvalue)
)
);
Table altered.
--查看表索引
SQL>
col index_name for a25
col COLUMN_NAME for a25
SELECT index_name, column_name, descend,COLUMN_POSITION FROM user_ind_columns WHERE table_name = 'ANDY_CLAUSE02';
INDEX_NAME COLUMN_NAME DESC COLUMN_POSITION
------------------------- ------------------------- ---- ---------------
IDX_OID OBJECT_ID ASC 1
IDX_CTIME_ONAME CREATED ASC 1
IDX_CTIME_ONAME OBJECT_NAME ASC 2
IDX_B_OTYPE OBJECT_TYPE ASC 1
-- 查看索引状态
SQL>
col index_name for a25
col TABLE_NAME for a25
select table_name,index_name,status,blevel,leaf_blocks from user_Indexes where table_name ='ANDY_CLAUSE02';
TABLE_NAME INDEX_NAME STATUS BLEVEL LEAF_BLOCKS
------------------------- ------------------------- -------- ---------- -----------
ANDY_CLAUSE02 IDX_OID N/A 1 113
ANDY_CLAUSE02 IDX_CTIME_ONAME N/A 2 432
ANDY_CLAUSE02 IDX_B_OTYPE N/A 1 3
0 0
- Oracle 12C 新特性之非分区表转分区表online clause(不停业务+索引有效)
- Oracle 12C 新特性之move (非分区表)table online
- Oracle 12c Study之-- 分区表新特性
- oracle非分区表,转分区表
- oracle 非分区表转分区表
- oracle创建分区表,以及非分区表转分区表
- ORACLE分区表、分区索引(转)
- ORACLE分区表、分区索引详解(转)
- ORACLE分区表、分区索引详解(转)
- Oracle 11g新特性系统分区表
- Oracle之分区表和索引优化,分区表字段回表
- 非分区表B-树索引叶子结点索引项特性
- Oracle 分区表(转)
- oracle分区表&索引(例子)
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- oracle 分区表,分区索引
- ORACLE分区表、分区索引
- Spring security3.0的基本概念学习参考高手作品
- windows的消息管理机制
- 通过Spring Session实现Session集中管理
- 关于JSON
- 欢迎使用CSDN-markdown编辑器
- Oracle 12C 新特性之非分区表转分区表online clause(不停业务+索引有效)
- size_t与ptrdiff_t
- safai中ifram使用JS吊起app中的一点小坑
- 写给刚入门的前端工程师的前后端交互指南
- Android Sensor详解(7)sensor framework层详解第二篇
- ubuntu 17 install wine
- 我所知道的关于 script 标签的一切
- 视频直播解决方案
- HDU5839-Special Tetrahedron(判断四点共面,叉乘求平面垂直向量)