drop table childORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
drop table parentORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table parent (
id number(4),
name varchar2(10),
constraint par_pk primary key (id)
deferrable initially immediate
) Table created.
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)
) Table created.
insert into parent values (1,'Smith')1 row(s) inserted.
insert into parent values (2,'Jones')1 row(s) inserted.
insert into child values(1,1,'Simon')1 row(s) inserted.
insert into child values(1,2,'Sally')1 row(s) inserted.
insert into child values(2,1,'Jack')1 row(s) inserted.
insert into child values(2,2,'Jill')1 row(s) inserted.
commitStatement processed.
begin
dbms_stats.gather_table_stats(null,'child');
dbms_stats.gather_table_stats(null,'parent');
end; Statement processed.
select
chi.*
from
child chi,
parent par
where
par.id = chi.id_p | ID_P | ID | NAME | 1 | 1 | Simon | 1 | 2 | Sally | 2 | 1 | Jack | 2 | 2 | Jill |
|---|
set constraint par_pk deferredStatement processed.
insert into parent (id,name) values (1,'Smith')1 row(s) inserted.
insert into parent (id,name) values (2,'Jones')1 row(s) inserted.
select
/*+ deferref PK */
chi.*
from
child chi,
parent par
where
par.id = chi.id_p | ID_P | ID | NAME | 1 | 1 | Simon | 1 | 2 | Sally | 2 | 1 | Jack | 2 | 2 | Jill |
|---|
select /*+ no_eliminate_join(@sel$1 par@sel$1) */
chi.*
from
child chi,
parent par
where
par.id = chi.id_p | ID_P | ID | NAME | 1 | 1 | Simon | 1 | 1 | Simon | 1 | 2 | Sally | 1 | 2 | Sally | 2 | 1 | Jack | 2 | 1 | Jack | 2 | 2 | Jill | 2 | 2 | Jill |
|---|