Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Exclamation Unanswered: Indexing the foriegn keys

    Hi,

    I am looking at a database where I saw each and every foriegn key is indexed. Is it really worthy in doing so? But all of these foriegn keys are used to join in the queries.

    Few tables in this database have a large data, frequent inserts and also are being frequently queried.

    So I would like to know your suggestions on the way indexes are used here.

    Thanks
    Sateesh.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Indexing the foriegn keys

    Usually you should index all the foreign keys. This prevents Oracle from taking out table-level locks on the child table whenever someone tries to delete (or update the PK of) a parent. Only if you know that parent records will never be subject to deletion or PK updates should you consider not indexing the FK.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am looking at a database where I saw each and every foriegn key is indexed.
    >Is it really worthy in doing so?
    YES!
    Otherwise the table whole table gets locked upon inserts to ensure key is unique.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Dazang!
    Why didn't I know this?

    SONOFASASQUACH!

    wait, is that referenced-BY or referenced-to? or both?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re: Indexing the foriegn keys

    Tony,

    I am not performing any deletion or updation of primary key in the parent table, but I am using all of these foriegn keys in the join. I am afraid that if I delete the indexes on the foriegn keys, then the queries may become slower.

    Thanks

    Originally posted by andrewst
    Usually you should index all the foreign keys. This prevents Oracle from taking out table-level locks on the child table whenever someone tries to delete (or update the PK of) a parent. Only if you know that parent records will never be subject to deletion or PK updates should you consider not indexing the FK.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com/pls/ask/f?p...A:27412348051,


    the whole purpose of indexing fkeys is

    a) to avoid taking a full table lock on the child table when you DELETE from
    parent or UPDATE a parent primary key.

    b) to avoid really bad performance with ON DELETE CASCADE (a full scan of child
    for every deleted parent row)

    that is all.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Indexing the foriegn keys

    Originally posted by n_sateesh
    Tony,

    I am not performing any deletion or updation of primary key in the parent table, but I am using all of these foriegn keys in the join. I am afraid that if I delete the indexes on the foriegn keys, then the queries may become slower.

    Thanks
    Yes, that is also true - queries that benefit from the indexes may become slower, of course. But whether or not you are performing any deletions or updates, those indexes should be there in the event that anyone ever does deletes or updates of the parent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •