FK index

Still a little unclear what happened if we don't have an index on FK, when it cause full scan performance killer and any other disadvantage. Can you explain your reason with EMP, DEPT tables.

Thanks Farrokh

What version of Oracle are you using? Different versions have different problems with this particular issue.

- chris

We use 8.1.6 Or 8i

If you don't have an index on FK and you update (or anything that locks) the child table, Oracle will lock the whole parent table, bad isn't it?
If you create the index, Oracle will lock the index and it will be a shared lock, you are out of problems :)

Have FK index only if u are updating/deleting records through primary key.

Rcabelle's answer applied to 7.x.

In 8.x, gpsingh's answer is more correct. If you do *not* have an index on an FK field (in, obviously, the child table of a relationship), then:
- If you update the PK of the parent (which you should *never* do anyway), you will lock the child table
- If you delete from the parent, you will lock the the child table.

That is it.

However, that is certainly not the *only* reason to have an index on a FK, as gpsingh seems to suggest. Indexes have often been known to help performance as well :)

Here is a link for you:

http://oradoc.photo.net/ora8doc/DOC/...1/ch6.htm#1574

HTH,

- Chris

If primary key that migrated from parent table is a combined key ( more than one field) does the scenario changed? ANd if don't have delete and update on child no more reason to have index on FK ??

Whether the PK of the parent table is a single field or multiple fields is irrelevant to this particular issue.

Again, it is the updating and deleting of the *parent* record that is the issue here, not the child. You can do any DML you like on the child, with or without indexes, and you will never lock the parent table.

Now, if you never update the parent's PK and never delete from the parent, then you do not need indexes *to avoid this issue*. That certainly does not mean that you do not need any indexes for performance reasons. That is, after all, the primary usage for indexes. :)

- Chris

Thanks Chris but just confirm these issues at the end:

1. I don't remember any table in my work lifetime that we had update on Primary Key.

2. How strong is the locking, Nobody can even select from poor child table during for example delete from Parent ( If no index exist) ??

3. If we have 10 FK in a child table we need to have 10 indexes plus PK index total 11 or more. More than 5 indexes in any table does not recommend by experts. How do you handle this.

Regards

1. Absolutely true!! This should never happen in a properly designed database.

2. It is an exclusive table lock. Others can SELECT, but nobody else can do any DML on the child while the lock is in effect. Basically, this will kill the concurrency of an OLTP database!

3. First of all, 5 indexes is probably a good 'rule of thumb', but certainly not an absolute - there are lots of factors that contribute to this rule. The basic idea is that you don't want too many indexes or your DML will start to suffer. But this is just a tradeoff. If your database tends to be big, and has a lot more SELECTs than DML, with these SELECTS being large and complex, than you may want and *need* 15 indexes on key tables - everything is just a tradeoff of variables. This child-locking issue is just another variable :)

Some parameters first:

If the parent is a code table, then this should not be an issue. First, code tables are generally static data, with any updates occuring only during upgrades or during batch. Therefore, an index is not needed for the parent-locking reason. Further, since one does not *normally* move from the code table to the main table, an index is *generally* not needed for performance. For example, if you have a State table (assuming you're in US) - this is obviously a code table. If you never use the State_Cd field in the WHERE clause, then this field does not need to be indexed. *Even if it is* used in the WHERE clause, it may make sense to be part of a multi-key index.

NOTE: In order for an index to qualify as an 'index on the FK' such that it will *not* cause the child-locking issue, the left-leading columns in the index must match the FK. In other words, an index on (State_Cd, Gender) *would* cover the need for an index on the State_Cd FK. Was I clear with this?

Back to our example, since we do *not* need the index for the child-locking reason, we can create an index on (Gender, State_Cd) or the like - whatever we need.

With non-code tables, however, we must be careful. If we *never* delete from the parent, then we don't need an index for the child-locking issue. But performance is a different story. If we have a relationship with another, non-code table, it is quite likely that a query will move from that table to ours.

For example

SELECT * FROM EMP E, EMP_HIST EH WHERE E.EMP_SSN = :b1 AND EH.EMP_ID = E.EMP_ID

This will move from E to EH. Therefore, EH had better have an index on EMP_ID.

Generally, tables have few FKs to other main tables. *Most* FKs are to code tables, so many of these can be without indexes, or combined - based on the needs of the queries going against them. So it is usually not *too* difficult to pare the indexes down to a reasonable number.

So, theses are the factors and the tradeoffs. It is now up to you to craft the proper set of indexes to balance all the variables.

Hope this helps,

- Chris

Very comprehensive and helful Chris. Thanks a lot.