drop table child

drop table parent

create table parent ( 
        id      number(4), 
        name    varchar2(10), 
        constraint par_pk primary key (id) 
        deferrable initially immediate 
) 

create table child( 
        id_p    number(4)        
                constraint chi_fk_par 
                references parent, 
        id      number(4), 
        name    varchar2(10), 
        constraint chi_pk primary key (id_p, id)  
) 

insert into parent values (1,'Smith')

insert into parent values (2,'Jones')

insert into child values(1,1,'Simon')

insert into child values(1,2,'Sally')

insert into child values(2,1,'Jack')

insert into child values(2,2,'Jill')

commit

begin 
        dbms_stats.gather_table_stats(null,'child'); 
        dbms_stats.gather_table_stats(null,'parent'); 
end; 

select 
        chi.* 
from 
        child   chi, 
        parent  par 
where 
        par.id = chi.id_p 

set constraint par_pk deferred

insert into parent (id,name) values (1,'Smith')

insert into parent (id,name) values (2,'Jones')

select 
        /*+ deferref PK */ 
        chi.* 
from 
        child   chi, 
        parent  par 
where 
        par.id = chi.id_p 

select  /*+ no_eliminate_join(@sel$1 par@sel$1) */ 
        chi.* 
from 
        child   chi, 
        parent  par 
where 
        par.id = chi.id_p