REM fk_index_check.sql
REM 19.10.2007
REM uncomment the following section to produce a test case that should buld two indexes
/*--------------------------------------------------------------------------------*/
ALTER TABLE EMP_TAB DROP CONSTRAINT MGR_FKEY;
ALTER TABLE EMP_TAB DROP CONSTRAINT DEPT_FKEY;
DROP TABLE Emp_tab; 
DROP TABLE DEPT_TAB; 

CREATE TABLE Dept_tab (
    SETID   NUMBER(3)
,    Deptno  NUMBER(3) 
,    Dname   VARCHAR2(15)
,    Loc     VARCHAR2(15)
--                CONSTRAINT Dname_ukey UNIQUE (Dname, Loc), 
--                CONSTRAINT Loc_check1 
--                  CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))
,   CONSTRAINT Dept_pkey PRIMARY KEY (setid,deptno)
)
/
CREATE TABLE Emp_tab ( 
    Empno    NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, 
    Ename    VARCHAR2(15) NOT NULL, 
    Job      VARCHAR2(10), 
    Mgr      NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab,
    Hiredate DATE, 
    Sal      NUMBER(7,2), 
    Comm     NUMBER(5,2), 
    setid    number(3),
    Deptno   NUMBER(3) NOT NULL,
    CONSTRAINT dept_fkey FOREIGN KEY (setid,deptno) REFERENCES Dept_tab (setid,deptno) ON DELETE CASCADE
)
/
/*--------------------------------------------------------------------------------*/
set serveroutput on buffer 1000000000 
REM gfc_fk_index_check is a working storage table that will be used in this script
DROP TABLE gfc_fk_index_check
/
CREATE TABLE gfc_fk_index_check
(owner			VARCHAR2(30)	NOT NULL
,table_name		VARCHAR2(30)	NOT NULL
,constraint_name	VARCHAR2(30)	NOT NULL
,r_owner		VARCHAR2(30)	NOT NULL
,r_table_name		VARCHAR2(30)	NOT NULL
,r_constraint_name	VARCHAR2(30)	NOT NULL
,i_index_owner		VARCHAR2(30)	
,i_index_name		VARCHAR2(30)	
,i_status		VARCHAR2(30)	DEFAULT 'UNKNOWN'
,i_column_list		VARCHAR2(300)
,CONSTRAINT gfc_fk_index_check_pk PRIMARY KEY(table_name, constraint_name)
)
/
TRUNCATE TABLE gfc_fk_index_check
/
--------------------------------------------------------------------------------
REM populate ws table with all the referential integrity constraints that reference a primary key
INSERT INTO gfc_fk_index_check
(owner, table_name, constraint_name
,r_owner, r_constraint_name, r_table_name)
SELECT	c.owner, c.table_name, c.constraint_name
,	c.r_owner, c.r_constraint_name
,	r.table_name r_table_name
FROM	all_constraints c
,	all_constraints r
WHERE	c.constraint_Type = 'R'
AND	r.owner = c.r_owner
AND	r.constraint_name = c.r_constraint_name
AND	r.constraint_Type = 'P'
AND	r.owner = user
/
--------------------------------------------------------------------------------
REM This PL/SQL routine checks each foreign key constraint in the table 
REM for each constraint it looks up the referring columns in all_cons_columns
REM and builds a dynamic query that SELECTs the owner and name of an index 
REM with the same columns in the same position.  The name of that index and 
REM the column list is stored on the working storage table.  Depending upon 
REM how many rows that query returns, a status string is written to the 
REM table: No Index/Index Found/Multiple Indexes
DECLARE
 l_counter NUMBER;
 l_column_list VARCHAR2(200);
 l_sql1 VARCHAR2(4000);
 l_sql2 VARCHAR2(4000);
 l_tmp1 VARCHAR2(20);
 l_tmp2 VARCHAR2(20);
 l_alias VARCHAR2(3);
 l_oldalias VARCHAR2(3);
 l_index_owner VARCHAR2(30);
 l_index_name VARCHAR2(30);
 l_status     VARCHAR2(30);
