外键索引检查的SQL

通过以下语句 为无索引的外键生成 建立索引的SQL

-- 查询没有建立索引的的外键字段 并生成 创建索引语句
select lower('create index ' || 'idx_' || x.c || ' on ' || x.t || '(' || x.c || ');') sqls
  from (

        select c.table_name  t,
                c.column_name c
          from user_cons_columns c,
                user_constraints  uc

         where c.table_name = uc.table_name
           and c.constraint_name = uc.constraint_name
           and uc.constraint_type = 'R'
           and uc.status = 'ENABLED'
        minus
        select ui.table_name  t,
               ui.column_name c
          from user_ind_columns ui) x;
点赞