Com uma pequena modificação, fiz uma coluna com o CREATE INDEX dos índices que faltam.
Selecionar tudo
select --decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, x.num_rows, --a.constraint_name, a.columns, b.columns,
'create index '||a.constraint_name||'_I on '||a.table_name||' ('||a.columns||');' cre_ind
from
user_tables x
,
( select a.table_name, a.constraint_name,
max(decode(position, 1, cname,NULL)) ||
max(decode(position, 2,', '||cname,NULL)) ||
max(decode(position, 3,', '||cname,NULL)) ||
max(decode(position, 4,', '||cname,NULL)) ||
max(decode(position, 5,', '||cname,NULL)) ||
max(decode(position, 6,', '||cname,NULL)) ||
max(decode(position, 7,', '||cname,NULL)) ||
max(decode(position, 8,', '||cname,NULL)) ||
max(decode(position, 9,', '||cname,NULL)) ||
max(decode(position,10,', '||cname,NULL)) ||
max(decode(position,11,', '||cname,NULL)) ||
max(decode(position,12,', '||cname,NULL)) ||
max(decode(position,13,', '||cname,NULL)) ||
max(decode(position,14,', '||cname,NULL)) ||
max(decode(position,15,', '||cname,NULL)) ||
max(decode(position,16,', '||cname,NULL)) columns
from (select substr(column_name,1,30) cname,
substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name ) a,
( select table_name, index_name,
max(decode(position, 1, cname,NULL)) ||
max(decode(position, 2,', '||cname,NULL)) ||
max(decode(position, 3,', '||cname,NULL)) ||
max(decode(position, 4,', '||cname,NULL)) ||
max(decode(position, 5,', '||cname,NULL)) ||
max(decode(position, 6,', '||cname,NULL)) ||
max(decode(position, 7,', '||cname,NULL)) ||
max(decode(position, 8,', '||cname,NULL)) ||
max(decode(position, 9,', '||cname,NULL)) ||
max(decode(position,10,', '||cname,NULL)) ||
max(decode(position,11,', '||cname,NULL)) ||
max(decode(position,12,', '||cname,NULL)) ||
max(decode(position,13,', '||cname,NULL)) ||
max(decode(position,14,', '||cname,NULL)) ||
max(decode(position,15,', '||cname,NULL)) ||
max(decode(position,16,', '||cname,NULL)) columns
from ( select substr( column_name, 1, 30 ) cname,
substr( table_name, 1, 30 ) table_name,
substr( index_name, 1, 30 ) index_name,
column_position position
from user_ind_columns ) user_ind_columns
group by table_name, index_name ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'
and a.table_name = x.table_name
order by
decode( b.table_name, NULL, '****', 'ok' )
, x.num_rows desc nulls last