索引管理
1 统计信息
1.1 统计信息定义与范畴
统计信息用于描述数据库中表、索引的大小、规模、数据分布状况，涵盖表行数、块数、平均每行大小，索引高度、叶子节点数，以及索引字段行数、不同值大小等内容。
1.2 统计数据对象分类
表统计信息：聚焦表整体数据特征，如行数、所占页数、平均记录长度。
列统计信息：统计列数据的分布特征。
索引统计信息：统计索引列的数据分布特征。
1.3 统计信息生成流程
1）确定采样对象
依据数据对象，明确分析数据：
表：计算行数、所占页数目、平均记录长度 。
列：统计列数据分布特征 。
索引：统计索引列数据分布特征 。
2）确定采样率
用户可按需设定，缺省时由内部算法确定 。
3）生成统计信息
表：生成行数、所占页数目、平均记录长度等汇总数据。
列和索引：生成直方图（频率直方图、等高直方图），以1万个不同值为界，少于1万用频率直方图（桶高度不同），否则用等高直方图（桶高度相同）。
1.4 统计信息作用
执行查询时，代价算法利用统计信息精准计算操作成本，确定对象访问路径、连接方式、连接顺序，助力选择最优执行计划。
1.5 静态统计信息收集方式
1）自动收集
前提：INI参数AUTO_STAT_OBJ为1或2，执行SP_CREATE_AUTO_STAT_TRIGGER过程实现。
补充：可创建定时作业（调用SP_DB_STAT_INIT过程）更新统计信息。
2）手动收集
①方式1：dbms_stats包
搜集表统计信息：dbms_stats.gather_table_stats('TEST','T_F1');
②方式2：SP_STAT相关系统函数
查看函数：select name, id from v$ifun t where name like 'SP%_STAT%_INIT';
搜集表统计信息（不含列）：SP_TAB_STAT_INIT
如sp_tab_stat_init('TEST', 'T1');
搜集列统计信息：SP_COL_STAT_INIT
如sp_col_stat_init('TEST', 'T1', 'ID');
① 方式3：stat on语句
收集表统计信息（不含列）：stat on test.t_f1;
收集列统计信息：stat 100 on test.t_f1(id);
1.6 统计信息查看
1）表统计信息（dbms_stats包）：
dbms_stats.table_stats_show('TEST', 'T_F1');
2）列统计信息及直方图信息（dbms_stats包）：
dbms_stats.column_stats_show('TEST', 'T_F1', 'ID');
3）查询统计信息
SELECT NUM_ROWS, BLOCKS FROM DBA_TAB_STATISTICS
WHERE TABLE_OWNER = 'TEST' AND TABLE_NAME = 'T_F1';
补充：查询路径《系统管理员手册》，《系统包使用手册》，结果集含大量空值等不适用于建立索引，在业务低谷期搜集统计信息。
2 介绍索引
2.1 索引物理存储分类
1）聚集索引（一级索引、主索引）
原理：按聚集索引键构造B树，表数据存于B树叶子节点，定位索引可直接在B树找数据。
特性：每个表仅能有1个聚集索引。
2）非聚集索引（二级索引、辅助索引）
原理：二级索引列与聚集索引列共同存于B树叶子节点。查非聚集/聚集索引键值可直接找；查其他数据，需回聚集索引查找。
特性：每个表可建多个非聚集索引。
2.2 索引优缺点
1）优点
大幅加快数据检索速度，优化查询效率。
加速表与表之间的连接操作，提升关联查询性能。
2）缺点
占用物理存储空间，增加存储成本。
表执行DML操作时，需同步维护索引，增加数据库开销。
2.3 达梦支持的索引类型
聚集索引、唯一索引、函数索引、位图索引、分区索引、复合索引、全文索引。
2.4 创建索引的规则
①常参与表连接的字段（连接字段），建议建索引，优化关联查询。
②频繁出现在WHERE子句的字段（尤其大表字段），建索引提升筛选效率。
③优先在选择性高（不同值占比高）的字段建索引，过滤数据更高效。
④索引建在小字段上，大文本、超长字段不建议（索引存储、维护成本高）
3 创建索引
3.1 索引类型分类与说明
1）唯一索引
特性：创建表时，若定义主键（PRIMARY KEY）或唯一约束（UNIQUE），数据库自动创建对应唯一索引，保障索引列值唯一性。
创建语法： Create unique index ind_id on t_f2(id);
2）复合索引
定义：基于多列创建索引，列顺序影响索引生效逻辑，需结合查询条件设计。
创建语法：
CREATE INDEX ind_emp ON EMPINFO (employee_id, employee_name);
3）位图索引
适用场景：列值分布稀疏（不同值少，如性别sex仅“男/女”）；适用于OLAP系统（查询频繁、数据更新少），不建议在高频更新表使用（更新会大幅增加索引维护成本）。
创建语法：CREATE BITMAP INDEX ind_sex ON t5(sex);
4）函数索引
定义：基于函数/表达式（以表列为变量）创建的索引，优化“函数/表达式过滤”的查询场景。
创建语法（示例：对name列转大写后建索引）
CREATE INDEX ind_name ON test.t_f1 (UPPER(name));
5）局部索引与全局索引（分区表场景 ）
默认规则：分区表创建索引时，默认生成局部索引（LOCAL）；指定GLOBAL关键字则为全局索引。
查询适配：分区表查询条件含“分区键”时，LOCAL索引更高效；全局索引需跨分区检索。
索引维护：分区执行TRUNCATE、DROP、SPLIT、MERGE等操作，数据库自动重建全局索引。
创建语法：
局部索引：
CREATE INDEX ind_f1_id ON t_f1(id);
全局索引：
CREATE INDEX ind_f1_id ON t_f1(id) GLOBAL;
6）类型查询（区分局部/全局）：
SELECT a.TABLE_NAME, a.INDEX_NAME, a.PARTITIONED
FROM SYS.USER_INDEXES a
WHERE a.TABLE_NAME = 'T_F1';
PARTITIONED 为 YES → 局部索引
PARTITIONED 为 NO → 全局索引
3.2 索引设计与使用建议
①复合索引：需结合查询条件的列顺序设计，优先匹配WHERE、JOIN子句的列顺序。
②位图索引：避免在高频更新表使用，防止索引维护拖慢DML性能。
③函数索引：针对固定函数/表达式查询（如UPPER(name)过滤），提前创建可规避全表扫描。
④分区表索引：优先用LOCAL索引，减少全局索引跨分区检索的性能开销；需跨分区聚合查询时，再评估全局索引。
补充：查询路径《SQL语言使用手册》，高并发的表，索引和表存放在不同的表空间。
4 维护索引
4.1 索引信息查询
通过查询系统视图 SYS.USER_INDEXES，可获取索引关联表、名称、状态、可见性等核心信息：
SELECT
a.TABLE_NAME, -- 索引关联的表名
a.INDEX_NAME, -- 索引名称
a.STATUS, -- 索引状态（有效/无效等）
a.VISIBILITY -- 索引可见性（可见/不可见）
FROM SYS.USER_INDEXES a;

