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 -> Re: N to M relationship table and indexes

Re: N to M relationship table and indexes

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Mon, 20 Oct 2003 23:28:23 GMT
Message-ID: <rc_kb.9115$PZ1.2548@nwrdny03.gnilink.net>


Luc,

If you go with this debate then each and every combination of the columns in your table
need to be indexed in some combination or the other.

The benefit shoudl be carefully weighed against how much that index will be used.
After all 100 indexes on one table might eliminate your table access, but eventually they
might fill up your buffer cache that any benefit you thought you had would be lost.
Also, you would be looking at slower inserts/updates/deletes, more space usage yada yada

However, if you are confused about unique/non-unique .. then unique definitely.

.. And remember: I suggested an IOT .. not a HOT as you describe in your example.

Anurag

"Luc Gyselinck" <Luc.Gyselinck_at_nospampandora.be> wrote in message news:B2_kb.93660$tj6.4521094_at_phobos.telenet-ops.be...
> 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:28:23 CDT

Original text of this message

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