Oracle diary - Apr27 2011

Use PRowID to see table organization, LROWID not

 

The database is my Virtual Machine. If I can do it in the database, I will. --TomAS Kyte

 

Index the Fkey, Otherwise :

 

- If I update the parent tableʹs primary key (a very rare occurrence if you follow the
rules of relational databases that primary keys should be immutable), the child
table will be locked in the absence of an index.

- If I delete a parent table row, the entire child table will be locked (in the absence of
an index) as well.

 

- When you have an ON DELETE CASCADE and have not indexed the child table.
For example, EMP is child of DEPT. DELETE DEPTNO = 10 should CASCADE to
EMP. If DEPTNO in EMP is not indexed, you will get a full table scan of EMP. This
full scan is probably undesirable, and if you delete many rows from the parent table,
the child table will be scanned once for each parent row deleted.

- When you query from the parent to the child. Consider the EMP/DEPT example
again. It is very common to query the EMP table in the context of a DEPTNO. If you
frequently run the following query, say to generate a report, youʹll find that not
having the index in place will slow down the queries:
       select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;

 

So, when do you not need to index a foreign key? The answer is, in general, when the
following conditions are met:
- You do not delete from the parent table.
- You do not update the parent tableʹs unique/primary key value (watch for
unintended updates to the primary key by tools!
- You do not join from the parent to the child (like DEPT to EMP)
If you satisfy all three above, feel free to skip the index ‐ it is not needed. If you do any of
the above, be aware of the consequences. This is the one very rare time when Oracle tends
to ʹover‐lockʹ data.