BEGIN
 FOR a IN (SELECT * FROM gfc_fk_index_check) LOOP
  l_counter := 0;
  l_column_list := '';
  l_sql1 := 'SELECT i1.index_owner, i1.index_name';
  l_sql2 := '';
  FOR b IN (SELECT * 
            FROM  all_cons_columns c
            WHERE c.owner = a.owner 
            AND   c.constraint_name = a.constraint_name
            AND   c.table_name = a.table_name
            ORDER BY position) LOOP
    l_counter := l_counter + 1;
    l_oldalias := l_alias;
    l_alias := ' i'||TO_CHAR(l_counter);
    IF l_counter > 1 THEN
     l_sql1 := l_sql1 ||', ';       
     l_sql2 := l_sql2 ||' AND' || l_oldalias || '.index_owner ='|| l_alias || '.index_owner'
                      ||' AND' || l_oldalias || '.index_name =' || l_alias || '.index_name'
                      ||' AND';
     l_column_list := l_column_list || ',';
    ELSE
     l_sql1 := l_sql1 ||' FROM ';
     l_sql2 := l_sql2 ||' WHERE';
    END IF;
    l_sql1 := l_sql1 || 'all_ind_columns' || l_alias;
    l_sql2 := l_sql2 || l_alias || '.TABLE_OWNER =''' || b.owner || ''''
           || ' AND' || l_alias || '.TABLE_NAME =''' || b.table_name || ''''
           || ' AND' || l_alias || '.COLUMN_NAME =''' || b.column_name || ''''
           || ' AND' || l_alias || '.COLUMN_POSITION =''' || b.position || '''';
    l_column_list := l_column_list || b.column_name;
   END LOOP;
--   dbms_output.put_line(l_sql1);
--   dbms_output.put_line(l_sql2);
--   dbms_output.put_line(l_column_list);
   l_status := a.i_status;
   l_index_owner := '';
   l_index_name := '';
   BEGIN
    EXECUTE IMMEDIATE l_sql1||l_sql2 INTO l_index_owner, l_index_name;
    l_status := 'Index Found';
   EXCEPTION
    WHEN NO_DATA_FOUND THEN l_status := 'No Index';
    WHEN TOO_MANY_ROWS THEN l_status := 'Multiple Indexes';
   END;
   UPDATE gfc_fk_index_check
   SET    i_status = l_status
   ,      i_index_owner = l_index_owner
   ,      i_indeX_name  = l_index_name
   ,      i_column_list = l_column_list
   WHERE  owner = a.owner
   AND    table_name = a.table_name
   AND    constraint_name = a.constraint_name;
 END LOOP;
 COMMIT;
END;
/
--------------------------------------------------------------------------------
REM report on test results

set lines 90 head on feedback on echo on
column owner             format a20
column table_name        format a30
column constraint_name   format a30
column r_owner           format a20
column r_constraint_name format a30
column r_table_name      format a30
column i_index_owner     format a20
column i_index_name      format a30
column i_status          format a30
column i_column_list     format a80
spool fk_index_check
SELECT g.owner, g.table_name, g.constraint_name
,      g.r_owner, g.r_table_name, g.r_constraint_name
,      g.i_index_owner, g.i_index_name, g.i_status
,      g.i_column_list
FROM   gfc_fk_index_check g
/
spool off
--------------------------------------------------------------------------------
REM this is a report of just largest tables that lack indexes on FK constraints.
REM It show tables more than 10000 rows (according to the CBO statistics), or at 
REM least the top 20.  These are likely to be most severe offencers.
spool fk_index_by_size
SELECT * from (
SELECT g.owner, g.table_name, g.constraint_name
,      g.r_owner, g.r_table_name, g.r_constraint_name
,      g.i_index_owner, g.i_index_name, g.i_status
,      /*t.temporary, t.partitioned, */ t.num_rows
,      g.i_column_list
FROM   gfc_fk_index_check g, all_tables t
WHERE  t.table_name = g.table_name
AND    t.owner = g.owner
AND    g.i_status = 'No Index'
ORDER BY num_rows desc
) WHERE rownum <= 20 or num_rows >= 10000
/
spool off
--------------------------------------------------------------------------------
REM This query generates a script containt create index DDL statements that will 
REM build the missing indexes.  The index will have the same name as the foreign 
REM key constraint to which it relates.
set head off trimout on trimspool on feedback off verify off timi off echo off lines 200
spool fk_index_build.sql
SELECT 'CREATE INDEX '||g.owner||'.'||g.constraint_name
       ||' ON '||g.owner||'.'||g.table_name||' ('||g.i_column_list||');' build_indexes
FROM   gfc_fk_index_check g, all_tables t
WHERE  t.table_name = g.table_name
AND    t.owner = g.owner
AND    g.i_status = 'No Index'
ORDER BY t.num_rows
/
spool off
set lines 90 head on feedback on echo on