精华内容
下载资源
问答
  • 分区索引 分区索引,有是全局分区索引与局部分区索引,加上一种全局非分区索引(也就是普通索引),加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。 1.全局非分区索引可以依赖普通的表,也可以依赖...

    分区索引 分区索引,有是全局分区索引与局部分区索引,加上一种全局非分区索引(也就是普通索引),加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。 1.全局非分区索引可以依赖普通的表,也可以依赖分区表建立。 CREATE INDEX month_ix ON s

    分区索引 分区索引,有是全局分区索引与局部分区索引,加上一种全局非分区索引(也就是普通索引),加起来共三种。下面我们讨论了这三种索引的组织结构以及应用场景。

    1.全局非分区索引可以依赖普通的表,也可以依赖分区表建立。 CREATE INDEX month_ix ON sales(sales_month); 等同于CREATE INDEX month_ix ON sales(sales_month) GLOBAL;

    2.全局分区索引 全局分区索引使用一种有别于底层表的分区机制,意思是索引的分区键可以选择跟表的分区键不一致,但索引的索引键前缀要包含索引的分区键。也就是只有”全局前缀索引“,而没有“全局非前缀索引”。这样,拿了索引分区键做前缀的索引,即使不包含表分区键,也能用于表的unique与primary约束。 建成后有多个段,每个段代表一个索引分区,每个索引分区中的键值可以指向任何表分区。可以依赖普通的表,也可以依赖分区表建立。可能索引分区数不等于表分区数。只能按range或hash(10g起)对索引分区。全局索引的range分区最后一个分区必须是maxvalue,以保证底层表的所有行都能放到这个索引中。 CREATE INDEX month_ix ON sales(sales_month,sales_date) GLOBAL PARTITION BY RANGE(sales_month) (PARTITION pm1_ix VALUES LESS THAN (2), PARTITION pm2_ix VALUES LESS THAN (3), PARTITION pm3_ix VALUES LESS THAN (4), PARTITION pm4_ix VALUES LESS THAN (5), PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));

    全局索引建立时global 子句允许指定索引的范围值,这个范围值是索引分区键的范围。全局分区索引的GLOBAL PARTITION BY RANGE(sales_month)的sales_month是指定索引分区键,可以跟表分区键不一样,我行我素地设立分区键,此时sales(sales_month,sales_date)句子,指定索引键,其前缀就必须包含索引分区键了。这一切,都可以跟底层表没啥关系。

    使用场景: 对于数据仓库,例如不断有旧数据的删除与新数据的流入(滑动窗口),全局索引很容易失效,使性能受影响。

    3.局部分区索引 不能对普通表建这个索引,只能依赖分区表建立,并且是依赖分区表的分区键来建立,即依赖底层表的分区机制来建立索引。随着表分区,建立一一对应的索引分区,每个索引分区中的条目都只指向一个表分区。 CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL; create index dinya_idx_t on dinya_test(item_id) local ( partition idx_1 tablespace tbs1, partition idx_2 tablespace tbs2, partition idx_3 tablespace tbs3 ); 局部分区索引逻辑上可以划分为: 局部前缀索引--表分区键在索引定义的第一列上。例如对表的字段LOAD_DATE进行range分区,而建索引时,LOAD_DATE又是索引的第一列。局部非前缀索引--索引不以表分区键作为它的索引字段的第一列,甚至压根不包含分区键。

    局部前缀索引与局部非前缀索引,对分区消除的影响? 首先我们得明白什么是分区消除。一个事务,可以只考虑特定的分区,其余分区就算物理介质损坏,其他分区所在表空间offline等,事务都可以不理会以及不扫描他们。分区消除的种类:表的分区消除,与索引的分区消除。分区消除更多的是为了可用性,以及在出现全表扫与全索引扫的时候,转换为只扫特定的分区以提高性能。 能否使用分区消除,关键在于谓词是否有分区键。如果谓词包含分区键,那可能是实现索引分区消除,也可能是表分区消除。如果谓词不包含分区键,那神马分区消除都是奢想。至于使用的是局部前缀索引还是局部非前缀索引,影响的只是能否实现索引分区消除。用局部前缀索引才能实现索引分区消除,用局部非前缀索引,不能实现“索引分区消除“(但表的分区消除仍然可能实现,但当cbo评估出来要先走索引,却发现索引分区不可用,如所在表空间offline了,此时已不能改路了)。cbo评估代价时,不会考虑分区索引是否可用,评估出一个路径,走下去发现此路不通,也不能走回头路了而直接报错了。

    局部前缀索引与局部非前缀索引,对于sql执行性能?如果将索引作为查询计划的第一步,效率上其实并没有什么区别,尽管前缀与非前缀索引会影响到是否能使用分区消除,但分区消除是什么呢?是可用性的提高,以及将全表扫描转为单分区全扫的性能上的优化。所以对于走索引作为第一步,是否分区消除不要紧,从而是否前缀也就不要紧了。

    局部前缀索引与局部非前缀索引的选择? 怎么选择,首先应该是能满足需求的。你如果建立一个(b,a)的索引,却总查where a=3,引出很多skip scan那就不好了,此时是应该换成建立(a,b)的索引。 如果仅仅有where a=1 and b=2这样的查询,你可能会问,我们是建(a,b)好还是(b,a)好呢,看哪个字段的选择性好,看我们有没有必要走a的索引分区消除,假如b的密度很大,从1-50000都有,而a只能是1与2,那么我们把b排前面更好。所以将哪个字段放前面,得满足业务需求、综合谓词的分区消除,与字段选择率来选择。

    局部索引与唯一约束 分区表字段想用unique或primary key约束,一般是使用全局索引来保证唯一性,这是一般的做法。因为局部索引只保证分区内部的键的唯一性,而不能跨分区,如果你的确想用局部索引来保证整个表的唯一性,就得把分区键加到约束当中,也成。如果oracle允许局部索引(不包含约束的情况)就能轻易来保证全表的唯一性,那么所有的update与insert,都得扫每一个分区,这样可用性与可扩展性都会丧失殆尽。

    4.总结 三种索引的选择? OLAP系统中多用局部索引,OLTP系统上,全局索引更为常见。可用性角度:局部索引更可用,就算一个索引分区出问题了也不影响其他,而全局索引很可能会成为一个故障点,一旦出现问题则整个索引都不可用。维护性角度:局部索引更好维护更灵活,DBA决定移动一个表分区,只需要重建与维护一个索引分区。对全局索引,很多情况下都需重建。 sql效率:因为局部索引随表分区,可以涉及出最优的执行计划。

    视图 select * from DBA_IND_PARTITIONS where index_name='LOCAL_NOPREFIXED'; select * from DBA_PART_INDEXES where index_name='LOCAL_NOPREFIXED'; select * from DBA_PART_KEY_COLUMNS where name='LOCAL_NOPREFIXED';

    实验: --创建表空间tbs1,tbs2,tbs3 create tablespace tbs1 datafile '+DATA6_MIDG'; create tablespace tbs2 datafile '+DATA6_MIDG'; create tablespace tbs3 datafile '+DATA6_MIDG'; SQL> --创建一个range分区表 create table t_part(a int,b int,data char(20)) partition by range (a) (partition p1 values less than(2) tablespace tbs1, partition p2 values less than(3) tablespace tbs2 ); --插入一些数据 insert into t_part select mod(rownum-1,2)+1,rownum,'x' from all_objects; commit; SQL> select * from t_part where rownum<=10; --创建局部前缀索引与局部非前缀索引 create index local_prefixed on t_part(a,b) local; create index local_noprefixed on t_part(b) local; create index local_prefixed on t_part(a,b) local (partition ind1 tablespace tbs3, partition ind2 tablespace tbs2);

    --为表收集统计信息 begin dbms_stats.gather_table_stats(user,'t_part',cascade=>TRUE);end; --把tbs2表空间下线,此时可以验证索引分区消除,将tbs1下线,可以验证表分区消除。 alter tablespace tbs3 offline; --再用这种命令来测试 select * from t_part where a=1 and b=1; select * from t_part where b=1; select /*+full(t_part)*/ * from t_part where a=1 and b=1;

    f68f2add0b68e4f9810432fce46917b7.png

    本文原创发布php中文网,转载请注明出处,感谢您的尊重!

    展开全文
  • --创建非分区表create tabletest_partition_01(number_1number,number_2number,string_1varchar2(10),string_2varchar2(20));-- 写入数据insert intotest_partition_01(number_1,number_2,string_1,string_2)select ...

    --创建非分区表

    create tabletest_partition_01(

    number_1number,

    number_2number,

    string_1varchar2(10),

    string_2varchar2(20)

    );

    -- 写入数据insert intotest_partition_01(number_1,

    number_2,

    string_1,

    string_2)select dbms_random.random() asnumber_1,round(dbms_random.value(0, 100000)) asnumber_2,

    dbms_random.string(opt=> 'A', len => 1) asString_1,

    dbms_random.string(opt=> 'p', len => 10) asString_2fromdual

    connectby rownum < 100000;commit;

    --试图创建本地分区索引 报错CREATE INDEX ix_test_partition_01_1 ONtest_partition_01(number_1)

    local (PARTITION p1,

    PARTITION p2,

    PARTITION p3);---ORA-14016

    ---创建普通索引

    CREATE INDEX ix_test_partition_01_1 ONtest_partition_01(number_1);

    --创建 全局分区索引CREATE INDEX ix_test_partition_01_2 ONtest_partition_01(number_2)

    GLOBAL PARTITIONBYRANGE (number_2)

    (PARTITION p1VALUES LESS THAN (10000),

    PARTITION p2VALUES LESS THAN (55000),

    PARTITION p3VALUESLESS THAN (MAXVALUE));

    结论:非分区表可以创建普通索引和全局分区索引不能创建本地分区索引。

    -------------------------------------------------------------------------------

    create tabletest_partiton_02(

    number_1number,

    number_2number,

    string_1varchar2(10),

    string_2varchar2(20)

    ) partitionbyrange(number_2)

    (

    partition p1values less than (10000),

    partition p2values less than (20000),

    partition p3values less than (50000),

    partition p4values less than (70000),

    partition p5valuesless than (maxvalue)

    );

    --试图创建本地分区索引CREATE INDEX ix_test_partiton_02_1 ONtest_partiton_02(number_1)

    local (PARTITION p1,

    PARTITION p2,

    PARTITION p3);--ora-14024 索引的分区数必须等于基础表的分区数

    --创建本地分区索引

    CREATE INDEX ix_test_partiton_02_1 ONtest_partiton_02(number_1)

    local (PARTITION p1,

    PARTITION p2,

    PARTITION p3,

    PARTITION p4,

    PARTITION p5

    );--drop index ix_test_partiton_02_1;

    CREATE INDEX ix_test_partiton_02_1 ON test_partiton_02(number_1) local; --和上面的创建方式等效

    --drop index ix_test_partiton_02_1;

    CREATE INDEX ix_test_partiton_02_1 ON test_partiton_02(number_1) ; --默认创建的是 非分区索引,分区索引才分 全局索引还是本地索引 ;

    ---创建全局分区索引CREATE INDEX ix_test_partiton_02_2 ONtest_partiton_02(number_2)

    GLOBAL PARTITIONBYRANGE (number_2)

    (PARTITION p1VALUES LESS THAN (10000),

    PARTITION p2VALUES LESS THAN (55000),

    PARTITION p3VALUESLESS THAN (MAXVALUE));

    ---写入测试数据insert intotest_partiton_02(number_1,

    number_2,

    string_1,

    string_2)select dbms_random.random() asnumber_1,round(dbms_random.value(0, 100000)) asnumber_2,

    dbms_random.string(opt=> 'A', len => 1) asString_1,

    dbms_random.string(opt=> 'p', len => 10) asString_2fromdual

    connectby rownum < 100001;commit;

    --分析表

    analyzetable test_partiton_02 compute statistics;--查看 普通索引是否可用

    select * from user_indexes t where t.INDEX_NAME in('IX_TEST_PARTITON_02_1','IX_TEST_PARTITON_02_2','IX_TEST_PARTITON_02_3');---查看分区索引是否 可用

    select * from user_ind_partitions t where t.INDEX_NAME in('IX_TEST_PARTITON_02_1','IX_TEST_PARTITON_02_2','IX_TEST_PARTITON_02_3');---改变分区,查看普通索引和分区索引是否可用

    --必须调整最后一个分区的大小,所以如果最后一个分区指定了最大值 必须先删除,再添加

    alter table test_partiton_02 add partition p6 values less than (90000);alter table test_partiton_02 droppartition p5;--- 结论 改变分区 普通索引和全局分区索引都会失效 只有本地分区索引好使

    PS:最好用以下语句查看索引的定义语句select dbms_metadata.get_ddl(object_type => 'INDEX',

    name=> 'IX_TEST_PARTITON_02_1')FROMDUAL;select dbms_metadata.get_ddl(object_type => 'INDEX',

    name=> 'IX_TEST_PARTITON_02_2')FROM DUAL;

    --- 发现了 plsql developer Version 11.0.5.1790 (64 bit) 的一个 bug 在用View 查看 DDL时 没有反应真实的情况

    展开全文
  • Oracle分区索引

    2021-01-12 05:35:34
    分区索引分为两类:Locally partitioned index(局部分区索引)Globally partitioned index(全局分区索引)下面就来详细解析一下这两类索引。一:Locally partitioned index(局部分区索引)1.概念:局部分区索引随表对...

    索引与表类似,也可以分区;

    分区索引分为两类:

    Locally partitioned index(局部分区索引)

    Globally partitioned index(全局分区索引)

    下面就来详细解析一下这两类索引。

    一:Locally partitioned index(局部分区索引)

    1. 概念:

    局部分区索引随表对索引完成相应的分区(即索引会使用与底层表相同的机制分区),每个表分区都有一个索引分区,并且只索引该表分区。

    如图,若一个表被划分为AB两个分区,则局部分区索引A就只索引A分区中的数据,局部分区索引B只索引B分区中的数据;

    2. 分类:

    局部分区索引又分为两类:

    Local prefixed index(局部前缀索引)

    Local nonprefixed index(局部非前缀索引)

    Ⅰ:局部前缀索引:以分区键作为索引定义的第一列

    Ⅱ:局部非前缀索引:分区键没有作为索引定义的第一列

    示例语句:

    create table local_index_example

    (

    id number(2),

    name varchar2(50),

    sex varchar2(10)

    )

    partition by range (id)

    (

    partition part_1 values less than (5),

    partition part_2 values less than (10)

    )

    --创建局部前缀索引;分区键(id)作为索引定义的第一列

    create index local_prefixed_index on local_index_example (id, name) local;

    --创建局部非前缀索引;分区键未作为索引定义的第一列

    create index local_nonprefixed_index on local_index_example (name, id) local;

    注意:判断局部索引是前缀还是非前缀的只需要看分区键是否作为索引定义的第一列

    3. 什么时候该使用前缀索引?什么时候该使用非前缀索引?

    对于该使用前缀还是非前缀索引,这完全取决于你的实际需求,你应该尽量从实际角度出发选择合适的索引方式以充分利用到其分区消除的特性。

    如果查询首先访问索引的话,它能否实现分区消除完全取决于查询中使用的谓词(即Where筛选条件);

    比如用上面的 local_index_example 表举例,现有两个查询:

    ①: select … from local_index_example where id = :id and name = :name;

    ②: select … from local_index_example where name = :name;

    对于以上两个查询来说,如果查询第一步是走索引的话,则:

    局部前缀索引 local_prefixed_index 只对 ① 有用;

    局部非前缀索引local_nonprefixed_index 则对 ① 和 ② 均有用;

    如果你有多个类似 ① 和 ② 的查询的话,则可以考虑建立局部非前缀索引;如果平常多使用查询 ① 的话,则可以考虑建立局部前缀索引;

    总之,重点是你要尽可能保证查询包含的谓词允许索引分区消除

    -------------------延伸阅读:绑定变量(bind variable)--------------------

    绑定变量是查询中的一个占位符,形如 :xxx 。

    例如,要获取 emp 表中 empno 为 123 的记录,你可以执行如下两种查询:

    ①: select * from emp where empno = 123;

    ②: 先将绑定变量 :empno 的值设置为 123,再执行查询 select * from emp where empno = :empno;

    第一种查询使用了 123 这样一个直接量(常量),如果有多个这样的查询的话,则每一个查询对数据库来说都是一个全新的查询,Oracle每次都会对查询进行解析、限定(命名解析)、安全性检查、优化等(简单地讲,就是每次执行时都要先编译);第二种查询使用了 :empno 这样一个绑定变量,变量值在查询时动态指定,这个查询只会在第一次时编译,随后Oracle会把查询计划存储在一个共享池中方便以后重用,如此当以后再传入不同的 empno 值进行查询时,Oracle会直接调用第一次解析好的这个执行计划进行执行,这样查询效率将大幅提升

    ------------------------------------------------------------------------

    4. 局部索引的唯一性

    Oracle只保证索引分区内部的唯一性,跨分区的唯一性无法保证。

    如果你想使用局部索引实现唯一性约束的话,则必须让分区键实现唯一性约束(UNIQUE 或 PRIMARY KEY)

    二:Globally partitioned index(全局分区索引)

    1. 概念:

    全局分区索引,顾名思义,就是针对整个表空间(全局)来说的。

    在此,索引按范围(Range)或散列(Hash,Oracle 10g中引入)进行分区,一个分区索引(全局)可能指向任何(或全部的)表分区。

    对于全局分区索引来说,索引的实际分区数可能不同于表的分区数量;

    全局索引的分区机制有别于底层表,例如表可以按 done_date 列划分为10个分区,表上的一个全局索引可以按 id 列划分为5个分区。

    与局部索引不同,全局索引只有一类,即全局前缀索引(prefixed global index),索引分区键必须作为索引定义的第一列,否则执行会报错。

    用例语句:

    --创建示例表,按id进行范围分区

    create table global_index_example

    (

    id number(2),

    name varchar2(50),

    age number(2)

    )

    partition by range (id)

    (

    partition part_1 values less than (5),

    partition part_2 values less than (10)

    )

    --创建按age进行范围分区的全局分区索引

    create index global_index on global_index_example(age) global

    partition by range (age)

    (

    partition index_part_1 values less than (20),

    partition index_part_2 values less than (maxvalue)

    )

    注意:

    全局索引要求最高分区(即最后一个分区)必须有一个值为 maxvalue 的最大上限值,这样可以确保底层表的所有行都能放在这个索引中;

    一般情况下,大多数分区操作(如删除一个旧分区)都会使全局索引无效,除非重建全局索引,否则无法使用

    2. 全局索引的使用:

    1) 数据仓库

    许多数据仓库系统都存在大量的数据出入,如典型的数据“滑入滑出”(即删除表中最旧的分区,并为新加载的数据增加一个新分区);

    这个过程涉及:

    去除老数据:最旧的分区要么被删除,要么与一个空表交换(将最旧的分区变为一个表),从而允许对旧数据进行归档;

    加载新数据并建立索引:将新数据加载到一个“工作”表中,建立索引并进行验证;

    关联新数据:一旦加载并处理了新数据,数据所在的表会与分区表中的一个空分区交换,将表中的这些新加载的数据变成分区表中的一个分区(分区表会变得更大)

    在 Oracle 9i 之前,对于创建的全局索引来说,这样增删分区的过程,意味着该全局索引的失效,你将不得不在最后花费相当长的时间重建全局索引;

    在 Oracle 9i 之后,你可以在分区操作期间使用 UPDATE GLOBAL INEXES子句来维护全局索引,这意味着当你在分区上执行删除、分解或其他操作时,Oracle会对原先建立的全局索引执行必要的修改,以保证它是最新的

    使用示例:

    --删除global_index_example表中的part_1分区,同时同步维护全局索引

    alter table global_index_example drop partition part_1 update global indexes;

    使用 UPDATE GLOBAL INEXES子句后,在删除一个分区时,必须删除可能指向该分区的所有全局索引条目;

    执行表与分区的交换时,必须删除指向原数据的所有全局索引条目,再插入指向刚加载的数据的新条目;

    如此一来 ALTER 命令执行的工作量会大幅增加;

    注意:使用 UPDATE GLOBAL INDEXES,将不能绕过 undo 或 redo 生成;

    小结:

    分区操作执行完成后重建全局索引方式占用的数据库资源更少,因此完成的相对“更快”,但是会带来显著的“停机时间”(重建索引时会有一个可观的不可用窗口);

    在分区操作执行的同时执行 UPDATE GLOBAL INEXES 子句方式会占用更多的资源,且可能需要花费更长的时间才能完成操作,但好处是不会带来任何的停机时间

    ----------------------------延伸阅读:redo(重做信息) 与 undo(撤销信息)------------------------------

    什么是redo?

    redo log file(重做日志文件),是数据库的事务日志。

    Oracle维护着两类重做日志文件:在线(online)重做日志文件和归档(archived)重做日志文件,这两类重做日志文件用于实例失败或是介质失败时的数据恢复;

    如果数据库所在主机突然断电导致实例失败,则Oracle会使用在线重做日志将系统恰好恢复到掉电之前的时间点;

    如果硬盘出现故障(即介质失败),Oracle会使用归档重做日志和在线重做日志将硬盘上的数据恢复到适当的时间点;

    另外如果你无意地删除了某些重要信息并提交了这个操作,那么可以恢复受影响数据的一个备份,并使用在线和归档重做日志文件把它恢复到之前的一个时间点;

    重做日志可能是数据库中最重要的恢复结构,但同时其他部分(如undo段、分布式事务恢复等)也不可或缺,重做日志是数据库区别于传统文件系统的一个主要特征;

    什么是undo?

    当你对数数据执行修改(增、删等)时,数据库会生成undo信息,万一你执行的事务或语句由于某些原因失败时,或者你用一条 rollback 语句请求回滚时,数据库就可以利用这些undo信息将数据返回到修改前的样子。

    redo用于在失败时恢复事务,undo则用于取消一条语句或一组语句的作用;

    undo信息存储在数据库内部一组特殊的段中(undo segment);

    注意:

    undo并不是使数据库物理地恢复到执行语句或事务之前的样子,数据库只是逻辑地恢复到原来的样子,所有修改都被逻辑地取消,但是数据结构以及数据库块在回滚后可能还与回滚前保持一致;

    因为在多用户系统中,可能会有数百或数千个并发事务,不仅仅你的事务在修改一些块,其他许多人的事务可能也在修改这些块;因此,不能简单地将一个块放回到你的事务开始前的样子,这样很可能会撤销掉其他人的事务工作。

    比如:

    假设你的事务执行了一个 insert 语句,这条语句导致分配了一个新区段;

    通过执行这个 insert,你将会获得一个新的数据库块,并在格式化该块后往其中放入一些数据;

    此时,可能出现另外某个事务,它也往这个块中插入数据;如果要回滚你的事务,显然不能取消对这个数据库块已有的格式化和空间分配,否则会影响到另外的那个事务的工作。

    因此在回滚时,Oracle实际上会做与先前逻辑上相反的工作,即:

    对于每个 insert,会执行一个 delete;

    对于每个 delete,会执行一个 insert;

    对于每个 update,会执行一个“反update”,或者是执行另一个 update 将修改前的行放回去;

    还有一点需要特别注意:undo生成对于直接路径操作(即使用append提示进行insert)不适用,直接路径操作能绕过表上的undo生成;

    如此,redo与undo共同协作以保证数据的完整与安全性

    --------------------------------------------------------------------------------------------------

    2) OLTP系统

    OLTP系统的特点是会频繁出现许多小的读写事务,一般在OLTP系统中,首要的是需要快速访问所需的行,其次数据的完整性、可用性也非常重要。

    在OLTP系统中,很多情况下全局索引很有意义,比如当表按一列分区后,你可能还需要通过其他列来快速访问数据,如此便可以考虑在这些列上建立全局索引。

    原文来自:https://www.cnblogs.com/Dreamer-1/p/6132776.html

    展开全文
  • 分区索引笔记(四)–分区索引什么时候会失效及处理分区索引笔记(三)–全局分区索引https://www.2cto.com/database/201308/238740.html分区表中 local 索引的维护会在oracle 操作表分区的时候自动进行,需要注意的是...

    分区索引笔记(四)–分区索引什么时候会失效及处理

    分区索引笔记(三)–全局分区索引

    https://www.2cto.com/database/201308/238740.html

    分区表中 local 索引的维护会在oracle 操作表分区的时候自动进行,需要注意的是global 索引,当global索引所在表执行alter table 涉及下列操作时,会导至该索引失效,需要重新建立:

    Ø ADD PARTITION | SUBPARTITION

    Ø COALESCE PARTITION | SUBPARTITION

    Ø DROP PARTITION | SUBPARTITION

    Ø EXCHANGE PARTITION | SUBPARTITION

    Ø MERGE PARTITION | SUBPARTITION

    Ø MOVE PARTITION | SUBPARTITION

    Ø SPLIT PARTITION | SUBPARTITION

    Ø TRUNCATE PARTITION | SUBPARTITION

    因此,建议用户在执行上述操作sql 语句后附加update indexes 子句,oracle即会自动维护全局索引,当然,需要注意这中间有一个平衡,你要平衡操作ddl 的时间和重建索引哪个时间更少,以决定是否需要附加updateindexes 子句。

    —————————————–

    总结:  —-不管是全局索引和本地索引,只要出现了数据移动,那么索引或分区索引都会失效

    1、执行alter table add partition 时未指定update indexes 子句:

    a. 如果是range/list分区,其local 索引和global 索引不会受影响;

    b. 如果是hash 分区,新加分区及有数据移动的分区的local 索引和global索引会被置为unuseable,需要重新编译。

    2、在执行drop partition时如果没有指定update indexes 子句:

    会导致glocal索引的失效,对于local索引,删除分区时对应的索引分区会被同时删除,且它分区的local索引不会受到影响。

    3、在执行split partition/subpartition 时,如果没有指定update indexes 子句:

    都会造成local 和global 索引的失效。不过如果你split partition/subpartition 的是个空分区,或者没有触发任何数据移动或变化,那么即使不加update indexes,也不会影响到索引。当然,保险起见,建议你还是执行完之后,查询一下数据字典,确认一下当前索引的状态。

    ———————————————————————-

    下面简单测试一下:

    1. 创建一个Range分区表:

    CREATE TABLE DFMS.TEST04

    PARTITION BY RANGE(OBJECT_ID)

    (

    PARTITION P1 VALUES LESS THAN (2000)

    TABLESPACE LOG_DATA,

    PARTITION P2 VALUES LESS THAN (8000)

    TABLESPACE LOG_DATA,

    PARTITION P3 VALUES LESS THAN (20000)

    TABLESPACE LOG_DATA,

    PARTITION P4 VALUES LESS THAN (40000)

    TABLESPACE LOG_DATA,

    PARTITION PMAX VALUES LESS THAN (MAXVALUE)

    TABLESPACE LOG_DATA

    )

    AS

    SELECT * FROM DBA_OBJECTS ;

    2. 建立一个PK, 同时生成global index:

    alter table DFMS.TEST04 add constraint pk_id primary key(object_id);

    建立一个local index :

    CREATE INDEX DFMS.IDX1_TEST04 ON DFMS.TEST04 (OBJECT_NAME)  LOCAL ;

    3. 我们通过dba_indexes视图查看global index的状态发现是valid :

    select index_name, status, last_analyzed,partitioned from dba_indexes where index_name=’PK_ID’ ;

    本地索引local index通过dba_indexes查看的状态是N/A, 需要通过dba_ind_partitions来查看,可以看到每个索引分区都是USABLE状态。而通过DBA_PART_INDEXES可以看到这个本地分区索引的整体状态。

    select * from dba_ind_partitions where index_name=’IDX1_TEST04′ ;

    select * from dba_indexes where index_name=’IDX1_TEST04′ ;

    select * from DBA_PART_INDEXES where index_name=’IDX1_TEST04′ ;

    4. 因为存在maxvalue,我们先测试split对全局及本地索引的影响 .

    4.1 新分区中都有数据的情况

    alter table test04 split partition pmax at (80000) into

    (partition p5 tablespace log_data ,

    partition pmax  tablespace log_data);

    我们从table的脚本可以看出pmax被分成p5和pmax两部分:

    ….

    PARTITION P5 VALUES LESS THAN (80000),

    PARTITION PMAX VALUES LESS THAN (MAXVALUE) ;

    …..

    显然由于select max(object_id) from TEST04 的行数是101769,split后旧分区中符合less than 80000的留在了第一个分区p5,其他的都存在了第二个分区(新的pmax分区)。

    我们查询global index及local index的状态:

    select index_name, status, last_analyzed,partitioned

    from dba_indexes where index_name=’PK_ID’ ;

    这里显然触发了数据的移动,global index索引状态变成UNUSABLE.

    select * from dba_ind_partitions where index_name=’IDX1_TEST04′ ;

    因为新split出来的分区(这里指p5)中有数据,原pmax中的数据被拆分到新分区p5及新的pmax中,发现p1,p2,p3,p4 对应的本地索引仍然是USABLE,而新的p5及新pmax对应的本地索引都是UNUSABLE.

    OK, 我们对global index及p5,pmax对应的本地分区索引进行rebuild :

    alter index PK_ID rebuild online;

    然后查询发现global index变成valid :

    select index_name, status, last_analyzed,partitioned

    from dba_indexes where index_name=’PK_ID’ ;

    alter index IDX1_TEST04 rebuild partition p5 online;

    alter index IDX1_TEST04 rebuild partition pmax online;

    执行之后查询:

    select * from dba_ind_partitions where index_name=’IDX1_TEST04′ ;

    可以看到两个索引分区p5及pmax状态都变成USABLE .

    4.2 新分区中有一个没有数据

    TEST04 的行数是101769,那么我们将p6新分区设置为110000,那么pmax分区显然就没有数据了。(新分区包含了所有PMAX分区的数据)

    alter table test04 split partition pmax at (110000) into      —-这里注意一下,新加一个分区,但数据算没有移动的

    (partition p6 tablespace log_data , partition pmax  tablespace log_data);

    查看global index及local index可以看到全局索引及每个本地索引分区都是USABLE, 这是因为没有触发数据移动。

    select index_name, status, last_analyzed,partitioned from dba_indexes where index_name=’PK_ID’ ;

    select * from dba_ind_partitions where index_name=’IDX1_TEST04′ ;

    备注:在split pmax分区时新的分区名称可以随便起(不一定含有pmax),比如上面的可以使用p6,p7, 只是p6会遵循less than 110000, 而第二个分区p7仍然是less than maxvalue.

    还有因为这里是表空间没有变化,如果非空的分区存储属性和原来的存储属性不一样,也会发生数据移动,也会导致索引失效。

    5. 测试drop partition对全局及本地索引的影响。

    对test04表的最后一个没有数据的pmax分区进行删除动作。

    alter table test04 drop partition pmax ;

    因为删除的分区没有数据,所以不涉及数据变化,所以对全局及本地所以都没有影响。

    假设我们要删除有数据的部分,既不保留分区也不保留数据,那么本地索引不会受到影响,global index会失效。

    alter table test04 drop partition p6 ;

    查询

    select * from dba_indexes where index_name=’PK_ID’ ;

    全局索引失效,状态变成UNUSABLE .

    select * from dba_ind_partitions where index_name=’IDX1_TEST04′ ;

    本地索引(其他分区)状态不变,为USABLE .

    6. 测试add partition 对全局索引和本地索引的影响。

    alter table test04 add partition p6  values less than (120000) ;

    查询状态:

    select * from dba_indexes where index_name=’PK_ID’ ;

    select * from dba_ind_partitions where index_name=’IDX1_TEST04′ ;

    发现Range分区,加入分区对于全局及本地索引都没有影响。

    同样测试list分区,也可以知道加入分区对于全局及本地索引都没有影响。这主要是因为没有触发数据的移动。

    6.1 对于Hash分区,由于add parittion会发生数据分布平衡的I/O操作,数据会发生移动,所以本地分区索引及全局索引都会置为UNUSABLE, 需rebuild.下面做简单测试:

    CREATE TABLE DFMS.TEST05

    PARTITION BY HASH (OBJECT_ID)

    PARTITIONS 8

    STORE IN (LOG_DATA)

    AS SELECT * FROM DBA_OBJECTS ;

    加入global及local index .

    alter table DFMS.TEST05 add constraint pk_test05_id primary key(object_id);

    CREATE INDEX DFMS.IDX1_TEST05 ON DFMS.TEST05 (OBJECT_NAME)  LOCAL ;

    加入新分区:

    alter table test05 add partition ;

    查询

    select * from dba_indexes where index_name=’PK_TEST05_ID’ ;

    select * from dba_ind_partitions where index_name=’IDX1_TEST05′ ;

    发现global index是UNUSABLE状态,本地分区索引中的第一个和最后一个分区的本地分区索引是UNUSABLE状态,其它是USABLE. 显然因为数据从第一个分区被拆分到了新的hasn分区,所以这两个分区中的数据发生了移动,导致了本地分区索引的失效,因为有数据行的移动,当然global index也变成了失效状态(UNUSABLE) .

    SQL> select index_owner,index_name,STATUS from dba_ind_partitions where index_name=’IDX1_TEST05′;

    INDEX_OWNER                    INDEX_NAME                     STATUS

    —————————— —————————— ——–

    SYS                            IDX1_TEST05                    UNUSABLE

    SYS                            IDX1_TEST05                    USABLE

    SYS                            IDX1_TEST05                    UNUSABLE

    7. 测试truncate partition 对全局索引和本地索引的影响。

    Truncate partition 就像truncate table 一样,直接从头部截断数据。在不指定update indexes 子句的情况下,truncate partition 也会造成分区所在表的global 索引失效。语法非常简单:

    alter table tbname truncate partition/subpartition ptname;

    alter table test04 truncate partition p6 ;

    查询

    select * from dba_indexes where index_name=’PK_ID’ ;

    select * from dba_ind_partitions where index_name=’IDX1_TEST04′ ;

    发现global index索引失效,本地分区索引状态都是USABLE .

    8. 其他操作如 Merge Partitions,Exchange Partitions及coalesce partitions等较少使用,这里不做测试。

    其实总之,如果发生数据移动,那么索引肯定是需要注意的。

    展开全文
  • ♣答案部分索引按照是否分区可以分为分区索引(Partitioned Indexes)和非分区索引(NonPartitioned Indexes),如下图所示:图3-12分区索引与分区表类似,分区索引被分解成更小、更易于管理的索引片断。分区索引提高了...
  • 本文基于oracle10gR2分区索引分为本地(local index)索引和全局索引(global index)。其中本地索引又可以分为有前缀(prefix)的索引和无前缀(nonprefix)的索引。而全局索引目前只支持有前缀的索引。B树索引和位图索引都...
  • 分区索引分为本地索引和全局索引,但对于在分区表上建索引,一般用的比较多的还是普通索引和本地分区索引,而全局分区索引相对用的比较少.以下测试为验证:分区表上的本地分区索引因为查询条件引起跨分区,是否改为普通...
  • Oracle索引和分区索引

    2021-03-05 17:52:23
    一.索引 1.索引的概念 ...1 B树索引:又称平衡树索引,按照平衡树算法来组织的索引的,适合索引值取值范围广泛、重复率低的应用。2 位图索引:按位图结构组织的索引,适合索引取值范围小、重复率高...
  • 对于分区表,可以建立不分区索引。也就是说表分区,但是索引不分区。以下着重介绍分区表的分区索引。索引与表一样,也可以分区。索引分为两类:locally partition index(局部分区索引)、globally partition index...
  • 二,到底如何判断建立怎样的分区索引(GLOBAL 还是LOCAL)我将用下面的例子来分析到底需要创建什么类型索引好。create table TT(id number,createdate date)partition by range(createdate)(partition Q1 VALUES LESS ...
  • mysql创建分区索引

    2021-03-03 19:59:06
    该楼层疑似违规已被系统折叠隐藏此楼查看此楼mysql创建分区索引(一)分区表:把所有的数据放在一个表中,但是物理存储数据会根据一定规则存放到不同的文件中(二)什么时候使用分区表?数据比较大时候,数以亿记或者数以TB...
  • 今天有人在QQ上问,如何将global的索引改成分区索引?由于在同一个列上不能建不同名的索引,不然会报错:SQL> create index ind_tab2_1 on tab2(a);Index created.Elapsed: 00:00:01.20SQL>SQL> create ...
  • 分区索引就是在所有每个区上单独创建索引,它能自动维护,在drop或truncate某个分区时不影响该索引的其他分区索引的使用,也就是索引不会失效,维护起来比较方便,但是在查询性能稍微有点影响。建立分区索引:create...
  • 分区表、分区索引和全局索引:在一个表的数据超过过2000万条或占用2G空间时,建议建立分区表。createtableta(c1int,c2varchar2(16),c3varchar2(64),c4intconstraintpk_taprimarykey(c1))partitionbyrange(c1)...
  • 为了提高数据库在大数据量读写操作和查询时的效率,达梦数据库提供了对表和索引进行分区的技术,把表和索引等数据库对象中的数据分割成小的单位,分别存放在一个个单独的段中,用户对表的访问转化为对较小段的访问,...
  • 2、 创建hash分区语法如下:[图:hash_partitioning.gif]语法看起来比range复杂,其实使用起来比range更简单,这里需要我们指定的有:column: 分区依赖列(支持多个,中间以逗号分隔);partition: 指定分区,有两种方式...
  • 创建list分区的语法如上,需要我们指定的:l column: 分区依赖列(注意:只能是一个);l partition:分区名称;l literal: 分区对应值(注意:每个分区可以对应多个值);l tablespace_clause: 分区的存储属性,例如所在表...
  • 分区索引状态

    2021-01-20 11:35:59
    N/A说明这个分区索引需要查user_ind_partitions或者user_ind_subpartitions(注意子分区的情况)来确定每个分区是否用;  VAILD   说明这个索引可用;  UNUSABLE说明这个索引不可用;  USABLE &...
  • 分区索引分为:全局分区 和 本地分区 创建全局分区表索引: SQL> create index idx123 on test123(object_id) global partition by range(object_id) ( partition idx_1 values less than(10000) tablespace ts01, ...
  • http://blog.csdn.net/waterxcfg304/article/details/8518577分区索引总结:一,分区索引分为2类:1、global,它必定是Prefix的。不存在non-prefix的2、local,它又分成2类:2.1、prefix:索引的第一个列等于表的...
  • 上一篇文章中我们了解了oracle普通表转化为分区表的方法的相关内容,接下来的这篇文章,我们将探讨oracle分区索引的失效和重建问题,提供了相关代码示例供大家参考,具体如下。--创建测试表SQL> create table t ...
  • 测试mysql扫描分区和分区索引 先做一些测试,指定分区字段,查看执行计划。 测试环境数据库版本为mysql5.7 创建分区表 CREATE TABLE employes ( id INT NOT NULL , fname VARCHAR (30), lname VARCHAR (30), ...
  • Oracle的本地分区索引

    2021-05-04 09:02:57
    类似文章很多,以下是本人的试验,主要说明一点:本地分区索引的分区可以和数据的分区不在一起,也可以在一起。drop table test_p2 cascade constraint;create table test_p2(Names varchar2(30),age int,home ...
  • oracle如何查看全局索引和分区索引

    千次阅读 2021-04-18 04:19:26
    -- DBA查看索引分区 select * from dba_ind_partitions where index_owner='DW' -- 查看索引分区类型 select * from dba_part_indexes where owner='DW' -- USER查看索引分区类型 select * from user_part_indexes;...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 172,070
精华内容 68,828
关键字:

分区索引