oralce数据库给所有没有索引的外键创建索引

select a.table_name,'create index idx_' || substr(a.table_name,1,20) || '_' ||
       ceil(dbms_random.value * 1000) || ' on ' || a.table_name || '( ' || a.columns  || ');'
  from (select substr(a.table_name, 1, 30) table_name,
               substr(a.constraint_name, 1, 30) constraint_name,
               max(decode(position, 1, substr(column_name, 1, 30), NULL)) ||
               max(decode(position,
                          2,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          3,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          4,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          5,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          6,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          7,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          8,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          9,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          10,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          11,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          12,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          13,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          14,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          15,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(position,
                          16,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) columns
          from user_cons_columns a, user_constraints b
         where a.constraint_name = b.constraint_name
           and b.constraint_type = 'R'
         group by substr(a.table_name, 1, 30),
                  substr(a.constraint_name, 1, 30)) a,
       (select substr(table_name, 1, 30) table_name,
               substr(index_name, 1, 30) index_name,
               max(decode(column_position,
                          1,
                          substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          2,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          3,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          4,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          5,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          6,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          7,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          8,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          9,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          10,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          11,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          12,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          13,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          14,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          15,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) ||
               max(decode(column_position,
                          16,
                          ', ' || substr(column_name, 1, 30),
                          NULL)) columns
          from user_ind_columns
         group by substr(table_name, 1, 30), substr(index_name, 1, 30)) b
 where a.table_name = b.table_name(+)
   and b.columns(+) like a.columns || '%'
   and b.table_name is null