4.2 索引状态与可见性管理
状态类型 操作影响（DML 与执行计划） 关键命令
无效（UNUSABLE） DML 不维护索引，执行计划不走该索引 ALTER INDEX IND_EMP_ID UNUSABLE;（置为无效）
ALTER INDEX ind_emp_id REBUILD;（重建恢复有效）
不可见（INVISIBLE） DML 自动维护索引，但执行计划默认不走该索引 ALTER INDEX ind_emp_id INVISIBLE;（置为不可见）
ALTER INDEX ind_emp_id VISIBLE;（恢复可见）
4.3 索引监控
1）监控开关控制
开启监控：追踪索引是否被执行计划使用
ALTER INDEX ind_emp_id MONITORING USAGE;
关闭监控：停止追踪
ALTER INDEX ind_emp_id NOMONITORING USAGE;
2）监控信息查询
通过 SYS.V$OBJECT_USAGE 视图，查看索引的使用统计（如是否被使用、使用次数等 ）：
SELECT * FROM SYS.V$OBJECT_USAGE;
4.4 索引删除
直接删除索引，释放存储空间（删除后需评估查询性能影响）
DROP INDEX ind_emp_id;
4.5 操作逻辑总结
操作目标 核心命令 / 视图 关键说明
查看索引基础信息 SYS.USER_INDEXES 含表名、索引名、状态、可见性
管理索引有效性 ALTER INDEX ... UNUSABLE/REBUILD 无效时 DML 不维护索引
管理索引可见性 ALTER INDEX ... INVISIBLE/VISIBLE 不可见时执行计划默认跳过
监控索引使用情况 ALTER INDEX ... MONITORING/NOMONITORING + V$OBJECT_USAGE 追踪索引是否被查询使用
删除索引 DROP INDEX 永久删除，需谨慎操作

