Oracle 19c约束DDL避免重复创建索引问题咨询

一、两类约束的核心差异 #

• 第一种场景：约束与索引是绑定创建的（索引由系统自动生成，或索引名与约束名一致）。此时Oracle生成的DDL仅通过USING INDEX指定索引存储参数，不会显式写出CREATE INDEX语句——因为索引是随约束创建过程自动生成的。
• 第二种场景：约束依赖预先独立创建的命名索引（即先建索引，再基于该索引创建约束），且索引名与约束名不同。Oracle生成DDL时会包含完整的CREATE UNIQUE INDEX语句，试图重建指定名称的索引，但如果该索引已存在，就会触发ORA-00955错误。

二、生成不含显式CREATE INDEX的约束DDL #

方法1：通过DBMS_METADATA调整参数生成 #

利用DBMS_METADATA的转换参数，屏蔽索引创建语句的生成：

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'INDEXES', FALSE); -- 关键参数：不包含索引创建逻辑
END;
/

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', 'MY_CONSTRAINT2', 'MY_OWNER') FROM DUAL;

执行后生成的DDL会直接引用已存在的索引，格式类似：

ALTER TABLE "MY_OWNER"."MY_TABLE2" ADD CONSTRAINT "MY_CONSTRAINT2" UNIQUE ("NAME", "ID")
USING INDEX "MY_OWNER"."MY_INDEX2" PCTFREE 10 INITRANS 2 ...;

方法2：手动修改现有DDL #

如果已经生成了带CREATE INDEX的DDL，直接删除括号内的索引创建语句，改为引用已存在的索引名即可：
原DDL：

ALTER TABLE "MY_OWNER"."MY_TABLE2" ADD CONSTRAINT "MY_CONSTRAINT2" UNIQUE ("NAME", "ID")
USING INDEX (CREATE UNIQUE INDEX "MY_OWNER"."MY_INDEX2" ON "MY_OWNER"."MY_TABLE2" ("NAME", "ID") PCTFREE 10 ...);

修改后：

ALTER TABLE "MY_OWNER"."MY_TABLE2" ADD CONSTRAINT "MY_CONSTRAINT2" UNIQUE ("NAME", "ID")
USING INDEX "MY_OWNER"."MY_INDEX2";

若需要保留索引的存储参数，可将参数移至USING INDEX后：

ALTER TABLE "MY_OWNER"."MY_TABLE2" ADD CONSTRAINT "MY_CONSTRAINT2" UNIQUE ("NAME", "ID")
USING INDEX "MY_OWNER"."MY_INDEX2" PCTFREE 10 INITRANS 2 MAXTRANS 255 ...;

方法3：查询数据字典手动拼接DDL #

通过查询Oracle数据字典视图，自行构建符合需求的约束DDL：

SELECT 'ALTER TABLE "' || c.owner || '"."' || c.table_name || '" ADD CONSTRAINT "' || c.constraint_name || '" UNIQUE (' ||
       LISTAGG('"' || cc.column_name || '"', ', ') WITHIN GROUP (ORDER BY cc.position) ||
       ') USING INDEX "' || i.index_name || '";' AS constraint_ddl
FROM user_constraints c
JOIN user_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
JOIN user_indexes i ON c.owner = i.owner AND c.index_name = i.index_name
WHERE c.constraint_name = 'MY_CONSTRAINT2'
  AND c.owner = 'MY_OWNER'
GROUP BY c.owner, c.table_name, c.constraint_name, i.index_name;

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