Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> N to M relationship table and indexes
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 GyselinckReceived on Mon Oct 20 2003 - 16:52:29 CDT