Re: N to M relationship table and indexes

"Luc Gyselinck" <Luc.Gyselinck_at_nospampandora.be> wrote in message news:xOYkb.93360$Lh6.4506693_at_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

Look into making doc_ass (nice name :) an IOT on columns doc_id, ass_id. Then create a non-unique/secondary index on ass_id.

Reverse .. if ass_id is used more.

Anurag Received on Mon Oct 20 2003 - 18:02:05 CDT