Re: utilize index

One can see what has happened.
Using a table alias, you need to use the table alias in your hint. Also, most likely the lastname column is not being indexed and as there has to be a full table scan on the driving table anyway, the hash join was supposed to be cheaper.
This might be true, in the past I have conducted experiments, and indeed the full table scan in combination with hash join did consume less I/O So a set autotrace on explain stats or a true trace would provide more conclusive evidence. However first you should change the tablename in the hint into that alias.

Hth,

Sybrand Bakker, Oracle DBA

"Vadim Grepan" <kezal_at_mail.ru> wrote in message news:3A9A2C36.D5A96EFB_at_mail.ru...
> Hello Sybrand!
>
> Thanks for your answer.
>
> Here is first query:
>
> SELECT /*+ INDEX(contributorinformation pk_contributorinformation) */
> t2.borndatestring, t2.dieddatestring
> FROM contributorinformation t2
> WHERE muzecontribid=2144244;
>
>
> .. and second:
>
> SELECT /*+ INDEX(contributorinformation pk_contributorinformation) */
> t1.firstname, t1.lastname,
> t2.borndatestring, t2.dieddatestring
> FROM contributor t1,
> contributorinformation t2
> WHERE t1.lastname like 'Churchward%' AND
> t1.muzecontribid=t2.muzecontribid;
>
>
> Sybrand Bakker wrote:
>
> > On Mon, 26 Feb 2001 11:49:34 +0300, Vadim Grepan <kezal_at_mail.ru>
> > wrote:
> >
> > >Hello All!
> > >
> > > I cannot use existing index during select with join-clause. There
> > >are two large tables, with common relation Parent-Child (PK-FK). Both
> > >columns indexed. Simple select from child table successfull using
 index.
> > >Meanwhile select with join rejects them and does fullscan.
> > >Index has valid state but even hint is helpless.
> > >
> > > Tables are CONTRIBUTOR and CONTRIBUTORINFORMATION.
> > > Simple select-clause has good execution plan:
> > >
> > >Execution Plan
> > >----------------------------------------------------------
> > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=7)
> > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRIBUTORINFORMATION'
> > > (Cost=3 Card=1 Bytes=7)
> > >
> > > 2 1 INDEX (UNIQUE SCAN) OF 'PK_CONTRIBUTORINFORMATION' (UNIQ
> > > UE) (Cost=2 Card=1)
> > >
> > >Select with join-clause:
> > >
> > >Execution Plan
> > >----------------------------------------------------------
> > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4877 Card=167435336
> > > Bytes=28966313128)
> > >
> > > 1 0 HASH JOIN (Cost=4877 Card=167435336 Bytes=28966313128)
> > > 2 1 TABLE ACCESS (FULL) OF 'CONTRIBUTOR' (Cost=627 Card=1686
> > > 7 Bytes=2799922)
> > >
> > > 3 1 TABLE ACCESS (FULL) OF 'CONTRIBUTORINFORMATION' (Cost=30
> > > 0 Card=992680 Bytes=6948760)
> > >
> > >It works on Sun SPARC, Oracle EE 8.1.6
> > >
> > >Rgds, Vadim Grepan
> > >------------------------
> > >Moscow, Russia
> > >
> > >
> > >
> > >
> >
> > This is just too little background for useful comments. Basically it
> > looks like 'It doesn't work'. But as you omit the offensive sql, we
> > have no clues whatsoever on how to resolve this. Evidently the join
> > clause is causing it, but this might be a clause deliberately
> > switching off the index.
> >
> > Regards
> >
> > Sybrand Bakker, Oracle DBA
>
> Rgds, Vadim Grepan
> ------------------------
> Moscow, Russia
>
>
Received on Mon Feb 26 2001 - 04:32:19 CST