Oracle数据库中的本地索引和全局索引的区别

表可以按range,hash,list分区,表分区后,其上的索引和普通表上的索引有所不同,Oracle对于分区表上的索引分为2类,即局部索引和全局索引,下面分别对这2种索引的特点和局限性做个总结。
 局部索引local index

1. 局部索引一定是分区索引,分区键等同于表的分区键,分区数等同于表的分区说,一句话,局部索引的分区机制和表的分区机制一样。
2. 如果局部索引的索引列以分区键开头,则称为前缀局部索引。
3. 如果局部索引的列不是以分区键开头,或者不包含分区键列,则称为非前缀索引。
4. 前缀和非前缀索引都可以支持索引分区消除,前提是查询的条件中包含索引分区键。
5. 局部索引只支持分区内的唯一性,无法支持表上的唯一性,因此如果要用局部索引去给表做唯一性约束,则约束中必须要包括分区键列。
6. 局部分区索引是对单个分区的,每个分区索引只指向一个表分区,全局索引则不然,一个分区索引能指向n个表分区,同时,一个表分区,也可能指向n个索引分区, 对分区表中的某个分区做truncate或者move,shrink等,可能会影响到n个全局索引分区,正因为这点,局部分区索引具有更高的可用性。
7. 位图索引只能为局部分区索引。
8. 局部索引多应用于数据仓库环境中。

全局索引global index

1. 全局索引的分区键和分区数和表的分区键和分区数可能都不相同,表和全局索引的分区机制不一样。
2. 全局索引可以分区,也可以是不分区索引,全局索引必须是前缀索引,即全局索引的索引列必须是以索引分区键作为其前几列。
3. 全局分区索引的索引条目可能指向若干个分区,因此,对于全局分区索引,即使只动,截断一个分区中的数据,都需要rebulid若干个分区甚至是整个索引。
4. 全局索引多应用于oltp系统中。
5. 全局分区索引只按范围或者散列hash分区,hash分区是10g以后才支持。
6. oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引,用消耗一定资源来换取高度的可用性。
7. 表用a列作分区,索引用b做局部分区索引,若where条件中用b来查询,那么oracle会扫描所有的表和索引的分区,成本会比分区更高,此时可以考虑用b做全局分区索引

 
分区索引字典 
DBA_PART_INDEXES 分区索引的概要统计信息,可以得知每个表上有哪些分区索引,分区索引的类新(local/global,)
Dba_ind_partitions每个分区索引的分区级统计信息
Dba_indexesminusdba_part_indexes,可以得到每个表上有哪些非分区索引
 
索引重建 
Alter index idx_name rebuild partition index_partition_name [online nologging]
需要对每个分区索引做rebuild,重建的时候可以选择online(不会锁定表),或者nologging建立索引的时候不生成日志,加快速度。
Alter index rebuild idx_name [online nologging]
对非分区索引,只能整个index重建

