Oracle 分区索引简介

---

一. 什么是分区索引

在 Oracle 数据库中，分区索引（Partitioned Index） 是一种与分区表相关的索引，用于提高分区表上的查询性能和维护效率。
分区索引将数据划分为多个逻辑分区，与表的分区结构紧密关联。这种索引的设计旨在通过限制索引扫描的范围来优化查询性能，尤其是对于大规模数据集。

⏹使用分区索引的契机

• 为了避免移动数据时重建整个索引，可对索引分区，在重建索引时，只需重建与数据分区相关的索引；
• 在对分区表进行维护时，为了避免整个表的索引处于不可用状态，可将索引进行分区。当对分区表进行维护时，只需将该分区表的索引置为不可用状态，并不会影响到其它分区索引的使用；
• 索引表的急剧增长导致索引条目的剧增，使得整个索引表非常大，影响索引速度，需要对索引分区；

---

二. 分区索引的种类

2.1 局部分区索引（Local Partitioned Index）

• 每个索引分区与表的一个分区一一对应。
• 如果删除或维护表中的一个分区，只需对应地删除或维护与之相关的索引分区，操作简单。
• 查询时，Oracle 会自动选择需要访问的索引分区，减少不必要的扫描范围。
• 适用于对分区表的分区列进行查询的场景。

⏹示意图

2.2 全局分区索引（Global Partitioned Index）

• 索引的分区与表的分区没有直接对应关系，而是按索引自己的分区键进行分区。
• 全局分区索引通常在查询涉及非分区键时使用，可以覆盖整个表的所有分区。
• 适合数据访问跨分区的情况，且分区键不是查询的过滤条件时使用。

⏹示意图

---

三. 分区索引的创建

⏹创建局部(local)分区索引

CREATE INDEX CHARGEFIXEDWORK_DB1 ON CHARGE_FIXED_WORK (CHARGE_REGST_BUSINS_DATE, CHARGE_NO) LOCAL;

⏹创建全局(global)分区索引

CREATE INDEX CHARGEFIXEDWORK_DB2 ON CHARGE_FIXED_WORK (ATZT_NO) GLOBAL PARTITION BY HASH (ATZT_NO) PARTITIONS 4;

---

四. 分区索引查看

4.1 USER_IND_COLUMNS 表

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'CHARGE_FIXED_WORK' ORDER BY INDEX_NAME;

4.2 USER_INDEXES 表

SELECTINDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, TO_CHAR(last_analyzed, 'YYYY/MM/DD HH24:MI:SS') AS LAST_ANALYZED
FROMUSER_INDEXES 
WHEREINDEX_NAME = 'CHARGEFIXEDWORK_DB1';

---

五. 分区索引的维护

5.1 重新构建索引

⏹重新构建全局索引

ALTER INDEX global_index_name REBUILD;

• 分区表的某些操作（如 TRUNCATE PARTITION 或 DROP PARTITION）会导致全局索引失效，需要重新维护。
• 在删除指定分区时，重新构建全局索引

ALTER TABLE partitioned_table_name TRUNCATE PARTITION partition_name UPDATE GLOBAL INDEXES;

⏹重新构建本地索引的某个分区

ALTER INDEX local_index_name REBUILD PARTITION partition_name;

5.2 删除索引

• 删除整个索引（包括所有分区）
• 适用于所有类型的索引，包括全局索引和本地索引。

DROP INDEX index_name;

• 删除本地索引的指定分区。
• 本地索引和分区一一对应，每个分区都会有自己的本地索引
  • 例如我们一共有4个分区，名称分别叫做SYS_P564，SYS_P565，SYS_P566，SYS_P567
  • 这4个分区都有自己的本地索引，名称叫做CHARGEFIXEDWORK_DB1
  • 我们现在只想删除SYS_P567分区上的本地索引，保留剩余分区的本地索引

ALTER INDEX local_index_name DROP PARTITION partition_name;

---

六. 项目中的经验教训

从XX年X月开始，围绕DB出现了各种竞合，其中对于同一个index的登录处理时，TXindex contention的问题频发。
也是从去年开始，几乎每个月都会人工的做下面几点，来解决此问题。

• index的本地化(把global index变成local index)
• 不要的index的删除

经调查，往往是在数据parten比较少的项目上加的index会有上述问题。
比如，登录年月日，区分等。
这种项目在登录时，一般只会登录当天的日期，或登录少数的区分值。
如果这些项目上做了gobalindex的话，根据DDL的定义，往往有256个分区来存储这个index。
在登录处理里，写入index的时候，oracle会优先把内容一样的数值放到相同的分区中以便今后的检索查找方便。
但这样一来，tps比较高的登录机能，就会对某一个分区进行高速的写入，最终导致TXindex contention的发生。
而使用local Index的话，则发生竞合的概率会变的很低。

★今后的要求★
案件开发时，需要加新TBL的时候，如果表里面有日期，区分等上述的项目并且要对这样的项目添加index的话，
要检讨是否应该用local index。

⏹参考

在插入分区时，oracle的Local index和Global index的算法有以下不同之处:

Local Index算法:对于Local Index，每个分区会有独立的索引副本，其索引的建立和维护只针对当前分区的数据。
当插入新的分区时，oracle会为该分区建立独立的索引副本，这些分区索引可以并行建立，
因此在分区插入时可以获得更好的性能。这对于分区表的查询和维护操作来说是最有效的选择。Global Index算法:对于G1obal Index，所有分区共字同一个索引副本，索引的建立和维护是针对整个表的。
因此，在插入新的分区时，需要对整个索引进行更新和重建，这可能涉及到大量的数据移动和索引重建，导致性能下降。
因此，在插入分区时，Global Index相对于Local Index来说性能较低。

综上所述，当频繁进行分区插入操作时，Local Index相对于Global ndex来说更加高效和适用。
但是Local Index也对存储需求有较高的要求，因为每个分区都需要存放一个独立的索引副本。