/* 
Create index for foreign keys
Created by : Shahid Hafeez
Email : shafeez67@yahoo.com
*/

select 'CREATE BITMAP INDEX '||uc.constraint_name ||' ON '||t.table_name||'('||t.column_name||')'
--,t.table_name, t.column_name, uc.constraint_type
from user_tables ut, user_tab_columns t, user_cons_columns col, user_constraints uc
where ut.table_name = t.table_name
and (t.table_name = col.table_name(+) and t.COLUMN_NAME = col.COLUMN_NAME(+))
and col.constraint_name = uc.constraint_name(+)
and decode(uc.constraint_type,null,'R',uc.constraint_type) <> 'P'

order by t.table_name,t.column_name