Oracle 19.3/21.3如何筛选手动创建的CREATE [UNIQUE] INDEX索引？

在Oracle 19.3和21.3版本中，要区分CREATE [UNIQUE] INDEX手动创建的索引与PRIMARY KEY、UNIQUE约束自动生成的索引，原方案关联ALL_CONSTRAINTS的查询耗时过长（19.3约6秒，21.3约15秒），且ALL_INDEXES.GENERATED仅标识索引名是否自动生成，无法判断索引的创建来源。以下是优化后的高效解决方案：

核心查询语句 #

SELECT 
    i.owner,
    i.index_name,
    i.table_name,
    i.unique_flag
FROM 
    all_indexes i
LEFT JOIN 
    all_constraints c 
    ON i.owner = c.owner 
    AND i.index_name = c.index_name
WHERE 
    c.constraint_name IS NULL
    AND i.index_type NOT IN ('LOB', 'FUNCTION-BASED DOMAIN')
    AND i.owner NOT IN ('SYS', 'SYSTEM', 'SYSAUX', 'OUTLN')
    -- 可选：若需排除手动创建但名称由系统生成的索引，添加下面一行
    -- AND i.generated = 'N'
ORDER BY 
    i.owner, i.table_name, i.index_name;

优化逻辑说明 #

1. 约束关联过滤：通过左关联ALL_CONSTRAINTS并筛选c.constraint_name IS NULL，直接定位未被任何约束引用的索引——这部分就是纯手动创建的索引（约束自动生成的索引必然会被对应约束关联）。
2. 特殊索引排除：过滤LOB、FUNCTION-BASED DOMAIN等Oracle自动为特殊字段生成的索引，避免误将系统索引判定为手动创建。
3. 系统用户过滤：排除SYS、SYSTEM等系统用户下的索引，这类索引几乎都是系统自动维护的，无需纳入统计。
4. GENERATED字段的可选性：如果你的场景允许手动创建时不指定索引名（由Oracle自动生成名称），可以去掉i.generated = 'N'的条件；如果只需要统计手动指定名称的索引，则保留该条件。

性能提升原因 #

原查询可能存在低效关联或冗余数据处理，而左关联+NULL过滤的方式利用Oracle优化器的索引扫描能力，快速排除被约束关联的索引；同时提前过滤系统用户和特殊索引类型，大幅减少了需要处理的数据量，从而降低执行耗时。

验证方式 #

若要确认单个索引的创建方式，可通过DBMS_METADATA.GET_DDL查看其原始DDL：

SELECT DBMS_METADATA.GET_DDL('INDEX', '你的索引名', '索引所属用户') FROM DUAL;

手动创建的索引会返回CREATE [UNIQUE] INDEX语句，约束生成的索引则会关联对应的约束DDL信息。

内容的提问来源于stack exchange，提问作者Seb