Script - Foreign keys sin Indexar

Consulta para localizar FKs sin indexar en nuestra bbdd, utiliza la función transponer (http://www.soportedba.com/2011/01/script-trasponerfilasacolumnasql.html), (se debe modificar para incluir la directiva "with authid current_user").

Con la columna "INDICE_A_CREAR" obtenemos la propuesta del índice a crear (que habrá que revisar para incluir opciones de almacenamiento u otras características).

COLUMN INDICE_A_CREAR FORMAT A200

SELECT DISTINCT FK.OWNER,FK.TABLE_NAME, FK.CONSTRAINT_NAME, 'CREATE INDEX '||FK.OWNER||'.'||FK.TABLE_NAME||'_'||ROWNUM||'_I ON '||FK.OWNER||'.'||FK.TABLE_NAME||'('||TRANSPONER('SELECT COLUMN_NAME FROM DBA_CONS_COLUMNS WHERE OWNER='||CHR(39)||FK.OWNER||CHR(39)||' AND CONSTRAINT_NAME='||CHR(39)||FK.CONSTRAINT_NAME||CHR(39)||' ORDER BY POSITION',',')||')' INDICE_A_CREAR
FROM
    (select DC.OWNER, DC.TABLE_NAME, DC.constraint_name, DCC.COLUMN_NAME, DCC.POSITION
    FROM
        DBA_CONSTRAINTS DC,
        DBA_CONS_COLUMNS DCC
    WHERE
        DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME AND
        DC.OWNER=DCC.OWNER AND
        DC.CONSTRAINT_TYPE='R') FK,
    (SELECT DI.OWNER, DI.TABLE_NAME, DI.INDEX_NAME, DIC.COLUMN_NAME, DIC.COLUMN_POSITION   
    FROM
        DBA_INDEXES DI,
        DBA_IND_COLUMNS DIC
    WHERE
        DI.INDEX_NAME=DIC.INDEX_NAME AND
        DI.OWNER=DIC.INDEX_OWNER ) IND
WHERE
    FK.COLUMN_NAME=IND.COLUMN_NAME(+) AND
    FK.POSITION=IND.COLUMN_POSITION(+) AND
    FK.OWNER=IND.OWNER(+) AND
    FK.TABLE_NAME=IND.TABLE_NAME(+) AND
    IND.TABLE_NAME IS NULL AND
    FK.OWNER NOT IN ('SYS','SYSTEM','ORACLE_OCM','DIP','TSMSYS','SYSMAN','DBSNMP','WMSYS','EXFSYS')
ORDER BY 1,2,3
/