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

小咪咪 2022-12-24 03:00 123阅读 0赞

# 整体查看外键是否有索引 #

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
    ;