如何解析分区索引中local index索引和global index

本篇文章给大家分享的是有关如何解析分区索引中local index索引和global index，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

分区索引分为本地(local index)索引和全局索引(global index)

其中本地索引又可以分为有前缀(prefix)的索引和无前缀(nonprefix)的索引。而全局索引目前只支持有前缀的索引。B树索引和位图索引都可以分区，但是HASH索引不可以被分区。位图索引必须是本地索引。下面就介绍本地索引以及全局索引各自的特点来说明区别；

一、本地索引特点：
分区索引就是在所有每个区上单独创建索引，它能自动维护，在drop或truncate某个分区时不影响该索引的其他分区索引的使用，也就是索引不会失效，维护起来比较方便，但是在查询性能稍微有点影响。

      create index idx_ta_c2 on ta(c2) local (partition p1,partition p2,partition p3,partition p4); 或者 create index idx_ta_c2 on ta(c2) local ;

1. 本地索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，本地索引的分区机制和表的分区机制一样。

2. 如果本地索引的索引列以分区键开头，则称为前缀局部索引。

3. 如果本地索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。

4. 前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

5. 本地索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用本地索引去给表做唯一性约束，则约束中必须要包括分区键列。

6. 本地分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，本地分区索引具有更高的可用性。

7. 位图索引只能为本地分区索引。

8. 本地索引多应用于数据仓库环境中。

本地索引:创建了一个分区表后，如果需要在表上面创建索引，并且索引的分区机制和表的分区机制一样，那么这样的索引就叫做本地分区索引。本地索引是由ORACLE自动管理的，它分为有前缀的本地索引和无前缀的本地索引。什么叫有前缀的本地索引？有前缀的本地索引就是包含了分区键，并且将其作为引导列的索引。什么叫无前缀的本地索引？无前缀的本地索引就是没有将分区键的前导列作为索引的前导列的索引。下面举例说明：

create table test (id number,data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (1000) tablespace p1,
partition p2 values less than (2000) tablespace p2,
partition p3 values less than (maxvalue) tablespace p3
);

create index i_id on test(id) local; 因为id是分区键，所以这样就创建了一个有前缀的本地索引。

SQL> select dbms_metadata.get_ddl('INDEX','I_ID','ROBINSON') index_name FROM DUAL;------去掉了一些无用信息

INDEX_NAME

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

CREATE INDEX "ROBINSON"."I_ID" ON "ROBINSON"."TEST" ("ID") LOCAL

(PARTITION "P1" TABLESPACE "P1" ,PARTITION "P2" TABLESPACE "P2" ,PARTITION "P3" TABLESPACE "P3" );

--也可以这样创建：

SQL> drop index i_id;

Index dropped

SQL> CREATE INDEX "ROBINSON"."I_ID" ON "ROBINSON"."TEST" ("ID") LOCAL
2 (PARTITION "P1" TABLESPACE "P1" , PARTITION "P2" TABLESPACE "P2" ,PARTITION "P3" TABLESPACE "P3" );

Index created

create index i_data on test(data) local;因为data不是分区键，所以这样就创建了一个无前缀的本地索引。

SQL> select dbms_metadata.get_ddl('INDEX','I_DATA','ROBINSON')index_name FROM DUAL;---删除了一些无用信息

INDEX_NAME
--------------------------------------------------------------------------------

CREATE INDEX "ROBINSON"."I_DATA" ON "ROBINSON"."TEST" ("DATA")LOCAL
(PARTITION "P1" TABLESPACE "P1" ,PARTITION "P2" TABLESPACE "P2" ,PARTITION "P3" TABLESPACE "P3" )；

--从user_part_indexes视图也可以证明刚才创建的索引，一个是有前缀的，一个是无前缀的

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes;

