How to determine efficacy of index on child side of foreign key Given a parent-child relationship between two tables, such as:

table ORDER
    order_id PK (already indexed)
    order_name

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


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.
There are basically three situations where you would want to index the child in addition to the parent in an FK/PK relationship:


If you ever select from ITEM based on order_id (obviously!)
If you ever run queries on from the JOIN of ITEM and ORDERS with a filter on ORDERS
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.