oracle本地索引和全局索引整理及效率对比

---------------------------------------2014-05-18-------------------------------------------------------------------------------------------------------------------
时间总归是要打败你我
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
今天必须弄清楚本地索引、全局索引、前缀索引、非前缀索引这些名词间的关系
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
分区索引分为本地(local index)索引和全局索引(global index)。

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


一、本地索引特点：

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

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

创建分区表：以id作为分区条件
create table test (id number,data varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (1000) tablespace users,
partition p2 values less than (2000) tablespace users,
partition p3 values less than (maxvalue) tablespace users
);


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

select dbms_metadata.get_ddl('INDEX','I_ID','KING') index_name FROM DUAL;

这条语句查询出来的结果和我执行的语句是一样的，并没有看到转换后的分区语句。

创建无前缀的本地分区索引：
create index i_data on test(data) local;因为data不是分区键，所以这样就创建了一个无前缀的本地索引。（也就是说，一张分区表上面建的分区索引，只要不是以表分区条件作为引导列的索引
                                                                                                都是非前缀本地分区索引）

select dbms_metadata.get_ddl('INDEX','I_DATA','KING') index_name FROM DUAL;


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


  CREATE INDEX "KING"."I_DATA" ON "KING"."TEST" ("DATA")
  PCTFREE 10 INITRANS


理想状态是应该看到如下语句：
 CREATE INDEX "KING"."I_DATA" ON "KING"."TEST" ("DATA") LOCAL
 (PARTITION "P1"  TABLESPACE "USERS" ,PARTITION "P2"  TABLESPACE "USERS" , PARTITION "P3" TABLESPACE "USERS" )；

这种情况下方便我们对本地索引进行理解，比如上面我对本地分区索引的分区机制和表分区机制相同的不理解，从上面的语句就可以得到答复。
索引的分区就按照表的分区进行的，
表的分区条件是ID，相对应的ID值都落在了P1，P2，P3这三个分区里面。
当我们再创建DATA这个非前缀本地索引后，其实DATA的范围域就是根据ID的范围域来划分的。比如P1分区对应着ID小于1000的值，那么DATA在P1分区ID值就是对应着小于1000的值。

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

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

       index_name   table_name     partitioning_type     locality    ALIGNMENT 


2 I_ID        TEST            RANGE          LOCAL PREFIXED
3 I_DATA        TEST            RANGE          LOCAL NON_PREFIXED


二、全局索引特点：

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为例，讲解全局分区索引：


删除之前创建的本地索引：i_id

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 USERS,
  4    partition p2 values less than (maxvalue) tablespace USERS
  5  );


Index created


SQL> alter table test drop partition p3;

Table altered

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

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 users,
  4    partition p2 values less than (maxvalue) tablespace users
  5  );

ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed               -------------全局索引必须是前缀索引

SQL> create index i_id_global on test(id，data) global            ------------------说明，全局索引的索引列必须要以索引分区键做为开头，这样才是前缀索引
  2  partition by range(id)                                       -------------------之前一直混淆了索引列和索引分区键的区别。对于本地索引而言，分区键其实都是表的分区键
                                                                 --------------------而对于全局索引而言，索引分区键可以是任意字段，但是对应的索引列就必须以索引分区键作为引导列。
  3  ( partition p1 values less than (2000) tablespace users,
  4    partition p2 values less than (maxvalue) tablespace users
  5  );

Index created


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

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


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操作）可以得到每个表上有哪些非分区索引



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


至此，算是理清了全局索引和本地索引的区别，以及绕人的前缀索引和非前缀索引。当然还有一些细节问题，需要继续学习，但是现在我以及迫不及待的想下来打球了。。


然后抱着篮球走到楼下突然想起来，全局索引又分为分区全局索引和非分区全局索引，这个非分区全局索引和分区表上建的普通索引有啥区别呢？？

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

创建一张分区表：


create table test (id number,name varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (100000) tablespace users,
partition p2 values less than (200000) tablespace users,
partition p3 values less than (300000) tablespace users,
partition p4 values less than (400000) tablespace users,
partition p5 values less than (500000) tablespace users,
partition p6 values less than (600000) tablespace users,
partition p7 values less than (700000) tablespace users,
partition p8 values less than (800000) tablespace users,
partition p9 values less than (900000) tablespace users,
partition p10 values less than (maxvalue) tablespace users
);


插入数据：
begin 
   for i in 1..1000000 loop
     insert into test values(i,'killvoon');
     end loop;
    commit;
end;
/

---------------------------------------------------------------------------------
下面开始测试：各个索引情况下，select * from test where id <1000;的执行计划

1、创建全局索引分区索引：

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

Index created.

对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from test where id <1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3986709389


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |   999 | 11988 |     8   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE SINGLE             |             |   999 | 11988 |     8   (0)| 00:00:01 |  1 |        1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |   999 | 11988 |     8   (0)| 00:00:01 |  1 |        1 |
|*  3 |    INDEX RANGE SCAN                 | I_ID_GLOBAL |   100 |       |     5   (0)| 00:00:01 |  1 |        1 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<1000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        143  consistent gets                                   -----------多次执行之后，逻辑读143
          0  physical reads
          0  redo size
      17763  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed


这时候，我删掉该索引，重新创建全局分区索引，增加分区数量：


drop index i_id_global ;


 create index i_id_global on test(id) global
    partition by range(id)
   ( partition p1 values less than (200000) tablespace users,
     partition p2 values less than (400000) tablespace users,
     partition p3 values less than (600000) tablespace users,
     partition p4 values less than (800000) tablespace users,
     partition p5 values less than (maxvalue) tablespace users
    );

对表进行表分析：analyze table test compute statistics;
set autotrace traceonly;
select * from test where id <1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3986709389


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |   999 | 11988 |     7   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE SINGLE             |             |   999 | 11988 |     7   (0)| 00:00:01 |  1 |        1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |   999 | 11988 |     7   (0)| 00:00:01 |  1 |        1 |
|*  3 |    INDEX RANGE SCAN                 | I_ID_GLOBAL |   100 |       |     4   (0)| 00:00:01 |  1 |        1 |
-------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<1000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        142  consistent gets                                                       --------------对比上面的执行计划可以看出，逻辑读降低了一次，并且cost也降低了1个点
          0  physical reads                                                        --------------我不知道为什么会降低一次逻辑读，所以再添加几个分区试试。
          0  redo size
      17763  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed


这时候，我再次删掉该索引，重新创建全局分区索引，增加分区数量：

drop index i_id_global ;

 create index i_id_global on test(id) global                                    ---------------写脚本的过程中突然觉得，这样建出来的全局索引和本地索引就没区别了吧？
    partition by range(id)
   ( partition p1 values less than (100000) tablespace users,
     partition p2 values less than (200000) tablespace users,
     partition p3 values less than (300000) tablespace users,
     partition p4 values less than (400000) tablespace users,
     partition p5 values less than (500000) tablespace users,
     partition p6 values less than (600000) tablespace users,
     partition p7 values less than (700000) tablespace users,
     partition p8 values less than (800000) tablespace users,
     partition p9 values less than (900000) tablespace users,
     partition p10 values less than (maxvalue) tablespace users
    );


对表进行表分析：analyze table test compute statistics;
set autotrace traceonly;
select * from test where id <1000;


Execution Plan
----------------------------------------------------------
Plan hash value: 3986709389


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |   999 | 11988 |     7   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE SINGLE             |             |   999 | 11988 |     7   (0)| 00:00:01 |  1 |        1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |   999 | 11988 |     7   (0)| 00:00:01 |  1 |        1 |
|*  3 |    INDEX RANGE SCAN                 | I_ID_GLOBAL |   100 |       |     4   (0)| 00:00:01 |  1 |        1 |
-------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("ID"<1000)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        142  consistent gets                                                       ----------------这时候发现cost就没啥变化，并且逻辑读也是142
          0  physical reads
          0  redo size
      17763  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed


---------------------------------------------------------------------------
下面，要进入重要的对比阶段。


A、全局分区索引和全局非分区索引的对比：


删掉索引，创建一个非分区的全局索引：
drop index i_id_global ;
create index i_id_global on test(id) global;                                    


对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from test where id <1000;


Execution Plan
----------------------------------------------------------
Plan hash value: 3039421086

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |   999 | 11988 |    57   (0)| 00:00:01 |    |  |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |   999 | 11988 |    57   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                 | I_ID_GLOBAL |  9990 |       |    30   (0)| 00:00:01 |    |  |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<1000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        143  consistent gets                                                -----逻辑读停留在了143上面
          4  physical reads                                                 -----多出来4个物理读
          0  redo size
      26950  bytes sent via SQL*Net to client                               -----返回信息到客户端的值增大到了26950  
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed


小结：从上面的执行计划可以看出，对于没有分区的全局索引，它创建出来的是一整个索引，PARTITION RANGE SINGLE 这一项没有用到，如此情况下，CBO需要扫描一整个索引进行处理。

