• oracle的局部本地分区索引


    环境:oracle 12.2.0.1

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

    毫无疑问,这种 特性对于dba或者实施人员而言显得很重要,尤其当你的数据库主要用于olap或者dw(数据仓库)环境的时候。

    很多时候,如果需要在一个巨大的表上创建新的索引,例如这个表示一个基站性能数据,可能整张表有3-5亿条。

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

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

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

    以下就是本人的试验!

     1 create table t_tab_columns(
     2 owner              varchar2(128) NOT NULL, 
     3 table_name         varchar2(128) NOT NULL, 
     4 column_name        varchar2(128) NOT NULL, 
     5 data_type          varchar2(128), 
     6 data_type_mod      varchar2(3),   
     7 data_type_owner    varchar2(128), 
     8 data_length        number,        
     9 data_precision     number,        
    10 data_scale         number,        
    11 nullable           varchar2(1),   
    12 column_id          number,        
    13 default_length     number,              
    14 num_distinct       number,        
    15 collation          varchar2(100)  
    16 )
    17 partition by list(owner)
    18 (
    19  partition p_sys values  ('SYS','SYSTEM') ,
    20  partition p_sys_other values('MDSYS','CTXSYS','DVSYS','WMSYS','LBACSYS') indexing off,
    21  partition p_USERS values ('LZF','EMCUSER')
    22 );
    23 
    24 ---
    25 INSERT INTO t_tab_columns
    26 select 
    27 owner, 
    28 table_name, 
    29 column_name, 
    30 data_type, 
    31 data_type_mod, 
    32 data_type_owner, 
    33 data_length, 
    34 data_precision, 
    35 data_scale, 
    36 nullable, 
    37 column_id, 
    38 default_length, 
    39 num_distinct,  
    40 collation
    41 from dba_tab_columns
    42 WHERE OWNER IN ('SYS','SYSTEM','MDSYS','CTXSYS','DVSYS','WMSYS','LBACSYS','LZF','EMCUSER');
    43 commit;
    44 ---2.1创建索引
    45 create index idx_Tab_columns_colname on t_tab_columns(column_name) local indexing partial 
    46 --验证
    47 select segment_name,partition_name from user_segments where segment_name='IDX_TAB_COLUMNS_COLNAME';
    48 SEGMENT_NAME    PARTITION_NAME
    49 IDX_TAB_COLUMNS_COLNAME    P_SYS
    50 IDX_TAB_COLUMNS_COLNAME    P_USERS
    51 --**** 证明:有起到作用
    52 --2.2 创建索引而不带 indexing partial
    53 create index idx_Tab_columns_dt on t_tab_columns(data_type) local ;
    54 --验证
    55 select segment_name,partition_name from user_segments where segment_name='IDX_TAB_COLUMNS_DT';
    56 SEGMENT_NAME    PARTITION_NAME
    57 IDX_TAB_COLUMNS_DT    P_SYS
    58 IDX_TAB_COLUMNS_DT    P_SYS_OTHER
    59 IDX_TAB_COLUMNS_DT    P_USERS
    60 --**** 证明:是否创建索引完全取决于  indexing partial 字句。
    61 --2.3 如果把本来可以索引的,调整为不可索引,会如何了?
    62 alter table t_tab_columns modify partition p_users indexing off;
    63 --验证
    64 select segment_name,partition_name from user_segments where segment_name='IDX_TAB_COLUMNS_COLNAME';
    65 SEGMENT_NAME              PARTITION_NAME
    66 IDX_TAB_COLUMNS_COLNAME    P_SYS
    67 
    68 select segment_name,partition_name from user_segments where segment_name='IDX_TAB_COLUMNS_DT';
    69 SEGMENT_NAME    PARTITION_NAME
    70 IDX_TAB_COLUMNS_DT    P_SYS
    71 IDX_TAB_COLUMNS_DT    P_SYS_OTHER
    72 IDX_TAB_COLUMNS_DT    P_USERS
    73 --**** 证明:只影响采用了 indexing partial的索引,会删除对应分区的索引。
    74 
    75 --2.4 如果把本来不可以索引的,调整为可索引,会如何了?
    76 alter table t_tab_columns modify partition p_users indexing on;
    77 --验证
    78 select segment_name,partition_name from user_segments where segment_name='IDX_TAB_COLUMNS_COLNAME';
    79 SEGMENT_NAME    PARTITION_NAME
    80 IDX_TAB_COLUMNS_COLNAME    P_SYS
    81 IDX_TAB_COLUMNS_COLNAME    P_USERS
    82 select segment_name,partition_name from user_segments where segment_name='IDX_TAB_COLUMNS_DT';
    83 结果略。
    84 --**** 证明:修改表分区的indexing属性,只影响采用了 indexing partial的索引,会重建对应分区的索引。

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

  • 相关阅读:
    成长——新的开始,一切都是美好的
    asp.net mvc项目自定义区域
    随便记录下系列
    插件化编程实现的一份糖炒栗子~~
    个人项目框架搭建 -- 缓存接口与实现
    个人项目框架搭建 -- 仓储模式使用
    个人项目框架搭建 -- Autofac简单使用记录
    至自己
    使用T4模板合并js文件
    HTML5学习笔记
  • 原文地址:https://www.cnblogs.com/lzfhope/p/7617018.html
Copyright © 2020-2023  润新知