Constraints sem índices? Constraints without indexes?

Apresento aqui um script muito útil que mostra quais as constraints que não têm indices. Já sabem o que têm depois de fazer, não? Criar os indices respectivo! Porquê? optimização!

Here is a very usefull script that show the constraints that do not have indexes associated. You know what you need to do then, right? Create the indexes! Why? Optimization!



SELECT a.table_name, a.COLUMNS, b.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

actualização:script para criar os indices
update: script to create the indexes