B、全局非分区索引和普通索引的对比：

drop index i_id_global ;
create index i_id_global on test(id) global;                                    

对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from test where id <1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3039421086

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |   999 | 11988 |    57   (0)| 00:00:01 |    |  |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |   999 | 11988 |    57   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                 | I_ID_GLOBAL |  9990 |       |    30   (0)| 00:00:01 |    |  |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<1000)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        143  consistent gets                                   
          4  physical reads
          0  redo size
      26950  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

通过各项指标可以看出，在分区表上创建的普通索引和全局非分区索引基本上是没有区别的，sql生成的执行计划也是一样。


C、全局分区索引和本地索引的对比：

由上面的实验可知，全局分区索引（索引分区条件和表分区条件一样，并且键值范围也是一样）的执行计划如下：
select * from test where id <1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3986709389

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |   999 | 11988 |     7   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE SINGLE             |             |   999 | 11988 |     7   (0)| 00:00:01 |  1 |        1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |   999 | 11988 |     7   (0)| 00:00:01 |  1 |        1 |
|*  3 |    INDEX RANGE SCAN                 | I_ID_GLOBAL |   100 |       |     4   (0)| 00:00:01 |  1 |        1 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<1000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        142  consistent gets                                                                 
          0  physical reads
          0  redo size
      17763  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

这时候我删掉索引，创建一个本地索引：

drop index i_id_global ;
create index i_id_global on test(id) local;                                    

对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from test where id <1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2603209139

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |   999 | 11988 |     7   (0)| 00:00:01 |    |  |
|   1 |  PARTITION RANGE SINGLE            |             |   999 | 11988 |     7   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |   999 | 11988 |     7   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | I_ID_GLOBAL |   999 |       |     4   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<1000)

Statistics
----------------------------------------------------------
          1  recursive calls                                          ----对比执行计划发现，多了一次递归调用，而继续往上查看，发行所有全局分区索引都没有递归调用
          0  db block gets                                            ----与之对应的，全局非分区索引，本地索引都存在一次递归调用
        142  consistent gets
          3  physical reads
          0  redo size
      17763  bytes sent via SQL*Net to client
       1142  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

对比两个执行计划发现，没有什么本质区别。

---------------------------------------------------------------------------
那么现在当我们查询某个具体值的时候，看看执行计划有和变化：


我当前索引是本地索引：
执行：
select * from test where id =1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2603209139

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |    12 |     2   (0)| 00:00:01 |    |  |
|   1 |  PARTITION RANGE SINGLE            |             |     1 |    12 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| TEST        |     1 |    12 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | I_ID_GLOBAL |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"=1000)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        475  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

删掉本地索引，创建一个全局非分区索引：

drop index i_id_global ;
create index i_id_global on test(id) global;                                    

对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from test where id =1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3039421086

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |    12 |     4   (0)| 00:00:01 |    |  |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |     1 |    12 |     4   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                 | I_ID_GLOBAL |    10 |       |     3   (0)| 00:00:01 |    |  |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1000)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets                                               ----逻辑读多了1次
          2  physical reads                                                ----物理读多了2次
          0  redo size
        479  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这样发现，分区索引的效果反而全局索引的效果好。

这时候，我将ID字段的全局索引建成unique索引：

drop index i_id_global ;
create unique index i_id_global on test(id) global;                                    


对表进行表分析：analyze table test compute statistics;


set autotrace traceonly;
select * from test where id =1000;


Execution Plan
----------------------------------------------------------
Plan hash value: 3914950652

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |     1 |    12 |     3   (0)| 00:00:01 |    |  |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TEST        |     1 |    12 |     3   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX UNIQUE SCAN                | I_ID_GLOBAL |    10 |       |     2   (0)| 00:00:01 |    |  |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1000)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size
        383  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

效果相对普通索引建成的全局分区索引效果渐好，但是依然不如本地索引。

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

那么究竟在什么样的情况下，全局索引的效率是高于本地索引的？？？

总结些网上资料：
差别：
本地索引适用于sql语句种限定一个范围的查询比如时间之类的， 全局索引适用于在全部记录中查询，比如要查询一个手机号之类的。
全局索引总可能出现unused的情况，需要重建

适用情况：
本地索引适合条件中包含partition key的，当然不是绝对的
全局索引总可能出现unused的情况，通常的处理方式是这样的：

刚才也想到了这一点，我所创建的分区表，并在以后的实验中都是用分区键来执行的，下面，重新创建分区表：

