共计 6730 个字符，预计需要花费 17 分钟才能阅读完成。
本篇内容主要讲解“Oracle Partition 怎么使用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“Oracle Partition 怎么使用”吧!
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 create index idx_global on ohs_part(pdate,id) global;
Index created.
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 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 create index idx_local_non_prefixed on ohs_part(id,pdate) local;
Index created.
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
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到此，相信大家对“Oracle Partition 怎么使用”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
