Re: N to M relationship table and indexes

If the secondary index is on BOTH columns, this would avoid a table access to the table:

SQL> create table doc_ass(doc_id number, ass_id number);

SQL> create index i1 on doc_ass(ass_id);

SQL> select doc_id
  2 from doc_ass
  3 where ass_id = 1
  4 /

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DOC_ASS'    2 1 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) SQL> drop index i1;

Index dropped.

SQL> create index i1 on doc_ass(ass_id,doc_id);

Index created.

SQL> select doc_id
  2 from doc_ass
  3 where ass_id = 1
  4 /

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) But then, UNIQUE or NOT-UNIQUE ?

"Anurag Varma" <avarmadba.skipthis_at_yahoo.com> wrote in message news:NPZkb.8993$PZ1.4586_at_nwrdny03.gnilink.net...
>
> "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:17:53 CDT