今天创建了一个分区表content,随后在分区列上创建了本地索引全局索引,但是在执行语句“ select id,book_num from content where book_num=50 ;”时发现,若创建的为本地索引执行计划走“ INDEX RANGE SCAN”,若创建全区索引,则默认走全表扫描,使用hint方式可以走“ INDEX RANGE SCAN”,而且资源消耗的确比全表扫描小。 不明白为什么:有全区索引时为什么还总是默认走全表扫面?是oracle的bug,还是优化器有特殊考虑?除了加hint和SQL Profile方式外有什么方式能让优化器直接就走索引数据库版本 11.2.0.4 双节点RAC,操作系统redhat 6.4 。 以下为实验步骤: 1. 创建分区表content: CREATE TABLE "SCOTT"."CONTENT_PAR" ( "ID" VARCHAR2(60) NOT NULL ENABLE, "NAME" VARCHAR2(512) NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(4000), "PROVIDER" VARCHAR2(60), "PROVIDER_TYPE" VARCHAR2(60), "AUTHODID" VARCHAR2(60), "TYPE" VARCHAR2(5), "KEYWORDS" VARCHAR2(4000), "EXPIRETIME" VARCHAR2(14), "FEE" NUMBER(10,0), "LOCATION" VARCHAR2(100), "FIRST" VARCHAR2(5), "URL1" VARCHAR2(512), "URL2" VARCHAR2(512), "URL3" VARCHAR2(512), "URL4" VARCHAR2(512), "INFO_CONTENT" VARCHAR2(4000), "INFO_PIC" VARCHAR2(512), "INFO_SOURCE" VARCHAR2(4000), "FEE_CODE" VARCHAR2(60), "DETAIL_URL1" VARCHAR2(512), "DETAIL_URL2" VARCHAR2(512), "DETAIL_URL3" VARCHAR2(512), "BOOK_NUM" NUMBER(10,0), "CLASSIFY" VARCHAR2(100), "AUTHODS" VARCHAR2(1024), "ACTOR" VARCHAR2(1024), "OTHERS_ACTOR" VARCHAR2(4000), "BOOK_TYPE" VARCHAR2(50), "BOOK_STYLE" VARCHAR2(50), "BOOK_COLOR" VARCHAR2(50), "AREA" VARCHAR2(50), "LANGUAGE" VARCHAR2(50), "YEAR" VARCHAR2(14), "STATUS" VARCHAR2(4), "CHAPTER_TYPE" VARCHAR2(4), "PORTAL" VARCHAR2(1), "BUSINESSID" VARCHAR2(64), "DOWNLOAD_NUM" NUMBER(12,0), "AVERAGEMARK" NUMBER(12,0), "FAVORITES_NUM" NUMBER(12,0), "BOOKED_NUM" NUMBER(12,0), "CREATETIME" VARCHAR2(14), "FLOW_TIME" DATE, "USER_TYPE" VARCHAR2(50), "LUPDATE" VARCHAR2(14), "COMIC_IMAGE" VARCHAR2(512), "ADAPTERDESK" VARCHAR2(50), "SYNC_STATUS" NUMBER(1,0), "BASETYPE" VARCHAR2(5), "EBOOKURL" VARCHAR2(4000), "WEEKNUM" NUMBER(12,0), "MONTHNUM" NUMBER(12,0), "WEEKFLOWERSNUM" NUMBER(12,0), "MONTHFLOWERSNUM" NUMBER(12,0) ) partition by range (BOOK_NUM) ( partition BOOKNUM_50 values less than ( 50 ) tablespace test ,partition BOOKNUM_100 values less than (100) tablespace ts01 ,partition BOOKNUM_200 values less than (200) tablespace local ,partition BOOKNUM_670 values less than (670) tablespace user); 2. 创建本地索引ind_booknum_local create index ind_booknum_local on content(book_num) local; 查看select id,book_num from content where book_num=50 ;执行计划走的是“ INDEX RANGE SCAN” 3. 删除本地索引ind_booknum_local ,创建全局索引ind_booknum_gloabal。 create index ind_booknum_gloabal oncontent(book_num) global partition by range (BOOK_NUM) (partition ind_50 values less than ( 50) tablespace test ,partition ind_100 values less than (100) tablespace test ,partition ind_200 values less than (200) tablespace local ,partition ind_300 values less than (300) tablespace test ,partition ind_670 values less than ( maxvalue) tablespace test ); alter system flush shared_pool;(仅限在实验环境操作) alter system flush buffer_cache;(仅限在实验环境操作) 收集表的统计信息:exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname=> 'CONTENT',estimate_percent => 100,cascade => true,method_opt =>'for all columns size 1'); 查看select id,book_num from content where book_num=50 ; 执行计划走的是 “TABLE ACCESS FULL” 执行计划走的是“ INDEX RANGE SCAN” 使用加hint 方式: alter system flush shared_pool;(仅限在实验环境操作) alter system flush buffer_cache;(仅限在实验环境操作) select /*+ index(content IND_BOOKNUM_GLOABAL)*/ id,book_num from content where book_num=50 ; 4. 创建非分区的全局索引:create index ind_booknum_gloabal_nopar on content(book_num) global; alter system flush shared_pool;(仅限在实验环境操作) alter system flush buffer_cache;(仅限在实验环境操作) 收集表的统计信息:exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname=> 'CONTENT',estimate_percent => 100,cascade => true,method_opt =>'for all columns size 1'); 查看select id,book_num from content where book_num=50 ; 执行计划走的是 “TABLE ACCESS FULL” 使用加hint方式: alter system flush shared_pool;(仅限在实验环境操作) alter system flush buffer_cache;(仅限在实验环境操作) select /*+ index(content IND_BOOKNUM_GLOABAL_NOPAR)*/ id,book_num from content where book_num=50 ; 5. 创建复合索引:create index ind_num_id_gloabal_nopar on content(book_num,id) global; alter system flush shared_pool;(仅限在实验环境操作) alter system flush buffer_cache;(仅限在实验环境操作) 收集表的统计信息:exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname=> 'CONTENT',estimate_percent => 100,cascade => true,method_opt =>'for all columns size 1'); 查看select id,book_num from content where book_num=50 ; 执行计划走的是 “INDEX RANGESCAN” alter system flush shared_pool;(仅限在实验环境操作) alter system flush buffer_cache;(仅限在实验环境操作) 查看 select name,book_num from content where book_num=50 ; 执行计划走的是 “TABLE ACCESS FULL” 使用加hint方式: alter system flush shared_pool;(仅限在实验环境操作) alter system flush buffer_cache;(仅限在实验环境操作) select /*+ index(content IND_NUM_ID_GLOABAL_NOPAR)*/ name,book_num from content where book_num=50 ; 执行计划走 “INDEX RANGESCAN”
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页