oracle数据库表索引个数建议

在Oracle数据库中，虽然理论上每个表最多允许创建1000个索引（受MAX_INDEXES参数限制），但在实际生产环境中，索引的数量需要严格控制，以避免性能和维护问题。以下是根据不同场景的通用建议：

1、一般性建议
OLTP系统（高并发写入/事务处理）：
建议每个表不超过 5-10个索引。
原因：索引过多会显著降低DML（INSERT/UPDATE/DELETE）性能，增加锁争用和日志生成量。

OLAP系统（分析型查询为主）：
可适当放宽至 10-20个索引，但需结合分区、位图索引等优化手段。
原因：OLAP侧重复杂查询，索引对查询加速的收益可能高于写入开销。

2、关键考量因素
（1）索引对写入的影响
每个索引都会增加DML操作的开销。例如：

INSERT：需同步更新所有索引。
UPDATE：若修改了索引列的值，需删除旧索引条目并插入新条目。
DELETE：需清理所有索引中的相关条目。

建议：对频繁写入的表，索引数量需严格压缩。

（2）查询模式优化
高频查询条件：为WHERE/JOIN/GROUP BY/ORDER BY中频繁使用的列创建索引。

高选择性列优先：选择性高的列（如唯一值多的列）更适合单列索引。

复合索引替代单列索引：若多个列常联合查询，优先设计复合索引，而非单独为每个列建索引。

（3）索引类型选择
位图索引：适用于低基数列（如性别、状态），但仅适用于OLAP（静态数据），OLTP中可能引发锁争用。
函数索引：针对表达式或函数查询（如UPPER(name)）。
分区索引：大表可结合分区策略（Local/Global索引）减少维护开销。

3、 最佳实践
定期审查索引：
使用DBA_INDEXES和DBA_IND_STATISTICS监控索引使用频率，删除未使用的索引。

– 查询未使用的索引
SELECT * FROM dba_indexes WHERE table_name=‘YOUR_TABLE’ AND status=‘UNUSED’;
避免冗余索引：
例如，若已有复合索引(A, B)，单列索引(A)通常是冗余的。

利用索引合并工具：
使用Oracle SQL Tuning Advisor或第三方工具分析索引优化建议。

测试验证：
在测试环境中模拟生产负载，验证索引增减对性能的影响。

4、示例场景
用户表（OLTP）：

主键索引（必需）
唯一索引（如手机号、邮箱）
外键索引（如部门ID）
高频查询列（如注册时间、状态）
总数建议：5-7个。

订单表（混合负载）：
主键索引
买家ID + 订单时间（复合索引）
商品ID + 状态（复合索引）
总数建议：6-8个。

总结
硬性上限：1000个（但实际极少接近此值）。
合理范围：根据系统类型和负载，通常建议 5-15个，需结合业务场景动态调整。
核心原则：“按需创建，定期清理，复合优先，避免冗余”。

索引失效
方法：
1、利用“select status from user_indexes where index_name=‘索引名称’”语句，若结果返回VALID，则索引没有失效；
2、利用“select status from DBA_IND_PARTITIONS”语句查看分区索引状态，如果状态不是VALID的，那么就是失效的索引；
3、利用“select status from dba_indexes”查看普通索引状态，如果状态不是VALID的，那么就是失效的索引。

ORACLE查看某个表的索引状态

状态列STATUS说明：
valid:当前索引有效
N/A :分区索引 有效
unusable:索引失效

在Oracle数据库中，哪些操作会导致索引失效？
答：当某些操作导致数据行的ROWID改变，索引就会完全失效。可以分普通表和分区表来讨论哪些操作将导致索引失效。

（一）普通表索引失效的情形如下所示：

①　手动置索引无效：ALTER INDEX IND_OBJECT_ID UNUSABLE;
②　如果对表进行MOVE操作（包含移动表空间和压缩操作）或在线重定义表后，那么该表上所有的索引状态会变为UNUSABLE。MOVE操作的SQL语句为：ALTER TABLE TT MOVE;。
③　SQL*Loader加载数据。

在SQLLoader加载过程中会维护索引，由于数据量比较大，在SQLLoader加载过程中出现异常情况，也会导致Oracle来不及维护索引，导致索引处于失效状态，影响查询和加载。异常情况主要有：在加载过程中杀掉SQL*Loader进程、重启或表空间不足等。

（二）分区表索引失效的情形如下所示：

①　对分区表的某个含有数据的分区执行了TRUNCATE、DROP操作可以导致该分区表的全局索引失效，而分区索引依然有效，如果操作的分区没有数据，那么不会影响索引的状态。需要注意的是，对分区表的ADD操作对分区索引和全局索引没有影响。
②　执行EXCHANGE操作后，全局索引和分区索引都无条件地会被置为UNUSABLE（无论分区是否含有数据）。但是，若包含INCLUDING INDEXES子句（缺省情况下为EXCLUDING INDEXES），则全局索引会失效，而分区索引依然有效。
③　如果执行SPLIT的目标分区含有数据，那么在执行SPLIT操作后，全局索引和分区索引都会被被置为UNUSABLE。如果执行SPLIT的目标分区没有数据，那么不会影响索引的状态。
④　对分区表执行MOVE操作后，全局索引和分区索引都会被置于无效状态。

对于分区表而言，除了ADD操作之外，TRUNCATE、DROP、EXCHANGE和SPLIT操作均会导致全局索引失效，但是可以加上UPDATE GLOBAL INDEXES子句让全局索引不失效。
重建分区索引的命令为：ALTER INDEX IDX_RANG_LHR REBUILD PARTITION P1：