Forum Stats

  • 3,781,630 Users
  • 2,254,532 Discussions
  • 7,879,769 Comments

Discussions

Foreign key and index

614497
614497 Member Posts: 209
edited Nov 11, 2009 9:45AM in General Database Discussions
Hi all,

My db is 10.2.0.3 RAC db with 2 nodes on MS window 2003 servers. My question is related to foreign keys and indexes. I have learned that we need to create an index for each foreign key in order to avoid table lock and improve performance. Here is my situation:
a parent table we call it parent (id is the primary key),
a look up table we call it lookup (id is primary key)
a child table wevcall it child ((parent_id, lookup_id) combination is the primary key on the child table). The parent.id and lookup.id are referenced as foreign keys as well. Obviously we have a unique key (for primary key) on parent_id, lookup_id in child table. My question is should we also create an index on parent_id and another index on lookup_id on the child table from best practice perspective? Our db is OLTP system.

Thanks a lot for your insights!

Shirley
Tagged:
«1

Answers

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    If there is already an index on (PARENT_ID, LOOKUP_ID) in the child table, there would generally be no need to create a separate index on PARENT_ID. You generally don't need to have two indexes with the same leading columns. You would, however, almost certainly want to create a separate index on LOOKUP_ID.

    Justin
  • 614497
    614497 Member Posts: 209
    Thank you very much! Justin.

    We have a concatenated index with 6 columns starting with lookup_id column in child table. Do you think we still need a separate index for lookup_id? What about if we have range partition on lookup_id column?

    Really appreciate your help,

    Shirley
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown
    user611494 wrote:
    I have learned that we need to create an index for each foreign key in order to avoid table lock and improve performance.
    That sentence can be viewed in two different ways (a) you've been told (or read) that you're supposed to create these indexes, or (b) you've discovered that for your system it's necessary.

    As a general principle, you do not need to create "foreign key indexes" just because you have declared a foreign key constraint. (If it were necessary, Oracle would probably create the index automatically just as it does for primary key constraints). However, if you expect to delete parent rows, or update parent keys, then a "foreign key index" will avoid the need to lock the child table; and, fairly often, the "foreign key index" may be a useful index in its own right for performance reasons would be an index that you would create even if you had't declared the constraint.

    Here is my situation:
    a parent table we call it parent (id is the primary key),
    a look up table we call it lookup (id is primary key)
    a child table wevcall it child ((parent_id, lookup_id) combination is the primary key on the child table).
    This actually sounds more like an "intersection table", representing a many-to-many association between lookup and parent.

    You need three indexes to enforce the uniqueness of the primary keys (although the indexes need not be unique, and need only start with the primary key columns, so you will have:
        parent(id)
        lookup(id)
        child(id_parent, id_lookup)   or child(id_lookup, id_parent)
    (Or indexes starting with these columns).

    It might be perfectly reasonable (for your application) to have both the indexes on the child as this might allow you to execute some queries that join from lookup through child to parent without visiting the child table working in either direction.

    It might be necessary (for your application) to have at least
    a) (id_parent, id_lookup) and (id_lookup)
    or
    b) (id_parent) and (id_lookup, id_parent)
    (or indexes starting that way) to ensure that you have covered both foreign keys and the primary key. It is entirely possible that having just (id_parent, id_lookup) or just (id_lookup, id_parent) will work for you because you know that you won't run the risk of updating one of the two outer tables in a way that causes a locking problem, and the other index would simply be surplus to requirements.

    Bottom line:
    Don't create "foreign key indexes" unless you actually need them
    "Primary key" and "foreign" key indexes only have to start with the relevant columns.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • Rafu
    Rafu Member Posts: 186 Bronze Badge
    Would it be worth considering to create the many to many link table as a index organized table (id_parent, id_lookup).
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown
    user8601229 wrote:
    Would it be worth considering to create the many to many link table as a index organized table (id_parent, id_lookup).
    I'd certainly consider it. Whether or not I decided in favour not would depend on several factors:
    <ul>
    It will be efficient going one way across the intersection, how efficient will the secondary index be if I need to go the other way (answer varies with version of Oracle)
    how many non-key columns are there in the intersecton table
    how many non-key columns should go into the overflow if there were more than four or five small columns
    If it's a question of changing an existing structure, how much application code has to be checked for storing rowids rather than urowids
    </ul>

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • Rafu
    Rafu Member Posts: 186 Bronze Badge
    If query execution is going another way would the secondary unique index look like (id_lookup, id_parent), if no other columns in overflow.
  • 614497
    614497 Member Posts: 209
    Thanks,

    But what do you mean by "no other columns in overflow"?
  • 614497
    614497 Member Posts: 209
    Thank you very much, Jonathan, for your thorough explanation. I have learned foreign key indexes from online postings. Based on what your said that we may not need these foreign key indexes since both parent.id and lookup.id are generated from sequences and we never delete records or update primary key on those two tables. You are right that child table is an “intersection table” of parent and lookup tables. Since lookup table stays pretty stable (no a lot of transactions) and every time we insert a record in parent table we insert a bunch of records into child table so that is why I see this parent-child relationship.

    By the way, I bought your book of “Cost-Based Oracle Fundamentals” but have not got chance to look into it yet since I am reading Oracle Performance Tuning Guide now. I am thinking that your book may require that I have the knowledge from this manual.

    Anyway, thanks a lot for your help and have a great day!

    Shirley
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown
    Rafu wrote:
    If query execution is going another way would the secondary unique index look like (id_lookup, id_parent), if no other columns in overflow.
    Yes, although you might find a reason in some cases to include extra copies at the end of the index to allow some queries to avoid going to the primary key index to pick them up (but that's just the old trick of copying extra columns from the table into an index to avoid visiting the table).

    In 10g, the secondary index will be a little smaller than in earlier versions because Oracle added an enhancement that means it doesn't have to duplicate primary key columns into the "urowid" portion of the index entry if they already appear in the definition of the secondary index. In fact, I have some sample code on my laptop dated Oct 2004 (five years old) that demonstrates this point and suggests that it makes IOTs a particularly interesting option for intersection tables.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown
    user611494 wrote:

    By the way, I bought your book of “Cost-Based Oracle Fundamentals” but have not got chance to look into it yet since I am reading Oracle Performance Tuning Guide now. I am thinking that your book may require that I have the knowledge from this manual.
    Shirley,

    Thanks for the comment.

    You're right to start with the performance tuning guide. It will tell you the sorts of things that can happen - and my book is useful to explain why they happen (sometimes at the wrong moments).

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
This discussion has been closed.