Oracle的局部本地分区索引

199次阅读

共计 2936 个字符，预计需要花费 8 分钟才能阅读完成。

环境：Oracle 12.2.0.1

注：未确定 10g,11g 是否有这些特性。现在基本不用 10g, 主要用 12c，11g。

毫无疑问，这种特性对于 dba 或者实施人员而言显得很重要，尤其当你的数据库主要用于 olap 或者 dw（数据仓库)环境的时候。很多时候，如果需要在一个巨大的表上创建新的索引，例如这个表示一个基站性能数据，可能整张表有 3 - 5 亿条。

如果按照以往的操作，明显是非常可怕的事情：

消耗巨量的时间
不一定成功
可能影响业务的进行

然后，有了局部本地分区索引就不一样了。

以下就是本人的试验！

create table t_tab_columns(
owner varchar2(128) NOT NULL,
table_name varchar2(128) NOT NULL,
column_name varchar2(128) NOT NULL,
data_type varchar2(128),
data_type_mod varchar2(3),
data_type_owner varchar2(128),
data_length number,
data_precision number,
data_scale number,
nullable varchar2(1),
column_id number,
default_length number,
num_distinct number,
collation varchar2(100)
)
partition by list(owner)
(
partition p_sys values (‘SYS’,’SYSTEM’) ,
partition p_sys_other values(‘MDSYS’,’CTXSYS’,’DVSYS’,’WMSYS’,’LBACSYS’) indexing off,
partition p_USERS values (‘LZF’,’EMCUSER’)
);

—
INSERT INTO t_tab_columns
select
owner,
table_name,
column_name,
data_type,
data_type_mod,
data_type_owner,
data_length,
data_precision,
data_scale,
nullable,
column_id,
default_length,
num_distinct,
collation
from dba_tab_columns
WHERE OWNER IN (‘SYS’,’SYSTEM’,’MDSYS’,’CTXSYS’,’DVSYS’,’WMSYS’,’LBACSYS’,’LZF’,’EMCUSER’);
commit;
—2.1 创建索引
create index idx_Tab_columns_colname on t_tab_columns(column_name) local indexing partial
– 验证
select segment_name,partition_name from user_segments where segment_name=’IDX_TAB_COLUMNS_COLNAME’;
SEGMENT_NAME PARTITION_NAME
IDX_TAB_COLUMNS_COLNAME P_SYS
IDX_TAB_COLUMNS_COLNAME P_USERS
–**** 证明：有起到作用
–2.2 创建索引而不带 indexing partial
create index idx_Tab_columns_dt on t_tab_columns(data_type) local ;
– 验证
select segment_name,partition_name from user_segments where segment_name=’IDX_TAB_COLUMNS_DT’;
SEGMENT_NAME PARTITION_NAME
IDX_TAB_COLUMNS_DT P_SYS
IDX_TAB_COLUMNS_DT P_SYS_OTHER
IDX_TAB_COLUMNS_DT P_USERS
–**** 证明：是否创建索引完全取决于 indexing partial 字句。
–2.3 如果把本来可以索引的，调整为不可索引，会如何了？
alter table t_tab_columns modify partition p_users indexing off;
– 验证
select segment_name,partition_name from user_segments where segment_name=’IDX_TAB_COLUMNS_COLNAME’;
SEGMENT_NAME PARTITION_NAME
IDX_TAB_COLUMNS_COLNAME P_SYS

select segment_name,partition_name from user_segments where segment_name=’IDX_TAB_COLUMNS_DT’;
SEGMENT_NAME PARTITION_NAME
IDX_TAB_COLUMNS_DT P_SYS
IDX_TAB_COLUMNS_DT P_SYS_OTHER
IDX_TAB_COLUMNS_DT P_USERS
–**** 证明：只影响采用了 indexing partial 的索引，会删除对应分区的索引。

–2.4 如果把本来不可以索引的，调整为可索引，会如何了？
alter table t_tab_columns modify partition p_users indexing on;
– 验证
select segment_name,partition_name from user_segments where segment_name=’IDX_TAB_COLUMNS_COLNAME’;
SEGMENT_NAME PARTITION_NAME
IDX_TAB_COLUMNS_COLNAME P_SYS
IDX_TAB_COLUMNS_COLNAME P_USERS
select segment_name,partition_name from user_segments where segment_name=’IDX_TAB_COLUMNS_DT’;
结果略。
–**** 证明：修改表分区的 indexing 属性，只影响采用了 indexing partial 的索引，会重建对应分区的索引。

通过修改所有分区的 indexing 属性（为 off），然后创建 indexing partial 对的本地索引，最后通过一个定时任务把对应分区修改为 indexing on，就可以逐步创建每个分区的索引。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-10/147212.htm

正文完

星哥玩云-微信公众号