lundi 29 décembre 2014

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.





Aucun commentaire:

Enregistrer un commentaire