Oracle关联all_tab_columns等三张系统视图结果异常排查

问题复现 #

首先创建测试对象：

CREATE SEQUENCE my_seq;
CREATE TABLE mytable (
    id NUMBER(10) DEFAULT my_seq.nextval PRIMARY KEY,
    name VARCHAR2(10) NOT NULL,
    version FLOAT, 
    active CHAR, 
    updated DATE CONSTRAINT date_uk UNIQUE
)

需求是关联all_tab_columns、all_cons_columns、all_constraints三张Oracle系统视图，查询目标表的列及对应约束属性。

两两关联测试（结果符合预期） #

• 关联all_tab_columns和all_cons_columns，执行SQL：

SELECT at.owner, at.column_name, ac.constraint_name
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac 
  ON (at.table_name = ac.table_name 
    AND at.owner = ac.owner 
    AND at.column_name = ac.column_name)
WHERE at.table_name = 'MYTABLE';

返回结果：

OWNER           COLUMN_NAME     CONSTRAINT_NAME
--------------- --------------- ---------------
TEST            ID              SYS_C008423
TEST            NAME            SYS_C008422
TEST            VERSION
TEST            ACTIVE
TEST            UPDATED         DATE_UK

• 关联all_cons_columns和all_constraints，执行SQL：

SELECT ac.owner, ac.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_cons_columns ac
JOIN all_constraints cc 
  ON (ac.constraint_name = cc.constraint_name)
WHERE ac.table_name = 'MYTABLE';

返回结果：

OWNER           COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- --------------- ---------------
TEST            NAME            SYS_C008422     C               GENERATED NAME
TEST            ID              SYS_C008423     P               GENERATED NAME
TEST            UPDATED         DATE_UK         U               USER NAME

三表关联测试（结果异常） #

执行三表关联SQL：

SELECT at.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac 
  ON (at.table_name = ac.table_name 
    AND at.owner = ac.owner)
LEFT JOIN all_constraints cc 
  ON (ac.constraint_name = cc.constraint_name 
    AND ac.owner = cc.owner 
    AND ac.table_name = cc.table_name)
WHERE ac.table_name = 'MYTABLE';

返回结果共15行，出现大量不符合预期的匹配：

COLUMN_NAME     CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
--------------- --------------- --------------- ---------------
ID              SYS_C008422     C               GENERATED NAME
NAME            SYS_C008422     C               GENERATED NAME
VERSION         SYS_C008422     C               GENERATED NAME
ACTIVE          SYS_C008422     C               GENERATED NAME
UPDATED         SYS_C008422     C               GENERATED NAME
ID              SYS_C008423     P               GENERATED NAME
NAME            SYS_C008423     P               GENERATED NAME
VERSION         SYS_C008423     P               GENERATED NAME
ACTIVE          SYS_C008423     P               GENERATED NAME
UPDATED         SYS_C008423     P               GENERATED NAME
ID              DATE_UK         U               USER NAME
NAME            DATE_UK         U               USER NAME
VERSION         DATE_UK         U               USER NAME
ACTIVE          DATE_UK         U               USER NAME
UPDATED         DATE_UK         U               USER NAME

预期返回结果：

COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_TYPE GENERATED
----------- --------------- --------------- --------------
ID          SYS_C008423      P               GENERATED NAME
NAME        SYS_C008422      C               GENERATED NAME
VERSION    
ACTIVE     
UPDATED     DATE_UK         U               USER NAME

测试环境为Oracle 12 XE，可通过Docker快速复现：

• 启动容器命令：

docker run --name oracle_test \
  -e "ORACLE_PASSWORD=test" \
  -e "APP_USER=test" \
  -e "APP_USER_PASSWORD=test" \
  -p 1521:1521 \
  -d gvenzl/oracle-xe:21-slim

• 等待容器启动完成后，连接数据库：

docker exec -it oracle_test sqlplus -l test/test@localhost:1521/XEPDB1

• 连接成功后执行上述建表SQL即可复现问题。

问题排查与修正 #

按照建议将all_constraints的关联改为内连接后，结果仍然异常：

SELECT at.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac ON (at.table_name = ac.table_name AND at.owner = ac.owner)
JOIN all_constraints cc ON (ac.constraint_name = cc.constraint_name AND ac.owner = cc.owner AND ac.table_name = cc.table_name)
WHERE ac.table_name = 'MYTABLE';

错误原因 #

三表关联时，all_tab_columns和all_cons_columns的JOIN条件遗漏了列名匹配：at.column_name = ac.column_name。
缺少该条件时，每一列都会和当前表下所有约束对应的列做笛卡尔积匹配：mytable共5列、3个约束，最终返回5*3=15行错误数据，和实际返回结果行数一致。
之前两两关联时写了列名匹配条件所以结果正常，三表关联时漏写该条件是核心错误。

修正后SQL #

补全列名匹配条件，同时增加owner过滤避免同表名不同用户的匹配问题：

SELECT at.column_name, ac.constraint_name, cc.constraint_type, cc.generated
FROM all_tab_columns at
LEFT JOIN all_cons_columns ac 
  ON at.table_name = ac.table_name 
    AND at.owner = ac.owner 
    AND at.column_name = ac.column_name -- 补全漏写的列名关联条件
LEFT JOIN all_constraints cc 
  ON ac.constraint_name = cc.constraint_name 
    AND ac.owner = cc.owner 
    AND ac.table_name = cc.table_name
WHERE at.table_name = 'MYTABLE'
  AND at.owner = 'TEST';

执行该SQL即可得到预期结果。

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