全域性分割槽索引和區域性分割槽索引

最近正在溫習ORACLE分割槽表，下面是關於全域性分割槽索引和區域性分割槽索引，以及字首區域性分割槽索引和非字首區域性分割槽索引的概念和需要注意的地方
全域性分割槽索引：索引分割槽不按照表的分割槽進行，索引的分割槽可以指向任何表分割槽
區域性分割槽索引：索引分割槽完成按照表的分割槽進行
字首區域性分割槽索引：分割槽鍵在索引定義的第一列上
非字首區域性分割槽索引：分割槽鍵不在索引定義的第一列上，可能根本不包含分割槽鍵
1、這裡先說明下分割槽修剪，以及字首區域性分割槽索引和非字首區域性分割槽索引的關係
何為分割槽修剪？其實這個是分割槽表的主要功能，表示可以透過分割槽，ORACLE會判斷查詢的資料在那個分割槽中
從而不會去訪問未包含資料的分割槽，從而提高效能。
字首區域性分割槽索引總是可以很好的利用分割槽修剪，而非字首區域性分割槽索引可能在利用上有一些問題。
因為根據分割槽鍵總能準確的判定出資料所在的分割槽，而非字首區域性分割槽索引因為不包含分割槽鍵所以不會很好的
利用，因為它判定不出分割槽中是否有需要的資料只能全分割槽掃描。
列子如下；
建立分割槽表
SQL> CREATE TABLE pp
  2        ( a int,
  3        b int,
  4        data char(20)
  5        )
  6        PARTITION BY RANGE (a)
  7        (
  8               PARTITION part_1 VALUES LESS THAN(2) tablespace tbs1,
  9               PARTITION part_2 VALUES LESS THAN(3) tablespace tbs2
 10   ) ;

表已建立。
字首區域性分割槽索引
SQL>   create index pp_local on pp(a,b) local;

索引已建立。
非字首區域性分割槽索引
SQL>   create index pp_nolocal on pp(b) local;

索引已建立。
插入資料
SQL> insert into pp
  2  select mod(rownum-1,2)+1, rownum, 'x'
  3  from all_objects;

已建立49580行。

分析表和索引
SQL> execute dbms_stats.gather_table_stats(ownname => 'PP',tabname => 'PP',cascade => true);

PL/SQL 過程已成功完成。
現在進行檢視執行計劃
SQL> select * from pp where a=1 and b=1;

         A          B DATA
---------- ---------- --------------------
         1          1 x

執行計劃
----------------------------------------------------------
Plan hash value: 1330722014

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

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

   3 - access("A"=1 AND "B"=1)
PSTART PSTOP都是1這裡忽略了分割槽2
如果我是用以下的查詢
SQL>  select * from pp  where b=1;

         A          B DATA
---------- ---------- --------------------
         1          1 x

執行計劃
----------------------------------------------------------
Plan hash value: 1993740852

----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |            |     1 |    28 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL               |            |     1 |    28 |     4   (0)| 00:00:01 |     1 |     2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PP         |     1 |    28 |     4   (0)| 00:00:01 |     1 | 
|*  3 |    INDEX RANGE SCAN                | PP_NOLOCAL |     1 |       |     3   (0)| 00:00:01 |     1 |     2 |
----------------------------------------------------------------------------------------------------

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

   3 - access("B"=1)
大家都知道其實返回的值都是一樣這裡卻掃描的全分割槽PSTART=1,PSTOP=2因為沒有分割槽鍵索引這裡
只能進行全分割槽掃描了。其實感覺這裡和索引的型別沒有多大的關係。關鍵在於查詢中是否有分割槽鍵。

另外一個限制區域性分割槽索引只會保證分居中的資料唯一性，而不會去關注全分割槽的資料唯一性，索引
即使對錶進行了分割槽，加入主鍵的時候主鍵對應的索引一樣不是分割槽的，列子
SQL>  CREATE TABLE pp2
  2       (
  3       id int,
  4       ic_id int,
  5        constraint partitioned_pk primary key(id)
  6        )
  7        PARTITION BY RANGE (id)
  8        (
  9        PARTITION part_1 VALUES LESS THAN (20000) tablespace tbs1,
 10        PARTITION part_2 VALUES LESS THAN (70000) tablespace tbs2
 11  ) ;
 
Table created
SQL>  insert into pp2
  2    select rownum,mod(rownum-1,2)+1
  3    from all_objects;
 
49587 rows inserted
 
SQL>
SQL> execute dbms_stats.gather_table_stats(ownname => 'PP100',tabname => 'PP2',cascade => true);
 
PL/SQL procedure successfully completed
 
SQL> select * from user_objects;
 
SQL>  CREATE TABLE pp2
  2       (
  3       id int,
  4       ic_id int,
  5        constraint partitioned_pk primary key(id)
  6        )
  7        PARTITION BY RANGE (id)
  8        (
  9        PARTITION part_1 VALUES LESS THAN (20000) tablespace tbs1,
 10        PARTITION part_2 VALUES LESS THAN (70000) tablespace tbs2
 11  ) ;
 
Table created
SQL>  insert into pp2
  2    select rownum,mod(rownum-1,2)+1
  3    from all_objects;
 
49587 rows inserted
 
SQL>
SQL> execute dbms_stats.gather_table_stats(ownname => 'PP100',tabname => 'PP2',cascade => true);
 
PL/SQL procedure successfully completed
 
SQL> select * from user_objects;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
PP2                                                                              PART_2                              58189          58189 TABLE PARTITION     2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID   N         N         N
PP2                                                                              PART_1                              58188          58188 TABLE PARTITION     2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID   N         N         N
PP2                                                                                                                  58187                TABLE               2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID   N         N         N
PARTITIONED_PK                                                                                                       58190          58190 INDEX               2010-9-3 14 2010-9-3 14:5 2010-09-03:14:56:19 VALID   N         N         N
ORACLE這樣做是為了提高效能，如果每次插入資料的時候ORACLE都必須去檢查每個分割槽，這樣導致的結果是效能地下，分割槽越多效能就越差。
2、全域性分割槽索引
這個分割槽索引只有字首這一種方式，全域性分割槽索引可以用於保證主鍵的惟一性，這樣一來，即使不包括表的分割槽鍵，也可以有能保證惟一性的分割槽索引