Foreign Key Indexes

accept s_schema prompt 'Schema : ' declare type r_data is record ( name varchar2( 30 ), definition varchar2( 1000 ), used varchar2(1) ); type t_data is table of r_data index by binary_integer; li_counter number := 0; cursor csr_tables is select * from dba_tables tbl where owner = upper( '&&s_schema' ) and exists ( select 1 from dba_constraints where table_name = tbl.table_name and constraint_type = 'R' ) order by table_name; cursor csr_fks( x_table_name in dba_tables.table_name%type ) is select * from dba_constraints where owner = upper( '&&s_schema' ) and table_name = x_table_name and constraint_type = 'R' order by table_name; cursor csr_fk_cols ( x_owner in dba_constraints.owner%type, x_table_name in dba_tables.table_name%type, x_cons_name in dba_constraints.constraint_name%type ) is select * from dba_cons_columns where owner = x_owner and table_name = x_table_name and constraint_name = x_cons_name order by position; cursor csr_tbl_inds ( x_t_owner in dba_indexes.table_owner%type, x_table_name in dba_tables.table_name%type ) is select * from dba_indexes where table_owner = x_t_owner and table_name = x_table_name; cursor csr_ind_cols( x_i_owner in dba_indexes.owner%type, x_index in dba_indexes.index_name%type ) is select * from dba_ind_columns where index_owner = x_i_owner and index_name = x_index order by column_position; ls_fk_output varchar2( 2000 ); ls_ix_output varchar2( 2000 ); ls_fk_cols varchar2( 2000 ); ls_ix_cols varchar2( 2000 ); begin for i_csr_tables in csr_tables loop declare fk_data t_data; ix_data t_data; lb_match_found boolean := false; begin dbms_output.put_line( 'Table : ' || i_csr_tables.table_name ); dbms_output.put_line( '--------' || rpad( '-', length( i_csr_tables.table_name ), '-' ) ); li_counter := 1; for i_csr_fks in csr_fks( i_csr_tables.table_name ) loop ls_fk_output := 'FK : ' || rpad( i_csr_fks.constraint_name, 30 ); ls_fk_cols := ''; fk_data( li_counter ).name := i_csr_fks.constraint_name; for i_csr_fk_cols in csr_fk_cols( i_csr_fks.owner, i_csr_fks.table_name, i_csr_fks.constraint_name ) loop ls_fk_output := ls_fk_output || ' ' || i_csr_fk_cols.column_name; ls_fk_cols := ls_fk_cols || ' ' || i_csr_fk_cols.column_name; end loop; -- dbms_output.put_line( ls_fk_output ); fk_data( li_counter ).definition := ls_fk_cols; li_counter := li_counter + 1; end loop; li_counter := 1; for i_csr_tbl_inds in csr_tbl_inds( i_csr_tables.owner, i_csr_tables.table_name ) loop ls_ix_output := 'IX : ' || rpad( i_csr_tbl_inds.index_name, 30 ); ls_ix_cols := ''; ix_data( li_counter ).name := i_csr_tbl_inds.index_name; for i_csr_ind_cols in csr_ind_cols( i_csr_tbl_inds.owner, i_csr_tbl_inds.index_name ) loop ls_ix_output := ls_ix_output || ' ' || i_csr_ind_cols.column_name; ls_ix_cols := ls_ix_cols || ' ' || i_csr_ind_cols.column_name; end loop; -- dbms_output.put_line( ls_ix_output ); ix_data( li_counter ).definition := ls_ix_cols; ix_data( li_counter ).used := 'N'; li_counter := li_counter + 1; end loop; for i in 1..fk_data.count() loop lb_match_found := false; dbms_output.put_line( 'FK : ' || rpad( fk_data( i ).name, 30 ) || chr(9) || fk_data( i ).definition ); for j in 1..ix_data.count() loop if substr( ix_data( j ).definition, 1, length( fk_data( i ).definition ) ) = fk_data( i ).definition then dbms_output.put_line( chr(9) || 'IX : ' || rpad( ix_data( j ).name, 30 ) || ix_data( j ).definition ); ix_data( j ).used := 'Y'; lb_match_found := true; end if; end loop; if not lb_match_found then dbms_output.put_line( '* No matching index *' ); end if; dbms_output.put_line( ' ' ); end loop; dbms_output.put_line( ' ' ); dbms_output.put_line( 'Other indexes :' ); for i in 1..ix_data.count() loop if ix_data( i ).used = 'N' then dbms_output.put_line( 'IX : ' || rpad( ix_data( i ).name, 30 ) || ix_data( i ).definition ); end if; end loop; end; dbms_output.put_line( chr(9) ); end loop; end; /

set linesize 500

col OWNER	  for a20
col TABLE_NAME for a20
col INDEX_NAME  for a20
col CONSTRAINT_NAME for a20	 
col COLS for a30	 


define schema='SCOTT'

 SELECT CASE
            WHEN ind.index_name IS NOT NULL
            THEN
               CASE
                  WHEN ind.index_type IN ('BITMAP') THEN '** Bitmp idx **'
                  ELSE 'indexed'
               END
            ELSE
               '** Check idx **'
         END
            checker,
         ind.index_type,
         cons.owner,
         cons.table_name,
         ind.index_name,
         cons.constraint_name,
         cons.cols
    FROM (  SELECT c.owner,
                   c.table_name,
                   c.constraint_name,
                   LISTAGG (cc.column_name, ',')
                      WITHIN GROUP (ORDER BY cc.column_name)
                      cols
              FROM dba_constraints c, dba_cons_columns cc
             WHERE     c.owner = cc.owner
                   AND c.owner = UPPER ('&schema')
                   AND c.constraint_name = cc.constraint_name
                   AND c.constraint_type = 'R'
          GROUP BY c.owner, c.table_name, c.constraint_name) cons
         LEFT OUTER JOIN
         (  SELECT table_owner,
                   table_name,
                   index_name,
                   index_type,
                   cbr,
                   LISTAGG (column_name, ',') WITHIN GROUP (ORDER BY column_name)
                      cols
              FROM (    SELECT ic.table_owner,
                               ic.table_name,
                               ic.index_name,
                               ic.column_name,
                               ic.column_position,
                               i.index_type,
                               CONNECT_BY_ROOT (ic.column_name) cbr
                          FROM dba_ind_columns ic, dba_indexes i
                         WHERE     ic.table_owner = UPPER ('&schema')
                               AND ic.table_owner = i.table_owner
                               AND ic.table_name = i.table_name
                               AND ic.index_name = i.index_name
                    CONNECT BY     PRIOR ic.column_position - 1 =    ic.column_position
                               AND PRIOR ic.index_name = ic.index_name)
          GROUP BY table_owner,
                   table_name,
                   index_name,
                   index_type,
                   cbr) ind
            ON     cons.cols = ind.cols
               AND cons.table_name = ind.table_name
               AND cons.owner = ind.table_owner
ORDER BY checker, cons.owner, cons.table_name;

     

CHECKER         INDEX_TYPE                  OWNER                TABLE_NAME           INDEX_NAME           CONSTRAINT_NAME      COLS
--------------- --------------------------- -------------------- -------------------- -------------------- -------------------- ------------------------------
indexed         NORMAL                      SCOTT                EMP                  IX_DEPTNO            FK_DEPTNO            DEPTNO