In various places I have seen mentioned the importance of indexing
foreign keys to avoid table locks on the child table on parent
update/delete and full table scans when the constraint has action ON
DELETE CASCADE. For example, see
http://asktom.oracle.com/pls/ask/f?p...A:292016138754,
However, if I have no index on the FKs, the only mention of full table
scans on the child table is when the constraint specifies ON DELETE
CASCADE. But what happens if the RI constraint is set to the default
NO
ACTION, meaning that it inhibits any operation that would create
orphans? If I delete a parent table row, and in the absence of an
index, doesn't it then have to perform a full table scan on the child
table to make sure there are no references to the parent row?
An EXPLAIN of such a query doesn't seem to show any such scan.
If it doesn't perform a scan, how can it verify that there are no
references and it's safe to delete the parent row? Is there some kind
of internal reference count per parent row that is updated when any
child row is inserted or deleted?
-- Carlos A. Ibarra
foreign keys to avoid table locks on the child table on parent
update/delete and full table scans when the constraint has action ON
DELETE CASCADE. For example, see
http://asktom.oracle.com/pls/ask/f?p...A:292016138754,
However, if I have no index on the FKs, the only mention of full table
scans on the child table is when the constraint specifies ON DELETE
CASCADE. But what happens if the RI constraint is set to the default
NO
ACTION, meaning that it inhibits any operation that would create
orphans? If I delete a parent table row, and in the absence of an
index, doesn't it then have to perform a full table scan on the child
table to make sure there are no references to the parent row?
An EXPLAIN of such a query doesn't seem to show any such scan.
If it doesn't perform a scan, how can it verify that there are no
references and it's safe to delete the parent row? Is there some kind
of internal reference count per parent row that is updated when any
child row is inserted or deleted?
-- Carlos A. Ibarra