数据库索引（Oracle ）

最新推荐文章于 2025-05-27 22:55:24 发布

原创最新推荐文章于 2025-05-27 22:55:24 发布 · 5.6k 阅读

5 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了Oracle数据库索引的创建原则，包括单表索引数量限制、索引字段数、索引匹配与顺序、唯一性索引的影响、索引的选择性及存储位置。还提到了函数索引的效率问题、统计信息的收集与索引重建，以及如何判断索引使用情况和降低高水位线的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引的建立一般规则

单表索引不超过6个。
每个索引不超过3个字段。
索引匹配时，可以包含关系，但脚本中的字段在索引的前面的连续字段时，正常索引，否则会变成skip索引（索引性能下降70%)，如SQL条件里有C1，C3字段，如果索引是C1， C2，C3，则是 Skip index，如果是C1,C3,C2，则是正常索引。
唯一性索引有可能会影响到查询性能，主要是统计信息处理导致脚本不去用更优化的索引，而是用唯一索引。可以考虑用采隐藏索引技术。
脚本实现的时候，索引可选择性高的条件放在条件语句的前面，可以提高脚本性能。
索引单独表空间，存储在一起时，效果不大，需要在不同的存储设备上。
函数索引（TO_DATE)并不能提升效率，要从索引使用上分析。
可以根据历史统计信息查找索引使用的次数，并分析索引合理性，可以监控索引使用情况（会影响性能）。
查索引选择性：

select a.column_name,   
    b.num_rows,    
    a.num_distinct Cardinality,  
    round(a.num_distinct / b.num_rows * 100, 2) selectivity   
from dba_tab_col_statistics a, dba_tables b    
where a.owner = b.owner      
and a.table_name = b.table_name      
and a.owner = upper('XIR_TRD')      
and a.table_name = upper('TTRD_ACCOUNTING_SECU_OBJ_HIS')   
and a.column_name = upper('TSK_ID');

10. 统计信息收集与索引重建统计信息自动收集，数据量变化超过 20%自动，定时收集。

--收集单表的统计信息

call dbms_stats.gather_table_stats(user, '表名');

--收集整个schema的统计信息

call dbms_stats.gather_schema_stats(user);

--普通索引重建

SELECT 'alter index ' || INDEX_NAME || ' rebuilding on line

;', T.*  FROM   USER_INDEXES T

WHERE  T.TEMPORARY = 'N' AND

       INDEX_TYPE = 'NORMAL' AND

       T.PARTITIONED = 'NO';

11. 如何判断索引是否使用

SELECT *

FROM   DBA_INDEXES

WHERE  (OWNER, INDEX_NAME) NOT IN

       (SELECT OBJECT_OWNER, OBJECT_NAME

        FROM   DBA_HIST_SQL_PLAN) ;

12. 隐藏索引

Alter index 索引名  invisible / visible;

13. 表的高水位

分配的表空间

SELECT segment_name, segment_type, blocks  FROM dba_segments

WHERE segment_name=’TTRD_ACCOUNTING_SECU_OBJ_HIS’;

使用表空间

SELECT table_name,num_rows,blocks,empty_blocks

FROM user_tables

WHERE table_name=' TTRD_ACCOUNTING_SECU_OBJ_HIS '

当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用 delete 语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低。Select 语句扫描的数据块增加。降低方法：

Truncate 执行表重建指令 alter table table_name move;

 alter table table_name shrink space;

重建表导出/导入表

   alter   table  table_name  deallocate   unused;