too much consistent gets

From: Andrei Romazanov <romazanov.andrei_at_gfos.de>
Date: Thu, 7 Feb 2002 17:10:47 +0100
Message-ID: <a3u8pg$1ahun6$1@ID-70985.news.dfncis.de>

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 Received on Thu Feb 07 2002 - 10:10:47 CST