Oracle分区表基础运维 -06分区表索引

和分区相关的索引概念有很多，有本地索引和全局索引，其中本地索引又分为本地前缀分区索引和本地非前缀分区索引。全局索引分为全局非分区索引和全局分区索引( 全局前缀分区索引 )。

先说明下分区表导致索引失效的操作有哪些：

1 truncate分区

导致全局索引失效，不会导致本地索引失效，truncate分区时指定update global indexes全局索引不会失效。

2 drop分区

导致全局索引失效，本地索引因为分区已经drop掉了,也就不存在索引失效的说法了，drop分区时指定update global indexes全局索引不会失效。

3 split分区

导致全局索引和本地索引都失效，split分区时指定update global indexes全局索引不会失效，split相关的分区本地索引可以重建。

4 add分区

不会导致全局和本地索引失效。

5 exchange分区

导致全局索引失效，不会导致本地索引失效，exchange分区时指定update global indexes全局索引不会失效。

选择本地索引还是全局索引？

在考虑各种因素之后，应该选择并决定分区索引的类型。

Deciding on the Type of Partitioned Index to Use The type of partitioned index to use should be chosen after reviewing various factors.

在决定使用哪种分区索引时，应该按照以下顺序考虑:

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

1. 如果表分区列是索引键的子集，则使用本地索引。如果不是，那么查看第2条。

1. 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.

2. 如果唯一性索引且不包括分区键列，则使用全局索引。否则，继续看第3条。

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.

3.如果您的优先级是可管理性，那么可以考虑使用本地索引。否则，继续看第4条。

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.

4. 如果应用程序是OLTP类型，并且用户需要快速响应时间，那么使用全局索引。如果应用程序是DSS类型，并且用户对吞吐量更感兴趣，那么使用本地索引。

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.

一 本地分区索引

1本地前缀分区索引

2本地非前缀分区索引

二 全局索引

3全局非区索引

4全局分区索引(全局前缀分区索引)

一 本地分区索引

创建本地索引，需指定关键字local

1 创建本地非前缀索引

create index i_range_01 on RANGE_PART_TAB(id) local;

2 创建本地前缀索引

create index i_range_02 on RANGE_PART_TAB(DEAL_DATE) local;

--- 查看索引是否分区

分区表上创建本地索引，默认就是本地分区索引，不存在本地非分区索引。

select index_name, table_name, table_type, partitioned
  from user_indexes
 where table_name = 'RANGE_PART_TAB';

--- 查看分区索引类型等

select index_name, table_name, partitioning_type, locality, alignment
  from user_part_indexes
 where table_name = 'RANGE_PART_TAB';

---本地索引 ,分区个数和表分区个数相同

select dbms_metadata.get_ddl('INDEX','I_RANGE_01','CJC') from dual;

......

select * from user_segments where segment_name='I_RANGE_01';

创建本地索引时，索引分区和表分区数量必须相同，否则报错ORA-14024

CREATE INDEX i_range_03 ON RANGE_PART_TAB(id) 
local (PARTITION p1,
       PARTITION p2,
       PARTITION p3);

如果创建本地索引时指定分区，需要和表分区数量相同。

CREATE INDEX i_range_03 ON RANGE_PART_TAB(id) 
local (PARTITION A1,
       PARTITION A2,
       PARTITION A3,
       PARTITION A4,
       PARTITION A5,
       PARTITION A6,
       PARTITION A7,
       PARTITION A8,
       PARTITION A9,
       PARTITION A10,
       PARTITION A11,
       PARTITION A12,
       PARTITION A13);

二 全局索引

3 全局分非分区索引

在分区表上创建索引，不指定关键字，默认创建的是全局非分区索引。

---创建全局非分区索引

create index i_range_01 on RANGE_PART_TAB(id);

--- create   index  i_range_01  on  RANGE_PART_TAB ( id )   global ;

---查看索引信息

select index_name, table_name, table_type, partitioned
  from user_indexes
 where table_name = 'RANGE_PART_TAB';

select * from user_part_indexes; ---空

---查看段信息

select * from user_segments where segment_name='I_RANGE_01';

---查看索引创建语句

select dbms_metadata.get_ddl('INDEX','I_RANGE_01','CJC') from dual;

4 全局分区索引

 

---创建全局分区索引，分区数量和表分区数量相同

