oracle 数据库查看外键引用列上是否有索引

整体查看外键是否有索引

SELECT CASE  WHEN B.TABLE_NAME IS NULL THEN 'NO INDEXED' 
             ELSE 'INDEXED' 
        END              AS STATUS, 
       A.TABLE_OWNER     AS TABLE_OWNER, 
       A.TABLE_NAME      AS TABLE_NAME, 
       A.CONSTRAINT_NAME AS FK_NAME, 
       A.FK_COLUMNS      AS FK_COLUMNS, 
       B.INDEX_NAME      AS INDEX_NAME, 
       B.INDEX_COLUMNS   AS INDEX_COLUMNS 
FROM   (SELECT A.OWNER                              AS TABLE_OWNER, 
               A.TABLE_NAME                         AS TABLE_NAME, 
               A.CONSTRAINT_NAME                    AS CONSTRAINT_NAME, 
               LISTAGG(A.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS 
          FROM DBA_CONS_COLUMNS A, 
               DBA_CONSTRAINTS B 
         WHERE 1=1
           AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME 
           AND B.CONSTRAINT_TYPE = 'R' 
           AND A.OWNER = B.OWNER 
        GROUP  BY A.OWNER, 
                  A.TABLE_NAME, 
                  A.CONSTRAINT_NAME ) A, 
       (SELECT TABLE_OWNER, 
               TABLE_NAME, 
               INDEX_NAME, 
               LISTAGG(C.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS 
          FROM DBA_IND_COLUMNS C 
         WHERE 1=1
         GROUP BY TABLE_OWNER, 
                  TABLE_NAME, 
                  INDEX_NAME ) B 
WHERE 1=1
  AND A.TABLE_OWNER = B.TABLE_OWNER(+) 
  AND A.TABLE_NAME = B.TABLE_NAME(+) 
  AND B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS || '%' 
  AND B.TABLE_NAME IS NULL
ORDER BY 1 DESC,2,3
;

A表引用别的表，A表列上未创建索引

select dc.owner as source_owner,
       dc.table_name as source_table_name,
       dcc.column_name as source_column_name,
       '||' as flag1,
       dc.constraint_name as source_constraint_name,
       dc.constraint_type as source_constraint_type,
       '||' as flag2,
       dc.r_owner as refer_owner,
       (select ui.table_name
          from user_indexes ui
         where 1 = 1
           and ui.index_name = dc.r_constraint_name) as refer_table_name,
       (select listagg(uic.COLUMN_NAME, ',') within group(order by uic.COLUMN_POSITION)
          from user_ind_columns uic
         where 1 = 1
           and uic.INDEX_NAME = dc.r_constraint_name) as refer_table_col,
       dc.r_constraint_name as refer_constraint_nam,
       '||' as flag3
from user_constraints dc, 
     user_cons_columns dcc
where 1 = 1
  and dc.owner = dcc.owner
  and dc.table_name = dcc.table_name
  and dc.constraint_name = dcc.constraint_name
  and dcc.table_name = 'REPAIR_BALANCE_ORDER'
  and dc.constraint_type = 'R' -- 外键引用
  and not exists (
      select 1
      from user_ind_columns uic
      where 1 = 1
        and dc.table_name = uic.TABLE_NAME
        and dcc.table_name = uic.TABLE_NAME
        and dcc.column_name = uic.COLUMN_NAME)
order by dc.owner, dc.table_name, dcc.column_name
;

别的表引用A表，别的表列上没有建索引

select dc.owner as source_owner,
       dc.table_name as source_table_name,
       dcc.column_name as source_column_name,
       '||' as flag1,
       dc.constraint_name as source_constraint_name,
       dc.constraint_type as source_constraint_type,
       '||' as flag2,
       dc.r_owner as refer_,
       (select ui.table_name
          from user_indexes ui
         where 1 = 1
           and ui.index_name = dc.r_constraint_name) as refer_table_name,
       (select listagg(uic.COLUMN_NAME, ',') within group(order by uic.COLUMN_POSITION)
          from user_ind_columns uic
         where 1 = 1
           and uic.INDEX_NAME = dc.r_constraint_name) as refer_table_col,
       dc.r_constraint_name as refer_constraint_name,
      '||' as flag3
from user_constraints dc, 
     user_cons_columns dcc
where 1 = 1
  and dc.owner = dcc.owner
  and dc.table_name = dcc.table_name
  and dc.constraint_name = dcc.constraint_name
  and dc.r_constraint_name in (
      select ui.constraint_name 
        from user_constraints ui
       where 1 = 1
         and ui.table_name = 'STATUS_ITEM')
  and dc.constraint_type = 'R' -- 外键引用
  and not exists (
      select 1
        from user_ind_columns uic
       where 1 = 1
         and dc.table_name = uic.TABLE_NAME
         and dcc.table_name = uic.TABLE_NAME
         and dcc.column_name = uic.COLUMN_NAME)
order by dc.owner, dc.table_name, dcc.column_name
;