Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: N to M relationship table and indexes
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