| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: utilize index
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
 |
 |