fkindex.sql

-- 
-- Title :       fkindex.sql 
-- Description : Show missing foreign key indexes.
--               This script is a modified version of the one found on Oracle's
--               support website in article 16428.1.
-- 
-- Usage/Notes : Enter schema name or wild card variation when prompted.
--               Default is all schemas.
--               Requires read access to the DBA dictionary views.
-- 
-- Copyright :   ABCdba.com 2014 
-- 
 
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
 
ACCEPT schema PROMPT 'Enter schema name or press <Enter> for ALL  : ' DEFAULT '%';
 
SET ECHO OFF
SET SERVEROUTPUT ON FORMAT WRAPPED
 
DECLARE
   schema_name    VARCHAR2(30);
   pl_cons_column VARCHAR2(30);
   pl_foreign_key VARCHAR2(2000);
   pl_ind_column  VARCHAR2(30);
   pl_ind_name    VARCHAR2(30);
   pl_ind_owner   VARCHAR2(30);
   pl_index       VARCHAR2(2000);
   f_owner        VARCHAR2(30);
   f_table_name   VARCHAR2(30);
 
   CURSOR c1 IS
      SELECT
         constraint_name, owner, table_name, status, r_owner, r_constraint_name
      FROM
         dba_constraints
      WHERE
         constraint_type = 'R'
      AND
         owner LIKE UPPER(schema_name)
      ORDER BY
         owner;
 
   CURSOR c2 (cons_name VARCHAR2, cons_owner VARCHAR2) IS
      SELECT
         column_name
      FROM
         dba_cons_columns
      WHERE
         constraint_name = cons_name
      AND
         owner = cons_owner
      ORDER BY
         dba_cons_columns.position;
 
   CURSOR c3 (ind_table VARCHAR2, tab_owner VARCHAR2) IS
      SELECT
         index_name, owner
      FROM
         dba_indexes
      WHERE
         table_name = ind_table
      AND
         table_owner = tab_owner;
 
   CURSOR c4 (ind_name VARCHAR2, ind_owner VARCHAR2) IS
      SELECT
         column_name
      FROM
         dba_ind_columns
      WHERE
         index_name = ind_name
      AND
         index_owner = ind_owner
      ORDER BY
         dba_ind_columns.column_position;
 
   CURSOR c5 (for_owner VARCHAR2, for_constraint VARCHAR2) IS
      SELECT
         owner, table_name
      FROM
         dba_constraints
      WHERE
         owner = for_owner
      AND
         constraint_name = for_constraint;
 
BEGIN
 
schema_name:= '&schema';
 
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('                        Missing Indexes for Foreign Keys');
DBMS_OUTPUT.PUT_LINE('                        --------------------------------');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('************************');
 
FOR c1_rec IN c1
LOOP
 
   pl_cons_column := NULL;
   pl_foreign_key := NULL;
   pl_ind_column := NULL;
   pl_ind_name := NULL;
   pl_ind_owner := NULL;
   pl_index := NULL;
   f_owner:=NULL;
   F_table_name:=NULL;
 
   OPEN c5 (c1_rec.r_owner, c1_rec.r_constraint_name);
   FETCH c5 INTO f_owner, f_table_name;
   CLOSE c5;
 
   OPEN c2 (c1_rec.constraint_name, c1_rec.owner);
   FETCH c2 INTO pl_cons_column;
   pl_foreign_key := pl_cons_column;
 
<<constraint_names>>
   LOOP
      FETCH c2 into pl_cons_column;
      EXIT WHEN c2%NOTFOUND;
      pl_foreign_key := pl_foreign_key||','||pl_cons_column;
   END LOOP constraint_names;
 
   CLOSE c2;
 
   OPEN c3(c1_rec.table_name,c1_rec.owner);
 
<<index_name>>
   LOOP
      FETCH c3 INTO pl_ind_name, pl_ind_owner;
      EXIT WHEN c3%NOTFOUND;
      OPEN c4 (pl_ind_name, pl_ind_owner);
      FETCH c4 INTO pl_ind_column;
      pl_index := pl_ind_column;
 
      IF pl_index=pl_foreign_key
      THEN
         CLOSE c4;
         EXIT index_name;
      END IF;
 
      IF pl_index = SUBSTR(pl_foreign_key, 1, LENGTH(pl_index))
      THEN
 
<<index_columns>>
         LOOP
            FETCH c4 INTO pl_ind_column;
            EXIT WHEN c4%NOTFOUND;
            pl_index:= pl_index||','||pl_ind_column;
 
            IF pl_index=pl_foreign_key
            THEN
               CLOSE c4;
               EXIT index_name;
            END IF;
 
            IF pl_index != SUBSTR(pl_foreign_key, 1, LENGTH(pl_index))
            THEN
               EXIT index_columns;
            END IF;
         END LOOP index_columns;
      END IF;
      CLOSE c4;
   END LOOP index_name;
 
   CLOSE c3;
 
   IF pl_index != pl_foreign_key OR pl_index IS NULL
   THEN
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('Constraint               : '
         ||c1_rec.constraint_name||' ('||c1_rec.status||')');
      DBMS_OUTPUT.PUT_LINE('Changing data in table   : '
         ||f_owner||'.'||f_table_name);
      DBMS_OUTPUT.PUT_LINE('Would lock table         : '
         ||c1_rec.owner||'.'||c1_rec.table_name);
      DBMS_OUTPUT.PUT_LINE('Consider an index on     : '
         ||c1_rec.owner||'.'||c1_rec.table_name||' ('||pl_foreign_key||')');
      DBMS_OUTPUT.PUT_LINE(' ');
      DBMS_OUTPUT.PUT_LINE('************************');
   END IF;
 
END LOOP;
 
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
 
END;
/
 
SET HEADING ON
SET FEEDBACK ON
SET VERIFY ON

© copyright 2001-2014 ABCdba.com | all rights reserved