Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> N to M relationship table and indexes

N to M relationship table and indexes

From: Luc Gyselinck <Luc.Gyselinck_at_nospampandora.be>
Date: Mon, 20 Oct 2003 21:52:29 GMT
Message-ID: <xOYkb.93360$Lh6.4506693@phobos.telenet-ops.be>


We are running Oracle 8.1.7.4.0

We have two tables that have a N to M relationship: Table DOCUMENTS ( doc_id,... )
Table ASSETS ( ass_id,... )

So we have a third table DOC_ASS with two columns, that 'joins' both tables Table DOC_ASS (doc_id, ass_id)

The question now is, how should this third table be indexed?

The combination of (DOC_ID,ASS_ID) is (and should be) unique. So we created a primary key constraint (NOT DEFERRABLE) on (DOC_ID,ASS_ID) USING INDEX which created a UNIQUE index. Queries to select the ASSETS for a given DOC_ID can profit from this index.

How should we index DOC_ASS for queries that select the DOCUMENTS for a given ASS_ID ?

1/ A second UNIQUE index on the (ASS_ID,DOC_ID) columns (as a unique constraint or not) ? Because (DOC_ID,ASS_ID) is unique, so is (ASS_ID,DOC_ID)!

2/ A second NON UNIQUE index on the (ASS_ID,DOC_ID) columns ?
3/ A second NON UNIQUE index on the ASS_ID column ?
4/ Other options I did not think about ?

--
Luc Gyselinck
Received on Mon Oct 20 2003 - 16:52:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US