Re: utilize index

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 Received on Mon Feb 26 2001 - 03:57:41 CST