Check whether the foreign key column is the two statements of the index column.

Check whether the foreign key column is the two statements of the index column.

This article is taken from page 37th and page 38th of oracle index technology.

 

Check whether the foreign key column is one of the index column statements:

select distincta.owner  owner,a.constraint_name cons_name,a.table_name tab_name,b.column_name  cons_column,nvl(c.column_name,'***Check index***') ind_columnfrom dba_constraints a,     dba_cons_columns b,     dba_ind_columns cwhere constraint_type='R'and   a.owner=upper('&user_name')and   a.owner=b.ownerand   a.constraint_name=b.constraint_nameand   b.column_name=c.column_name(+)and   b.table_name=c.table_name(+)and   b.position=c.column_position(+)order by tab_name, ind_column;

The preceding query statement does not correctly report unindexed Foreign keys in all cases.
For example, when multiple foreign keys exist, it doesn't matter if you define the constraints in order different from the index column, as long as the index column is in front of the index.

 

Check whether the foreign key column is the second statement of the index column: -- use the listparts analysis function (this function is a new feature of 11g)

select case when ind.index_name is not null then  case when ind.index_type in ('BITMAP') then    '** Bitmp idx **'  else    'indexed'  endelse  '** Check idx **'end checker,ind.index_type,cons.owner,cons.table_name, ind.index_name,cons.constraint_name,cons.colsfrom (select c.owner,c.table_name,c.constraint_name,listagg(cc.column_name,',') within group (order by cc.column_name) colsfrom dba_constraints c, dba_cons_columns ccwhere c.owner=cc.ownerand c.owner=upper('&schema')and c.constraint_name=cc.constraint_nameand c.constraint_type='R'group by c.owner,c.table_name,c.constraint_name) consleft 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 ) indon cons.cols=ind.colsand cons.table_name=ind.table_nameand cons.owner=ind.table_ownerorder by checker,cons.owner,cons.table_name;

Create the following test table:

 create table TEST_USER.students(stud_id number constraint pk_stud_id primary key,lname varchar2(40),fname varchar2(40));    create table TEST_USER.attendees  (stud_id number,  class_id number,  constraint pk_attendees primary key (stud_id,class_id)   );      alter table TEST_USER.attendees add constraint fk_stud foreign key (stud_id) references TEST_USER.students(stud_id);

 

Execute the first script and output the following:

 

Execute the Second Script and output the following: