Oracle Partition Tips

原文链接：Oracle Partition Tips

1997年在Oracle 8.0中引入了分区表的概念，经过几年的发展，分区表的类型、功能、性能也都在完善。在10gR2，一个表允许有超过1M个分区。在11g中又引入了partition advisor用来帮助用户选择适合自己的分区方法，分区类型也有了更多的扩展，包括Interval Partition、Reference Partitions等。截止Oracle 12c，目前的分区表情况如下。

Oracle partition发展历史

oracle8
    Range
oracle 8i
    Hash
    Range-Hash
oracle 9i
    List
oracle 9iR2
    Range-List
oracle 11gR1
    Range-Range
    List-Range
    List-Hash
    List-List
oracle 11gR2
    Hash-Range
    Hash-List
    Hash-Hash
    Interval
    Reference
    Virtual Column based
    System partitioning(这个是由应用程序控制的分区，使用时必须指定分区名字)

Oracle分区索引类型
Local Index
A.Local Prefixed Index
可以理解为分区索引的第一个索引字段是分区表的Partition key
B.Local Non-Prefixed Index
可以理解为分区索引的第一个索引字段不是分区表的Partition key
Global Prefixed Index
A.range类型分区
B.hash类型分区
注意：这两种Global索引分区类型与基表的分区类型没有关系。我们可以在非分区表上创建该索引
Global Non-Prefixed Index(目前Oracle还不支持)
创建该索引时会提示ORA-14038: GLOBAL partitioned index must be prefixed

注意：Local Index索引分区和基表分区是一一对应的
Global Index索引分区和基表分区是相互独立，不存在索引分区和表分区之间的一一对应关系
比如基表有5个分区，索引有2个分区

如何确定分区索引是Global/Local,PREFIXED/NON-PREFIXED

SQL> select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA';
INDEX_NAME               TABLE_NAME   LOCALITY   ALIGNMENT
------------------------ ------------ ---------- --------------
IDX_GLOBAL_PREFIXED      OHS_PART     GLOBAL     PREFIXED
IDX_LOCAL_NON_PREFIXED   OHS_PART     LOCAL      NON_PREFIXED

如何选取分区索引类型

When deciding what kind of partitioned index to use, you should consider the following guidelines in this order:

If the table partitioning column is a subset of the index keys, then use a local index. If this is the case, then you are finished. If this is not the case, then continue to guideline 2.
If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
If your priority is manageability, then consider a local index. If this is the case, then you are finished. If this is not the case, continue to guideline 4.
If the application is an OLTP type and users need quick response times, then use a global index. If the application is a DSS type and users are more interested in throughput, then use a local index.

For more information about partitioned indexes and how to decide which type to use, refer to Using Partitioning in a Data Warehouse Environment and Using Partitioning in an Online Transaction Processing Environment.

以下的维护操作，易导致索引分区UNUSABLE
1. IMPORT PARTITION or conventional path SQL*Loader.
2. Direct-path SQL*Loader没有成功完成(local index partitions and global indexes)
3. 维护操作类似ALTER TABLE MOVE PARTITION.
4. 维护操作类似ALTER TABLE TRUNCATE PARTITION.
5. 维护操作类似ALTER TABLE SPLIT PARTITION.
6. 维护操作类似ALTER INDEX SPLIT PARTITION.
7. 对Hash分区类型的表增加分区(分区中数据会变化)

如何避免索引UNUSABLE
为了防止分区维护的操作造成Index不可用，我们可以使用带’update global indexes’的语句，以下的操作支持UPDATE GLOBAL INDEXES:
1. ADD PARTITION|SUBPARTITION (hash only)
2. COALESCE PARTITION|SUBPARTITION
3. DROP PARTITION
4. EXCHANGE PARTITION|SUBPARTITIO
5. MERGE PARTITION
6. MOVE PARTITION|SUBPARTITION
7. SPLIT PARTITION
8. TRUNCATE PARTITION|SUBPARTITION

Update Global Indexes和Update Indexes的区别
Update Global Indexes只维护全局索引，Update Indexes会同时维护全局和本地索引。Update Global Indexes可以理解为是Update Indexes的子集。假定当前有一个表，在其上面创建了local和global partitioned index，我们把其中一个非空的分区做spilt/merge，如果只使用Update Global Indexes，那么Local Index会被标记成UNUSABLE。如果使用Update Indexes，则两者都有效。Oracle 9.2中可以使用’update global indexes’，10g之后可以使用’update global indexes’/’update indexes’

测试可用脚本

create table ohs_part
(id number,
 pdate date)
partition by range(pdate)
(partition ohs_201701 values less than (to_date('2017-02-01','yyyy-mm-dd')),
 partition ohs_201702 values less than (to_date('2017-03-01','yyyy-mm-dd')),
 partition ohs_201703 values less than (to_date('2017-04-01','yyyy-mm-dd')),
 partition ohs_201704 values less than (to_date('2017-05-01','yyyy-mm-dd')),
 partition ohs_max values less than (maxvalue))
