分割槽表本地索引與全域性索引的區別

Oracle資料庫中，有兩種型別的分割槽索引，全域性索引和本地索引，其中本地索引又可以分為本地字首索引和本地非字首索引。下面就分別看看每種型別的索引各自的特點。
全域性索引以整個表的資料為物件建立索引，索引分割槽中的索引條目既可能是基於相同的鍵值但是來自不同的分割槽，也可能是多個不同鍵值的組合。
全域性索引既允許索引分割槽的鍵值和表分割槽鍵值相同，也可以不相同。全域性索引和表之間沒有直接的聯絡，這一點和本地索引不同。
 create table orders (
     order_no      number,
     part_no       varchar2(40),
     ord_date      date
     )
     partition by range (ord_date)
      (partition Q1 values less than (TO_DATE('01-04-1999','DD-MM-YYYY')),
       partition Q2 values less than (TO_DATE('01-07-1999','DD-MM-YYYY')),
       partition Q3 values less than (TO_DATE('01-10-1999','DD-MM-YYYY')),
       partition Q4 values less than (TO_DATE('01-01-2000','DD-MM-YYYY'))
      )
     ;


      create index orders_global_1_idx on orders(ord_date)
      global partition by range (ord_date)
     (partition Q1 values less than (TO_DATE('01-04-1999','DD-MM-YYYY')),
       partition Q2 values less than (TO_DATE('01-07-1999','DD-MM-YYYY')),
       partition Q3 values less than (TO_DATE('01-10-1999','DD-MM-YYYY')),
       partition Q4 values less than (maxvalue)   -----global索引 必須顯示指定maxvalue
      )
     ;

  create index orders_global_2_idx
     on orders(part_no)
      global partition by range (part_no)
       (partition IND1 values less than (555555),
        partition IND2 values less than (MAXVALUE)    -----global索引 必須顯示指定maxvalue
       );

從上面的語句可以看出，全域性索引和表沒有直接的關聯，必須顯式的指定maxvalue值。假如表中新加了分割槽，不會在全域性索引中自動增加新的分割槽，必須手工新增相應的分割槽。
SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));
Table altered.
SQL> select TABLE_NAME, PARTITION_NAME from dba_tab_partitions where table_name='ORDERS';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS                         Q1
ORDERS                         Q2
ORDERS                         Q3
ORDERS                         Q4
ORDERS                         Q5       ---新增分割槽成功
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');
INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS_GLOBAL_1_IDX            GLOBAL1
ORDERS_GLOBAL_1_IDX            GLOBAL2
ORDERS_GLOBAL_1_IDX            GLOBAL3
ORDERS_GLOBAL_1_IDX            GLOBAL4      ---但是沒有自動新增分割槽索引



-----字首索引
使用全域性索引，索引鍵值必須和分割槽鍵值相同，這就是所謂的字首索引。Oracle不支援非字首的全域性分割槽索引，如果需要建立非字首分割槽索引，索引必須建成本地索引。
SQL> create index orders_global_2_idx
  2  on orders(part_no)                                                 -----global索引索引鍵值必須和分割槽鍵值相同
  3   global partition by range (order_no)
  4    (partition IND1 values less than (555555),
  5     partition IND2 values less than (MAXVALUE)
  6    )
  7  ;
 global partition by range (order_no)
                                    *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed




本地分割槽local
本地索引的分割槽和其對應的表分割槽數量相等，因此每個表分割槽都對應著相應的索引分割槽。使用本地索引，不需要指定分割槽範圍因為索引對於表而言是本地的，當本地索引建立時，Oracle會自動為表中的每個分割槽建立獨立的索引分割槽。
建立本地索引不必顯式的指定maxvalue值，因為為表新新增表分割槽時，會自動新增相應的索引分割槽。
create index orders_local_1_idx on orders(ord_date) local；

Index created.
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');
INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX             LOCAL1
ORDERS_LOCAL_1_IDX             LOCAL2
ORDERS_LOCAL_1_IDX             LOCAL3
ORDERS_LOCAL_1_IDX             LOCAL4
SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));
Table altered.
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');
INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX             LOCAL1
ORDERS_LOCAL_1_IDX             LOCAL2
ORDERS_LOCAL_1_IDX             LOCAL3
ORDERS_LOCAL_1_IDX             LOCAL4
ORDERS_LOCAL_1_IDX             Q5              -----新增分割槽後，會自動新增索引

這裡系統已經自動以和表分割槽相同的名字自動建立了一個索引分割槽。同理，刪除表分割槽時相對應的索引分割槽也自動被刪除。
本地索引和全域性索引還有一個顯著的差別，就是上面提到的，本地索引可以建立成本地非字首型，而全域性索引只能是字首型。
SQL> create index orders_local_2_idx
     on orders(part_no)     ----local索引建在哪個欄位上都可以
      local
       (partition LOCAL1,
        partition LOCAL2,
        partition LOCAL3,
        partition LOCAL4)
     ;
Index created.
SQL> select INDEX_NAME, PARTITION_NAME, HIGH_VALUE from dba_ind_partitions
     where index_name=upper('orders_local_2_idx');
INDEX_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ---------------------------------------------------------
ORDERS_LOCAL_2_IDX             LOCAL1                         TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
                                                              'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX             LOCAL2                         TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
                                                              'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX             LOCAL3                         TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                              'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX             LOCAL4                         TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
                                                              'NLS_CALENDAR=GREGORIA'
從上面的輸出可以看出，雖然索引的鍵值是part_no，但索引分割槽的鍵值仍然和表的分割槽鍵值相同，即ord_date，也即是所謂的非字首型索引。
最後，再引用一個例子說明字首索引和非字首索引的應用。
假設有一個使用DATE列分割槽的大表。我們經常使用一個VARCHAR2列(VCOL)進行查詢，但這個列並不是表的分割槽鍵值。
有兩種可能的方法來訪問VCOL列的資料，一是建立基於VCOL列的本地非字首索引，
               |                                         |
             -------                                   -------
            |       |         (10 more                |        |
Values:     A..     Z..   partitions here)            A..      Z.. 
另一種是建立基於VCOL列的全域性索引，
                |                                         |
              -------                                   -------
             |       |         (10 more                |        |
Values:      A..     D..   partitions here)            T..      Z.. 
可以看出，如果能夠保證VCOL列值的唯一性，全域性索引將會是最好的選擇。如果VCOL列值不唯一，就需要在本地非字首索引的並行查詢和全域性索引順序查詢以及高昂的維護代價之間做出選擇。--如果索引列非分割槽列，能夠保證索引列值的唯一性則建立全域性索引比較好