CREATE INDEX i_range_01 ON RANGE_PART_TAB(DEAL_DATE)
 GLOBAL PARTITION BY RANGE(DEAL_DATE)
 ( partition p1 values less than ( TO_DATE ( '2019-06-01' , 'YYYY-MM-DD' )),
partition p2 values less than ( TO_DATE ( '2019-07-01' , 'YYYY-MM-DD' )),
partition p3 values less than ( TO_DATE ( '2019-08-01' , 'YYYY-MM-DD' )),
partition p4 values less than ( TO_DATE ( '2019-09-01' , 'YYYY-MM-DD' )),
partition p5 values less than ( TO_DATE ( '2019-10-01' , 'YYYY-MM-DD' )),
partition p6 values less than ( TO_DATE ( '2019-11-01' , 'YYYY-MM-DD' )),
partition p7 values less than ( TO_DATE ( '2019-12-01' , 'YYYY-MM-DD' )),
partition p8 values less than ( TO_DATE ( '2020-01-01' , 'YYYY-MM-DD' )),
partition p9 values less than ( TO_DATE ( '2020-02-01' , 'YYYY-MM-DD' )),
partition p10 values less than ( TO_DATE ( '2020-03-01' , 'YYYY-MM-DD' )),
partition p11 values less than ( TO_DATE ( '2020-04-01' , 'YYYY-MM-DD' )),
partition p12 values less than ( TO_DATE ( '2020-05-01' , 'YYYY-MM-DD' )),
partition p_max values less than ( maxvalue )
 );

---查看索引信息

select index_name, table_name, table_type, partitioned
  from user_indexes
 where table_name = 'RANGE_PART_TAB';

---查看索引类型 (全局前缀分区索引 )

select index_name, table_name, partitioning_type, locality, alignment
  from user_part_indexes
 where table_name = 'RANGE_PART_TAB';

---查看索引分区

select * from user_ind_partitions where index_name='I_RANGE_01';

---查看索引创建语句

select dbms_metadata.get_ddl('INDEX','I_RANGE_01','CJC') from dual;

......

---查看索引段信息

select * from user_segments where segment_name='I_RANGE_01';

select s.table_name, s.index_name
  from Dba_indexes s
 where s.owner = 'CJC'
minus
select s.table_name, s.index_name
  from dba_part_indexes s
 where s.owner = 'CJC';

创建全局分区索引，索引键列需要和索引分区键一致

CREATE INDEX i_range_02 ON RANGE_PART_TAB(id)
 GLOBAL PARTITION BY RANGE(DEAL_DATE)
 ( partition p1 values less than ( TO_DATE ( '2019-06-01' , 'YYYY-MM-DD' )),
partition p2 values less than ( TO_DATE ( '2019-07-01' , 'YYYY-MM-DD' )),
partition p3 values less than ( TO_DATE ( '2019-08-01' , 'YYYY-MM-DD' )),
partition p4 values less than ( TO_DATE ( '2019-09-01' , 'YYYY-MM-DD' )),
partition p5 values less than ( TO_DATE ( '2019-10-01' , 'YYYY-MM-DD' )),
partition p6 values less than ( TO_DATE ( '2019-11-01' , 'YYYY-MM-DD' )),
partition p7 values less than ( TO_DATE ( '2019-12-01' , 'YYYY-MM-DD' )),
partition p8 values less than ( TO_DATE ( '2020-01-01' , 'YYYY-MM-DD' )),
partition p9 values less than ( TO_DATE ( '2020-02-01' , 'YYYY-MM-DD' )),
partition p10 values less than ( TO_DATE ( '2020-03-01' , 'YYYY-MM-DD' )),
partition p11 values less than ( TO_DATE ( '2020-04-01' , 'YYYY-MM-DD' )),
partition p12 values less than ( TO_DATE ( '2020-05-01' , 'YYYY-MM-DD' )),
partition p_max values less than ( maxvalue )
 );

创建全局分区索引，索引分区数量可以和表分区数量不同

CREATE INDEX i_range_01 ON RANGE_PART_TAB(DEAL_DATE)
 GLOBAL PARTITION BY RANGE(DEAL_DATE)
 ( partition p1 values less than ( TO_DATE ( '2019-06-01' , 'YYYY-MM-DD' )),
partition p2 values less than ( TO_DATE ( '2019-07-01' , 'YYYY-MM-DD' )),
partition p3 values less than ( TO_DATE ( '2019-08-01' , 'YYYY-MM-DD' )),
partition p_max values less than ( maxvalue )
 );

创建全局分区索引，索引列可以和分区表分区列不同

CREATE INDEX i_range_01 ON RANGE_PART_TAB(ID)
 GLOBAL PARTITION BY RANGE(ID)
 ( partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p_max values less than ( maxvalue )
 );

范围分区表上，不能创建列表分区索引，

CREATE INDEX i_range_01 ON RANGE_PART_TAB(AREA_CODE)
GLOBAL partition by list (AREA_CODE)
(
partition p_591 values  (591),
partition p_592 values  (592),
partition p_593 values  (593),
partition p_594 values  (594),
partition p_595 values  (595),
partition p_596 values  (596),
partition p_597 values  (597),
partition p_598 values  (598),
partition p_599 values  (599),
partition p_other values  (DEFAULT)
);