INDEX_NAME TABLE_NAME PARTITIONING_TYPE LOCALITY ALIGNMENT
------------------------------ ------------------------------ ----------------- -------- ------------
I_DATA TEST RANGE LOCALNON_PREFIXED
I_ID TEST RANGE LOCALPREFIXED

二、全局索引特点：
全局索引就是在全表上创建索引，它可以创建自己的分区，可以和分区表的分区不一样，也就是它是独立的索引。在drop或truncate某个分区时需要创建索引alter index idx_xx rebuild，也可以alter table table_name drop partition partition_name update global indexes;实现，但是要花很长时间在重建索引上。可以通过查询user_indexes、user_part_indexes和 user_ind_partitions视图来查看索引是否有效

create index idx_ta_c3 on ta(c3);

或者把全局索引分成多个区(注意和分区表的分区不一样)：

create index idx_ta_c4 on ta(c4) global partition by range(c4)(partition ip1 values less than(10000),partition ip2 values less than(20000),partition ip3 values less than(maxvalue));

　　注意索引上的引导列要和range后列一致，否则会有ORA-14038错误。

　　oracle会对主键自动创建全局索引

　　如果想在主键的列上创建分区索引，除非主键包括分区键，还有就是主键建在两个或以上列上。

　　在频繁删除表的分区且数据更新比较频繁时为了维护方便避免使用全局索引。

1.全局索引的分区键、分区数和表的分区键、分区数可能都不相同，表和全局索引的分区机制不一样,分为全局索引和全局分区索引，以下面示例为例（全局分区索引）。

2.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前导列。

3.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

4.全局索引多应用于oltp系统中。

5.全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。

6.oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

7.表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引。
全局索引：与本地分区索引不同的是，全局分区索引的分区机制与表的分区机制不一样。全局分区索引全局分区索引只能是B树索引，到目前为止(10gR2)，oracle只支持有前缀的全局索引。另外oracle不会自动的维护全局分区索引，当我们在对表的分区做修改之后，如果执行修改的语句不加上update global indexes的话，那么索引将不可用。

以上面创建的分区表test为例，讲解全局分区索引：

SQL> drop index i_id ;

Index dropped

SQL> create index i_id_global on test(id) global
2 partition by range(id)
3 ( partition p1 values less than (2000) tablespace p1,
4 partition p2 values less than (maxvalue) tablespace p2
5 );

Index created

SQL> alter table test drop partition p3;

Table altered

ORACLE默认不会自动维护全局分区索引，注意看status列，

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where index_name='I_ID_GLOBAL';

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
I_ID_GLOBAL P1 USABLE
I_ID_GLOBAL P2 USABLE

SQL> create index i_id_global on test(data) global
2 partition by range(id)
3 ( partition p1 values less than (2000) tablespace p1,
4 partition p2 values less than (maxvalue) tablespace p2
5 );

create index i_id_global on test(data) global
partition by range(id)
( partition p1 values less than (2000) tablespace p1,
partition p2 values less than (maxvalue) tablespace p2
)

ORA-14038: GLOBAL 分区索引必须加上前缀

SQL> create bitmap index i_id_global on test(id) global
2 partition by range(id)
3 ( partition p1 values less than (2000) tablespace p1,
4 partition p2 values less than (maxvalue) tablespace p2
5 );

create bitmap index i_id_global on test(id) global
partition by range(id)
( partition p1 values less than (2000) tablespace p1,
partition p2 values less than (maxvalue) tablespace p2
)

ORA-25113: GLOBAL 可能无法与位图索引一起使用

三、分区索引不能够将其作为整体重建，必须对每个分区重建

SQL> alter index i_id_global rebuild online nologging;

alter index i_id_global rebuild online nologging

ORA-14086: 不能将分区索引作为整体重建

--这个时候可以查询dba_ind_partitions，或者user_ind_partitions，找到partition_name，然后对每个分区重建

SQL> select index_name,partition_name from user_ind_partitions where index_name='I_ID_GLOBAL';

INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
I_ID_GLOBAL P1
I_ID_GLOBAL P2

SQL> alter index i_id_global rebuild partition p1 online nologging;

Index altered

SQL> alter index i_id_global rebuild partition p2 online nologging;

Index altered

四、关于分区索引的几个视图

dba_ind_partitions 描述了每个分区索引的分区情况，以及统计信息
dba_part_indexes 分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类型(local/global)
dba_indexes minus dba_part_indexes （minus操作）可以得到每个表上有哪些非分区索引

五、实验
SQL> create table T48_TRANSACTION_MODEL  
     (  
       trandate    DATE,  
       orgid       VARCHAR2(11),  
       stan        NUMBER,  
       subjectno   VARCHAR2(10),  
       subjectname VARCHAR2(50),  
       acctid      NUMBER  
     )  
     partition by range (TRANDATE)  
     (  
       partition XYZ_20100000 values less than (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110101 values less than (TO_DATE(' 2011-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110102 values less than (TO_DATE(' 2011-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110103 values less than (TO_DATE(' 2011-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110104 values less than (TO_DATE(' 2011-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110105 values less than (TO_DATE(' 2011-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110106 values less than (TO_DATE(' 2011-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),  
       partition XYZ_20110107 values less than (TO_DATE(' 2011-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))  
     );

Table created.


SQL> create index T48_TRANSACTION_MODEL_IDX1 on T48_TRANSACTION_MODEL(stan) local;     

Index created.

SQL> create index T48_TRANSACTION_MODEL_IDX2 on T48_TRANSACTION_MODEL(acctid)  ;    

Index created.

SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name in('T48_TRANSACTION_MODEL_IDX1','T48_TRANSACTION_MODEL_IDX2');

INDEX_OWNER                    INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20100000
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110101
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110102
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110103
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110104
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110105
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110106
SYS                            T48_TRANSACTION_MODEL_IDX1     XYZ_20110107

8 rows selected.

查询发现全局索引不在视图dba_ind_partitions中。

 
--查看是否是分区索引:  
 
SQL> select owner,index_name,index_type,table_owner,table_name,table_type,partitioned from dba_indexes where index_name in('T48_TRANSACTION_MODEL_IDX1','T48_TRANSACTION_MODEL_IDX2');

OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE  PAR
------------------------------ ------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- ---
SYS                            T48_TRANSACTION_MODEL_IDX2     NORMAL                      SYS                            T48_TRANSACTION_MODEL          TABLE       NO
SYS                            T48_TRANSACTION_MODEL_IDX1     NORMAL                      SYS                            T48_TRANSACTION_MODEL          TABLE       YES

查询得索引T48_TRANSACTION_MODEL_IDX2不是分区索引，而T48_TRANSACTION_MODEL_IDX1是分区索引。

SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where index_name in('T48_TRANSACTION_MODEL_IDX1','T48_TRANSACTION_MODEL_IDX2');

INDEX_NAME                     TABLE_NAME                     PARTITION LOCALI ALIGNMENT
------------------------------ ------------------------------ --------- ------ ------------
T48_TRANSACTION_MODEL_IDX1     T48_TRANSACTION_MODEL          RANGE     LOCAL  NON_PREFIXED

 
总结:  
全局索引：  
优点：通过索引检索，没有限定分区的谓词、或跨分区时，性能好点，  
缺点：分区维护的时候麻烦，drop分区等维护会失效，dml的时候索引维护成本高，数据大了rebuild也难  
 
local 索引：  
优点：通过索引检索，有限定分区的谓词、不跨分区时，性能好，分区维护容易，dml的索引维护底，rebuild也方便。  
缺点：通过索引检索，又没有限定分区的谓词、或跨分区时，性能不如全局索引  
 
有分区裁剪的,那么其他列就建立分区索引  
 
没有分区裁剪的,那么列就建立global 索引 

以上就是如何解析分区索引中local index索引和global index，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。