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. 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, 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?

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