创建一张分区表：
create table king (id number,name varchar2(100),sid number)
partition by RANGE (id)
(
partition p1 values less than (100000) tablespace users,
partition p2 values less than (200000) tablespace users,
partition p3 values less than (300000) tablespace users,
partition p4 values less than (400000) tablespace users,
partition p5 values less than (500000) tablespace users,
partition p6 values less than (600000) tablespace users,
partition p7 values less than (700000) tablespace users,
partition p8 values less than (800000) tablespace users,
partition p9 values less than (900000) tablespace users,
partition p10 values less than (maxvalue) tablespace users
);


插入数据：
begin 
   for i in 1..1000000 loop
     insert into king values(i,'killvoon',i);
     end loop;
    commit;
end;
/

实验内容：创建 本地索引，全局分区索引，全局非分区索引，进行这三者的对比

1、创建本地索引：
drop index i_sid_global ;
create index i_sid_global on king(sid) local;                                    

对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from king where sid =1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3019414695

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |     1 |    78 |    12   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE ALL               |              |     1 |    78 |    12   (0)| 00:00:01 |  1 |    10 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| KING         |     1 |    78 |    12   (0)| 00:00:01 |  1 |    10 |
|*  3 |    INDEX RANGE SCAN                | I_SID_GLOBAL |     1 |       |    11   (0)| 00:00:01 |  1 |    10 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SID"=1000)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         92  recursive calls
          0  db block gets
        167  consistent gets
         10  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2、创建全局非分区索引：
drop index i_sid_global ;
create index i_sid_global on king(sid) global;                                    

对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from king where sid =1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 328286999

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |     1 |    78 |     4   (0)| 00:00:01 |    |          |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| KING         |     1 |    78 |     4   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | I_SID_GLOBAL |     1 |       |     3   (0)| 00:00:01 |    |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SID"=1000)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         40  recursive calls
          0  db block gets
        120  consistent gets
          2  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

在没有使用分区条件的情况下，可以看出，全局非分区索引的效果是优于本地索引的。

3、创建全局分区索引：
drop index i_sid_global ;
 create index i_sid_global on king(sid) global                                   
    partition by range(sid)
   ( partition p1 values less than (100000) tablespace users,
     partition p2 values less than (200000) tablespace users,
     partition p3 values less than (300000) tablespace users,
     partition p4 values less than (400000) tablespace users,
     partition p5 values less than (500000) tablespace users,
     partition p6 values less than (600000) tablespace users,
     partition p7 values less than (700000) tablespace users,
     partition p8 values less than (800000) tablespace users,
     partition p9 values less than (900000) tablespace users,
     partition p10 values less than (maxvalue) tablespace users
    );


对表进行表分析：analyze table test compute statistics;

set autotrace traceonly;
select * from king where sid =1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 135135479
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    78 |     2   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE SINGLE             |              |     1 |    78 |     2   (0)| 00:00:01 |  1 |        1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| KING         |     1 |    78 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                 | I_SID_GLOBAL |     1 |       |     1   (0)| 00:00:01 |  1 |        1 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SID"=1000)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         40  recursive calls
          0  db block gets
        127  consistent gets
          1  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

对比发现，当没有使用分区条件的情况下，全局分区索引的效率是高于全局非分区索引，本地索引的。

这时候，我看看select * from king where sid =1000 and id<1000;的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 3456533567

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    47 |  3666 |     2   (0)| 00:00:01 |    |          |
|   1 |  PARTITION RANGE SINGLE             |              |    47 |  3666 |     2   (0)| 00:00:01 |  1 |        1 |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| KING         |    47 |  3666 |     2   (0)| 00:00:01 |  1 |        1 | <== 在分区剪裁这地方有变化，说明用到了表分区
|*  3 |    INDEX RANGE SCAN                 | I_SID_GLOBAL |    47 |       |     1   (0)| 00:00:01 |  1 |        1 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"<1000)
   3 - access("SID"=1000)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
        124  consistent gets
          0  physical reads
          0  redo size
        385  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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


关于本地索引和全局索引，现在有了全新的认识，起码从概念层面已经不会混淆，并且对于二者的效率有了概况性的认识。
在以后的工作当中，注意运用二者的特点，提高sql执行效率。


经过上述相关实验对比，全局分区索引的优势在于确定值查询的情况，而本地索引，则在范围查询且包含分区条件的情况下，优势更明显。
这也就为啥常说全局索引通常用于主键查询，因为主键查询的时候通常都是给定明确值的，这种情况主键索引的效率更高。


---------------------------------------------------------------   end   -------------------------------------------------------------------------------------------------