|
 |
|
Oracle Tips by Burleson |
Indexing Foreign Keys
We talked about foreign keys and how they enforced
a parent child relationship. One problem with foreign keys is that
when you UPDATE/INSERT to the child column, you must scan the parent column to
validate the key. If the parent is not a primary key, this
can result in constantly reading the entire table to validate the
child value. Placing an index on the parent column will allow the
foreign key to validate the child with the index, avoiding the costly
full table scan.
When you start designing your database, you need
to develop a naming scheme. This will allow you to determine the
function of objects in the database. Tables are pretty easy, but
indexes are normally left out. You should name each object so that
you know what it does. The emp_job_fdx index could be the
index on the emp_job_fk constraint. A primary key index
could be name_pk, a unique index name_udx.
Sometimes, you can find that the database over
indexes, causing performance problems on INSERTs and UPDATEs. You
must determine which indexes you can remove to improve performance.
If you unknowingly delete an index on a foreign key, the performance
impact may be drastic. By using a logical naming convention, you will
know by the object name why you placed it into the database in the
first place.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