/
insert into ohs_part select 1, sysdate from dual;
insert into ohs_part select 2, sysdate from dual;
insert into ohs_part select 3, sysdate - 15 from dual;
insert into ohs_part select 4, sysdate - 15 from dual;
insert into ohs_part select 5, sysdate + 30 from dual;
insert into ohs_part select 6, sysdate + 30 from dual;
insert into ohs_part select 7, sysdate + 60 from dual;
insert into ohs_part select 8, sysdate + 60 from dual;
commit;
create index idx_local on ohs_part(pdate) local;
create index idx_normal on ohs_part(id);
create index idx_global on ohs_part(pdate,id) global;

SQL> create index idx_local on ohs_part(pdate) local; Index created.
SQL> create index idx_normal on ohs_part(id);
Index created.
SQL>
SQL> create index idx_global on ohs_part(pdate,id) global;
Index created.
SQL>
SQL> col index_name for a30
SQL> select index_name,partitioned,status from user_indexes where table_name='OHS_PART';
INDEX_NAME                     PARTITION STATUS
------------------------------ --------- ------------------------
IDX_GLOBAL                     NO        VALID
IDX_NORMAL                     NO        VALID
IDX_LOCAL                      YES       N/A
SQL> select index_name,status from user_ind_partitions where index_name='IDX_LOCAL';
INDEX_NAME                     STATUS
------------------------------ ------------------------
IDX_LOCAL                      USABLE
IDX_LOCAL                      USABLE
IDX_LOCAL                      USABLE
IDX_LOCAL                      USABLE
IDX_LOCAL                      USABLE
SQL>
SQL> col table_name for a20
SQL> select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA';

INDEX_NAME           TABLE_NAME           LOCALITY           ALIGNMENT
-------------------- -------------------- ------------------ --------------------
IDX_LOCAL            OHS_PART             LOCAL              PREFIXED
SQL>
SQL> create index idx_local_non_prefixed on ohs_part(id,pdate) local;
Index created.
SQL>
SQL> select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA';
INDEX_NAME                     TABLE_NAME           LOCALITY           ALIGNMENT
------------------------------ -------------------- ------------------ --------------------
IDX_LOCAL                      OHS_PART             LOCAL              PREFIXED
IDX_LOCAL_NON_PREFIXED         OHS_PART             LOCAL              NON_PREFIXED

SQL>

drop index idx_local;
CREATE INDEX idx_global_prefixed ON ohs_part(pdate)
GLOBAL PARTITION BY RANGE(pdate)
(partition ohs_ind_201701 values less than (to_date('2017-02-01','yyyy-mm-dd')),
 partition ohs_ind_201702 values less than (to_date('2017-03-01','yyyy-mm-dd')),
 partition ohs_ind_201703 values less than (to_date('2017-04-01','yyyy-mm-dd')),
 partition ohs_ind_201704 values less than (to_date('2017-05-01','yyyy-mm-dd')),
 partition ohs_ind__max values less than (maxvalue))
/

SQL> drop index idx_local;
Index dropped.
SQL> col index_name for a30
SQL> select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA';
INDEX_NAME                     TABLE_NAME           LOCALITY           ALIGNMENT
------------------------------ -------------------- ------------------ --------------------
IDX_LOCAL_NON_PREFIXED         OHS_PART             LOCAL              NON_PREFIXED

SQL> CREATE INDEX idx_global_prefixed ON ohs_part(pdate)
  2  GLOBAL PARTITION BY RANGE(pdate)
  3  (partition ohs_ind_201701 values less than (to_date('2017-02-01','yyyy-mm-dd')),
  4   partition ohs_ind_201702 values less than (to_date('2017-03-01','yyyy-mm-dd')),
  5   partition ohs_ind_201703 values less than (to_date('2017-04-01','yyyy-mm-dd')),
  6   partition ohs_ind_201704 values less than (to_date('2017-05-01','yyyy-mm-dd')),
  7   partition ohs_ind__max values less than (maxvalue))
  8  /

Index created.
SQL> select index_name,table_name,locality,alignment from dba_part_indexes where owner='OHSDBA';
INDEX_NAME                     TABLE_NAME           LOCALITY           ALIGNMENT
------------------------------ -------------------- ------------------ --------------------
IDX_GLOBAL_PREFIXED            OHS_PART             GLOBAL             PREFIXED
IDX_LOCAL_NON_PREFIXED         OHS_PART             LOCAL              NON_PREFIXED
SQL>

Reference
http://docs.oracle.com/database/122/VLDBG/toc.htm
http://docs.oracle.com/database/122/VLDBG/partition-concepts.htm#VLDBG002

http://docs.oracle.com/database/122/VLDBG/partition-admin.htm#VLDBG003

http://docs.oracle.com/database/122/VLDBG/partition-concepts.htm#VLDBG14025