5 全文索引
5.1 全文检索功能概述
核心目标：实现对大容量非结构化数据的快速查找，解决模糊查询效率问题。
组件特性：作为 DM 服务器独立组件，提供精准的全文检索能力。
5.2 全文索引创建规则
1）适用对象：仅可在基表定义，禁止在系统表、视图、临时表、列存表、外部表上创建。
2）唯一性限制：同一列只能创建一个全文索引。
3）列类型要求：支持CHAR/CHARACTER、VARCHAR/VARCHAR2、LONGVARCHAR、TEXT、CLOB。
4）权限要求：需具备CREATE CONTEXT INDEX权限。
5.3 分词参数类型
分词器名称 分词规则
CHINESE_LEXER 中文最少分词（默认）
CHINESE_VGRAM_LEXER 机械双字分词
CHINESE_FP_LEXER 中文最多分词
ENGLISH_LEXER 英文分词
DEFAULT_LEXER 默认分词（同中文最少分词）
5.4 系统表生成规则
创建全文索引ind_address后，自动生成以下系统表：
CTI$ind_address$I：保存分词结果
CTI$ind_address$P：保存基表增量变化数据
CTI$ind_address$N：保存原纪录ROWID与新纪录OCID的映射关系
CTI$ind_address$D：保存删除的OCID记录
5.5 全文索引操作语法
1）创建与全量更新
-- 创建索引（未立即填充）
CREATE CONTEXT INDEX ind_address ON PERSON.ADDRESS(ADDRESS1);
-- 全量更新索引
ALTER CONTEXT INDEX PERSON.ind_address ON PERSON.ADDRESS REBUILD;
2）数据修改与增量更新
-- 修改数据
UPDATE PERSON.address SET ADDRESS1='洪山区紫云府1-2401' WHERE ADDRESSID=13;
COMMIT;
-- 增量更新索引
ALTER CONTEXT INDEX PERSON.ind_address ON PERSON.ADDRESS INCREMENT;
3）创建时自动填充
CREATE CONTEXT INDEX ind_address ON PERSON.ADDRESS(ADDRESS1)
LEXER default_lexer sync;
4）事务级自动更新
CREATE CONTEXT INDEX ind_address ON PERSON.ADDRESS(ADDRESS1)
LEXER default_lexer SYNC TRANSACTION;
5）删除索引
DROP CONTEXT INDEX PERSON.ind_address ON PERSON.ADDRESS;
5.6 关键说明
1）词库特性：中文分词依赖系统只读词库，不允许修改。
2）索引维护：
全量更新（REBUILD）：重新构建整个索引。
增量更新（INCREMENT）：仅更新修改后的数据。
3）同步模式：
SYNC：创建时自动填充索引数据。
SYNC TRANSACTION：提交事务时自动更新索引。
4）补充：查询路径《管理员手册》