Lets do TM lock and find out what object should be indexed
create table tt ( id number ,fld varchar2(50) );
create table ttt ( id number ,id_tt number );
alter table tt add constraint pk_id primary key (id) ;
alter table ttt add foreign key ( id_tt ) references tt (id);
insert into tt select level ,rpad ('x',40) from dual connect by level <=100;
commit;
sess 1
insert into ttt values ( 1,1);
sess 2
delete from tt where id=1;
I use Tanel Poder sripts in this example
this one ( ashtop.sql )
and this one
— Script by Tanel Poder (http://www.tanelpoder.com)
—
— Look up object info by object id
col o_owner heading owner for a25
col o_object_name heading object_name for a30
col o_object_type heading object_type for a18
col o_status heading status for a9
select
owner o_owner,
object_name o_object_name,
object_type o_object_type,
subobject_name,
created,
last_ddl_time,
status o_status,
data_object_id
from
dba_objects
where
object_id in (&1)
order by
o_object_name,
o_owner,
o_object_type
result :
or use this scipt to find nonindexed foreing keys:
select table_name, column_name
from ( select c.table_name, cc.column_name, cc.position column_position
from all_constraints c, all_cons_columns cc
where c.constraint_name = cc.constraint_name
and c.constraint_type = ‘R’ and c.owner=cc.owner and c.owner=’&1′
minus
select i.table_name, ic.column_name, ic.column_position
from all_indexes i, all_ind_columns ic
where i.index_name = ic.index_name and i.owner=ic.table_owner and i.owner=’&1′);
18:13:51 (1)c##bushmelev_aa@u10pgp> @fk_no_indx C##BUSHMELEV_AA TABLE_NAME | COLUMN_NAME ---------- | --------------- TTT | ID_TT