Re: too much consistent gets

Andrei Romazanov wrote:
>
> Hello all,
>
> has someone an idea, why ORACLE makes too much consistent gets.
> The situation is the following (simplified described):
>
> SELECT a [JOIN] b WHERE a.field1=NN and b.field2=MM
>
> there are the following indexes:
> on A:
> IND1 (field1), IND2 (JOIN fields)
> on B:
> IND3 (field2), IND4 (JOIN fields)
>
> if I execute only SELECT A, then ORACLE uses IND1, and makes about 100
> consistent gets - OK!.
> If I execute SELECT on both tables, then:
>
> SELECT STATEMENT Optimizer=CHOOSE
> SORT (AGGREGATE)
> NESTED LOOPS
> TABLE ACCESS (BY INDEX ROWID) OF 'A'
> INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF 'B'
> INDEX (RANGE SCAN) OF 'IND4' (NON-UNIQUE)
>
> thereby makes ORACLE > 100000 consistent gets .
> It is strange that IND3 is not used.
> The tables A and B are not excessive: < 100000 data records.
> Why does ORACLE behave so strangely?
>
> Thank you in advance
> Andrei

Have you ANALYZED'd the tables. A hash join or sort-merge may be more appropriate.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."