Given a parent-child relationship between two tables, such as:

table ORDER
    order_id PK (already indexed)

table ITEM
    item_id PK (already indexed)
    order_id (FK to ORDER.order_id)

I want to determine whether there would be any benefit (performance improvement to SELECT queries) in adding an index on ITEM.order_id.

What information should I gather that would help me determine this? (such as structure of the queries my application runs against these tables, etc.) I do know that my application regularly runs queries that perform a simple inner join between these two tables. Are there any general principles that apply to index tuning for this kind of scenario?

I am using Oracle 11g.

1 Answer 1


There are basically three situations where you would want to index the child in addition to the parent in an FK/PK relationship:

  1. If you ever select from ITEM based on order_id (obviously!)
  2. If you ever run queries on from the JOIN of ITEM and ORDERS with a filter on ORDERS
  3. As with 2, but with a filter on ITEM that is less selective than that on ORDERS

In both cases, ITEM will benefit from becoming the inner part of the join and an index from on the child will help support a loop join strategy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.