Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Indexing foreign keys

    Is there a general consensus on whether all foreign keys should be indexed? I was reading about the problems with deadlocks that are possible when FK's are not indexed.

    Also, can a foreign key AND index be created in one ALTER TABLE statement, like you can for a PRIMARY KEY?

    I tried the following, which failed:

    Code:
    ALTER TABLE FOREIGN_KEY_TEST
    ADD (
    	CONSTRAINT foreign_key_test_myname_fk
    	FOREIGN KEY (myname)
    	REFERENCES PRIMARY_KEY_TEST (myname)
                 USING INDEX TABLESPACE users
    );
    Code:
    ALTER TABLE FOREIGN_KEY_TEST
    ADD (
    	CONSTRAINT foreign_key_test_myname_fk
    	FOREIGN KEY (myname)
                 USING INDEX TABLESPACE users
    	REFERENCES PRIMARY_KEY_TEST (myname)
    );
    -Chuck

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A foreign key must point to a primary or unique key in the parent table. You should index the column that the foreign key is attached to in the child table. This will greatly speed up processing if you delete the parent record. The create index command is a seperate step.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Thank you beilstwh.

    So, a table has a multiple field primary key:

    Code:
    ALTER TABLE INSLIC.UA_AGENCY_CLASS_N_LICENSES
    ADD (
    	CONSTRAINT UA_AGENCY_CLASS_N_LICENSES_PK
    	PRIMARY KEY (AGENCY_ID, AGENCY_LICENSE_NMBR, CLASS_ISSUED, CREATE_DATE)
    		USING INDEX TABLESPACE UA_DATA
    );
    Combinations of these 4 fields are also foreign keys:

    Code:
    ALTER TABLE INSLIC.UA_AGENCY_CLASS_N_LICENSES
    ADD (
    	CONSTRAINT UA_AGENCY_CLASS_N_LIC_AGNCY_FK
    	FOREIGN KEY (AGENCY_ID)
    	REFERENCES INSLIC.UA_AGENCYS (AGENCY_ID)
    	);
    
    ALTER TABLE INSLIC.UA_AGENCY_CLASS_N_LICENSES
    ADD (
    	CONSTRAINT UA_AGENCY_CLASS_AGENCY_LIC_FK
    	FOREIGN KEY (AGENCY_ID, AGENCY_LICENSE_NMBR)
    	REFERENCES INSLIC.UA_AGENCY_LICENSES (AGENCY_ID, AGENCY_LICENSE_NMBR)
    	);
    Would you still need to create 2 additional indexes for UA_AGENCY_CLASS_N_LIC_AGNCY_FK and UA_AGENCY_CLASS_AGENCY_LIC_FK?

    From what little I know I would say yes, but I'm still learning about index-tree structures.

    Thanks,
    Chuck

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If you are going to select the child table from the parent table, or if
    you are going to delete the child if the parent is deleted, then you should
    have an index on the foreign key. You can set the foreign key up to
    cascade delete so that if the parent is deleted, the child record is also
    deleted.

    If you are using the foreign key only for RI, then you might not want an
    index. Remember, the cost of maintaining an index is approx: 3 times that
    of maintaining a table....

    HTH
    Gregg

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    By "select the child table from the parent table", are you referring to a query along the following lines, where you reference their relationship in the WHERE clause?:

    select p.*, c.*
    from parent p, child c
    where p.pk = c.fk

    This would be the primary reason I would think an index would be useful, as it seems like it would bring back related records faster.

    And if this justifies an index on the foreign key(s), would I create 2 additional indexes in the example I posted above?

    -Chuck

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You are correct... if you reference the tables in a query, an index if worth it.
    You are also correct with the 2 index idea normally, but ...

    AGENCY_ID
    AGENCY_ID, AGENCY_LICENSE_NMBR

    Since AGENCY_ID is in both foreign keys, I would only create the index
    for AGENCY_ID, AGENCY_LICENSE_NMBR.

    HTH
    Gregg

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    You wouldn't happen to have a link to a document that explains why I would need only one of the two indexes, would you? Or maybe a brief explanation? it would truly help me out.

    Thanks
    -Chuck

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Agency_id is used for both FKeys. If you create only the index that has
    agency_id as the 1st column of the index, they any query with agency_id
    should look at the index. That all depends on the distibution (skewness)
    of the data... ie,

    agency_id - 2 different values 100 rows total 5 for code1, 95 for code2

    Oracle will not use the index on it's own (approx: 12% or the data returned
    and Oracle will use the index). If you use a histogram and let Oracle
    know about the skewness 5% or rows are code1, then Oracle will use
    the index.

    HTH
    Gregg

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Sorry.

    See the performance and tuning guide and the administrators guide

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    Again, I appreciate the help.

    So my last question on this topic:

    If an index solely on AGENCY_ID can be avoided because that field is part of two other multiple field indexes, why wouldn't the same rationale work for the 2 field index on AGENCY_ID, AGENCY_LICENSE_NMBR, since they are both in turn contained in the index on the primary key?

    -Chuck

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes
    Again, I appreciate the help.

    So my last question on this topic:

    If an index solely on AGENCY_ID can be avoided because that field is part of two other multiple field indexes, why wouldn't the same rationale work for the 2 field index on AGENCY_ID, AGENCY_LICENSE_NMBR, since they are both in turn contained in the index on the primary key?

    -Chuck
    It would - you don't need either index since the primary key index on (AGENCY_ID, AGENCY_LICENSE_NMBR, CLASS_ISSUED, CREATE_DATE) will serve for both.

Posting Permissions

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