index on foreign key

[dinocwh]

got a conceptual question. say.

delete from child;
delete from parent;

why is it take =~ forever to delete the parent table if no index is built on the child's foreign key? like to know in details how are they work differently.
thx

[tamilselvan]

When there is no index built on FK in the child table, then Oracle has to scan the entire child table (or PK of child table) for deleting a row in the Parent Table. That is why when a FK is created, for the best performance we should have index on it.

[jmodic]

Let's say your child table contains 10.000.000 rows, foreign key to your parent table is not indexed. let's say your parent table contains 1000 records.

Now in your first step you do:
delete from child;

That's fine, 10M rows get deleted, so child table now contains 0 rows. However its highwatter mark has not changed, so for each full table scan on that table Oracle has to scan *a lot* of database blocks just to find out there is no rows inthere. So full scan on empty child table still takes exactly the same amount of time as it took before the deletion.

Now you do:
delete from parent;

As there is still FK from your child to parent, for each row to be deleted Oracle has to check your child table if there is any record that would violate FK after the deletion of the parent row. As the FK is not indexed it has to perform FTS on child.

So in short, it has to perform 1000 full table scans on child, and the fact that child is empty doesn't have any influence because HWM is still as high as when all the rows were still in child.

Your option to speed this deletion process is either:
a) truncate your child befor you delete from parent
b) have FK on child indexed

[julian]

For the sake of completion of the toipic started, let's add also that starting 9i, if a foreign key column is unindexed, then altough the share lock (at table level) is still needed (when say deleting on the PK for instance), the lock is released at once after obtaining it.

If someone is curious about why is this shared lock needed, the answer is: only for checking wheather there are any pending transactions against any of the rows.

[dinocwh]

cool~~~ is it the same case for update?
say...
update child
set (all columns except pk/fk) = (new values);
update parent
set (all columns except pk) = (new values);

[jmodic]

Originally posted by dinocwh
cool~~~ is it the same case for update?
say...
update child
set (all columns except pk/fk) = (new values);
update parent
set (all columns except pk) = (new values);

No, for this kind of updates Oracle doesn't have to check any referential integrity, so having an index on FK or not doesn't make any difference in this case. Both the above updates are performed as the tables are not related via foreign key because you don't update FK/PK columns.