What is the meaning of Oracle's hint "restrict_all_ref_cons"?
I found it in a statement like this:
delete /*+ restrict_all_ref_cons */ from table_1 where ...
Can anyone give some piece of information for what the hint is doing?
The database is an Oracle database 10.2.0.3.0.
The hint disables cascade deletion, so child rows will not be deleted from a child table when deleting from the parent.
See example here;
create table s11 ( x int primary key ); create table s12 ( y int primary key, x references s11 on delete cascade ); insert into s11 values (1); insert into s12 values (1, 1); commit; SQL> delete from s11; 1 row deleted. SQL> select * from s12; no rows selected <=========== when deleting parent row in s11, the child row in s12 is also deleted. SQL> rollback; Rollback complete. SQL> delete /*+ RESTRICT_ALL_REF_CONS */ from s11; 1 row deleted. SQL> select * from s12; <=========== with RESTRICT_ALL_REF_CONS hint, the child row will not be deleted. Y X ---------- ---------- 1 1