Bitmap conversion of B-tree indexes

[sem]

I have 2 tables, parent and child linked via foreign key (referencing field fk_field is indexed). Besides pk_field parent table contains quite selective and indexed field selective_field.
child table contains some not quite selective though also indexed field nonselective_field. All indexes are B-tree ones.

The query looks like

SELECT 1
FROM
parent P, child c
WHERE p.selective_field between :1 and :2
AND c.fk_field = p.pk_field
AND c.nonselective_field=:3

Tables are not too large (both less then 100K records), statistics is fresh. I played with quite wide range of optimizer_index_cost_adj and optimizer_index_caching values, the result is the same.

I can not understand the plan chosen: instead of using obvious NL and just ignore completely unsuitable (B-tree!) index on [/b]nonselective_field[/b], Oracle uses bitmap and, utilizing both indexes and quite simple query runs forever. Suppressing that index makes the whole query complete within 0.05 seconds.
I can not reproduce it on another system so I can not suggest a testcase. Does anybody have any thoughts on this? What it may depend on?

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Ok, here's an example plan

SELECT STATEMENT, GOAL = CHOOSE            
 FILTER                            
  NESTED LOOPS
   TABLE ACCESS BY INDEX ROWID    Object owner=ADMBACK Object name=PARENT
    INDEX RANGE SCAN    Object owner=ADMBACK    Object name=SELECTIVE_IND
   BITMAP CONVERSION TO ROWIDS                            
    BITMAP AND                            
     BITMAP CONVERSION FROM ROWIDS                            
      INDEX RANGE SCAN    Object owner=ADMBACK    Object name=FK_IND
     BITMAP CONVERSION FROM ROWIDS                            
      INDEX RANGE SCAN    Object owner=ADMBACK    Object name=NONSELECTIVE_IND

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Never mind, found it in metalink Note:259126.1

Regards, Dima

http://seminihin.narod.ru