Constraints sem índices? (2) Constraints without indexes (2)

Num post anterior mostrei como descobrir os indices em falta para as contraints existentes. Apresento agora um script que faz o script a correr para criar os indices em falta (alterar o nome do tablespace para os indices para o caso respectivo).

In a previous post I showed how to find the FK contraints that didn't have an index. Today I present you will a script to create in missing indexes (change the name of tablespace for the indexes for your schema).

SELECT    ' create index '
       || constraint_name
       || ' on '
       || a.table_name
       || ' ('
       || a.columns
       || ') tablespace indx;'
  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;