Re: utilize index

Assuming that muzecontribid is a reasonably selective key, one would have to ask why oracle did
not use the index on its own. One possible reason is that you don't have current statistics on the tables.

I have seen instances where oracle doesn't choose to use an appropriate index, however, 9 times
out of 10, it's because the statistics are badly out of date.

Vadim Grepan 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
  Received on Mon Feb 26 2001 - 16:22:31 CST