全局（Global) 与本地（Local)索引的区别

一、定义说明

 oracle数据库中，存在多种对象，最常见的表和索引，索引的目的是为了加快查询；oracle建议一个表>2g时，就建议进行分区，分区表的好处此处省略，分区表有维护操作，但是某些维护操作对整个表的索引维护造成巨大开销，因此本地索引就是解决分区表管理的问题。

 非分区索引创建方式，create index index_name on table_name(column_name);

                  查询索引状态从dba_indexes->status列查询

全局分区索引创建，create index index_name on table_name(column_name) global partition by range(column_name) (partition p1 values less than (11104) tablespace system,partition p2 values less than (11100) tablespace users,partition p3 values less than (maxvalue) tablespace sysaux.) or partition by hash (id) partitions 8 online等;

                   查询索引状态dba_ind_partitions->STATUS列查询

                  查询分区索引类型，是全局or本地 DBA_PART_INDEXES ->LOCALITY

本地分区索引创建，create index index_name on table_name(column_name) local;

                    查询索引状态dba_ind_partitions->STATUS列查询

二、create/selet 说明

1) 非分区索引
SQL> create index ind_1 on range_part_tab(deal_date);
Index created.
SQL> select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_1';

no rows selected

SQL> select index_name,STATUS,GLOBAL_STATS,TABLESPACE_NAME from dba_indexes where owner='SYS' and index_name='IND_1';

INDEX_NAME STATUS GLO TABLESPACE_NAME
------------------------------ -------- --- ------------------------------
IND_1 VALID YES SYSTEM

STATUS

表示一个非分区索引是否VALID或UNUSABLE

GLOBAL_STATUS

对于分区索引，指示是通过分析整个索引（YES）来收集统计信息，还是根据基础索引分区和子分区（NO）的统计信息进行估计

2）全局分区索引

create index ind_3 on range_part_tab(area_code) global partition by range(area_code) (partition p1 values less than (11104) tablespace system,partition p2 values less than (11100) tablespace users,partition p3 values less than (maxvalue) tablespace sysaux)
*
ERROR at line 1:
ORA-14037: partition bound of partition "P1" is too high 

12:39:59 SYS@ceshi>select area_code,count(*) from range_part_tab group by area_code;

AREA_CODE COUNT(*)
---------- ----------
599 11104
594 11074
596 11021
593 11113
595 10927
592 11214
598 11235
591 11169
597 11143

9 rows selected.  全局范围分区索引，第一个分区不能比所有的值都大

SYS@ceshi>create index ind_3 on range_part_tab(area_code) global partition by range(area_code) (partition p1 values less than (592) tablespace system,partition p2 values less than (11100) tablespace users,partition p3 values less than (maxvalue) tablespace sysaux);

Index created.

select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_3'

INDEX_NAME PARTITION_ HIGH_VALUE STATUS TABLESPACE_NAME
---------- ---------- ------------------------------ -------- ------------------------------
IND_3 P1 592 USABLE SYSTEM
IND_3 P2 11100 USABLE USERS
IND_3 P3 MAXVALUE USABLE SYSAUX

PARTITION_NAME 索引的分区名称

HIGH_VALUE    索引分区的范围条件

STATUS       分区索引的状态

SYS@ceshi>select index_name,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_3';

INDEX_NAME TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT LOCALI DEF_TABLESPACE_NAME
---------- ------------------------------ --------- --------- --------------- ------ ------------------------------
IND_3 RANGE_PART_TAB RANGE NONE 3 GLOBAL SYSTEM

hash全局分区索引，使用场景较多，例如索引块争用，且表为非分区表时

SYS@ceshi>create table a1 as select * from range_part_tab;

SYS@ceshi>create index ind_4 on a1(id) global partition by hash (id) partitions 8 online;

Index created.

SYS@ceshi>alter index ind_4 parallel 1;

Index altered.

select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_4'

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME
------------------------------ ------------------------------ ---------- -------- ------------------------------
IND_4 SYS_P61 USABLE SYSTEM
IND_4 SYS_P62 USABLE SYSTEM
IND_4 SYS_P63 USABLE SYSTEM
IND_4 SYS_P64 USABLE SYSTEM
IND_4 SYS_P65 USABLE SYSTEM
IND_4 SYS_P66 USABLE SYSTEM
IND_4 SYS_P67 USABLE SYSTEM
IND_4 SYS_P68 USABLE SYSTEM

8 rows selected.

SYS@ceshi>select index_name,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_4';

INDEX_NAME TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT LOCALI DEF_TABLESPACE_NAME
------------------------------ ------------------------------ --------- --------- --------------- ------ ------------------------------
IND_4 A1 HASH NONE 8 GLOBAL SYSTEM

 3）  本地分区索引  

SQL> create index ind_2 on range_part_tab(id) local;

Index created.

select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_owner='SYS' and index_name='IND_2'

INDEX_NAME PARTITION_ HIGH_VALUE STATUS TABLESPACE_NAME
------------------ -----------------------------------------------------------------------------------------------------------------------
IND_2 P1 TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P10 TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P11 TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P12 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P2 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P3 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P4 TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P5 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P6 TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P7 TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P8 TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P9 TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') USABLE SYSTEM
IND_2 P_MAX MAXVALUE USABLE SYSTEM

13 rows selected.

SYS@ceshi>select index_name,table_name,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,LOCALITY,DEF_TABLESPACE_NAME from DBA_PART_INDEXES where index_name='IND_2';

INDEX_NAME TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT LOCALI DEF_TABLESPACE_NAME
---------- ------------------------------ --------- --------- --------------- ------ ------------------------------
IND_2 RANGE_PART_TAB RANGE NONE 13 LOCAL

三、索引的重建

 非分区索引：

SQL> alter index ind_1 rebuild;

全局分区索引：

SQL> alter table range_part_tab move partition p1;

Table altered.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_tab_partitions
where TABLE_OWNER='SYS' and TABLE_NAME='RANGE_PART_TAB';

TABLE_NAME PARTITION_ TABLESPACE_NAME

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

RANGE_PART_TAB P1 SYSTEM

RANGE_PART_TAB P10 SYSTEM

RANGE_PART_TAB P11 SYSTEM

SQL> select index_name,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME

from dba_ind_partitions where index_owner='SYS' and index_name='IND_3';

INDEX_NAME PARTITION_ HIGH_VALUE           STATUS   TABLESPACE_NAME

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

IND_3      P1         592                  USABLE   SYSTEM

IND_3      P2         11100                USABLE   USERS

IND_3      P3         MAXVALUE             USABLE   SYSAUX

SQL> alter index ind_3 rebuild partition p1;

Index altered.

全局分区索引，单个分区的move并不会导致全局分区索引的失效

本地分区索引

select index_name,PARTITION_NAME,STATUS,TABLESPACE_NAME
 from dba_ind_partitions where index_owner='SYS' and index_name='IND_2';
INDEX_NAME PARTITION_ STATUS   TABLESPACE_NAME
---------- ---------- -------- ------------------------------
IND_2      P1         USABLE   SYSTEM
IND_2      P10        USABLE   SYSTEM
IND_2      P11        USABLE   SYSTEM
IND_2      P12        USABLE   SYSTEM

SQL> alter table range_part_tab move partition p1;

Table altered

SQL> alter index ind_2 rebuild partition p1;

Index altered.