Query to get Unindexed Foreign Keys
undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
column fk_columns format a30
column index_name format a30
column index_columns format a30
select
case
when i.index_name is not null then
'indexed'
else
'unindexed'
end as status
,c.table_name as table_name
,c.constraint_name as fk_name
,c.fk_columns as fk_columns
,i.index_name as index_name
,i.index_columns as index_columns
from
(
select a.table_name
,a.constraint_name
,listagg(b.column_name, ' ' )
within group (order by column_name) as fk_columns
from
dba_constraints a
,dba_cons_columns b
where
a.constraint_name = b.constraint_name
and a.constraint_type = 'R'
and a.owner = b.owner
and a.owner = '&&schema_owner'
group by
a.table_name
,a.constraint_name
) c
left outer join
(
select table_name
,index_name
,cr
,listagg(column_name, ' ')
within group (order by column_name) as index_columns
from
(
select
table_name
,index_name
,column_position
,column_name
,connect_by_root(column_name) cr
from
dba_ind_columns
where
index_owner = '&&schema_owner'
connect
by prior column_position-1 = column_position
and prior index_name = index_name
)
group by table_name
,index_name, cr
) i
on c.fk_columns = i.index_columns
and c.table_name = i.table_name;
SQL> undefine schema_owner
set pagesize 5000
set linesize 350
column status format a10
column table_name format a30
column fk_name format a30
SQL> SQL> SQL> SQL> SQL> SQL> column fk_columns format a30
column index_name format a30
column index_columns format a30
select
SQL> SQL> SQL> SQL> SQL> 2 case
when i.index_name is not null then
'indexed'
else
'unindexed'
end as status
3 4 5 6 7 8 ,c.table_name as table_name
,c.constraint_name as fk_name
,c.fk_columns as fk_columns
,i.index_name as index_name
,i.index_columns as index_columns
9 10 11 12 13 from
(
select a.table_name
,a.constraint_name
,listagg(b.column_name, ' ' )
14 15 16 17 18 within group (order by column_name) as fk_columns
from
dba_constraints a
19 20 21 ,dba_cons_columns b
where
a.constraint_name = b.constraint_name
and a.constraint_type = 'R'
22 23 24 25 and a.owner = b.owner
and a.owner = '&&schema_owner'
26 27 group by
28 a.table_name
,a.constraint_name
) c
29 30 31 left outer join
(
select table_name
,index_name
,cr
32 33 34 35 36 ,listagg(column_name, ' ')
within group (order by column_name) as index_columns
from
37 38 39 (
40 select
table_name
,index_name
,column_position
,column_name
,connect_by_root(column_name) cr
from
41 42 43 44 45 46 47 dba_ind_columns
where
index_owner = '&&schema_owner'
connect
by prior column_position-1 = column_position
and prior index_name = index_name
48 )
group by table_name
,index_name, cr
49 50 51 52 53 54 55 56 ) i
on c.fk_columns = i.index_columns
and c.table_name = i.table_name; 57 58
Enter value for schema_owner: HR
old 26: and a.owner = '&&schema_owner'
new 26: and a.owner = 'HR'
old 49: index_owner = '&&schema_owner'
new 49: index_owner = 'HR'
STATUS TABLE_NAME FK_NAME FK_COLUMNS INDEX_NAME IN DEX_COLUMNS
---------- ------------------------------ --------------- ------- ------------------------------ ---------------- --------------- --------------
indexed EMPLOYEES EMP_JOB_FK JOB_ID EMP_JOB_IX JO B_ID
indexed EMPLOYEES EMP_MANAGER_FK MANAGER_ID EMP_MANAGER_IX MA NAGER_ID
indexed EMPLOYEES EMP_DEPT_FK DEPARTMENT_ID EMP_DEPARTMENT_IX DE PARTMENT_ID
indexed LOCATIONS LOC_C_ID_FK COUNTRY_ID LOC_COUNTRY_IX CO UNTRY_ID
indexed DEPARTMENTS DEPT_LOC_FK LOCATION_ID DEPT_LOCATION_IX LO CATION_ID
indexed JOB_HISTORY JHIST_JOB_FK JOB_ID JHIST_JOB_IX JO B_ID
indexed JOB_HISTORY JHIST_EMP_FK EMPLOYEE_ID JHIST_EMPLOYEE_IX EM PLOYEE_ID
indexed JOB_HISTORY JHIST_DEPT_FK DEPARTMENT_ID JHIST_DEPARTMENT_IX DE PARTMENT_ID
indexed JOB_HISTORY JHIST_EMP_FK EMPLOYEE_ID JHIST_EMP_ID_ST_DATE_PK EM PLOYEE_ID
unindexed DEPARTMENTS DEPT_MGR_FK MANAGER_ID
unindexed COUNTRIES COUNTR_REG_FK REGION_ID
11 rows selected.
SQL>
