There could be many reasons why the CBO does not use an index and a NL join
like the RBO. The predicate columns could have skewed data distribution in
which case gathering histograms on those columns might help, even though
you're using bind variables. The index(es) could have a rather high
Tks Wolfang
I have read the paper.
db_file_multiblock_read_countinteger 32
hash_area_size integer 4194304
sort_area_retained_size integer 1048576
sort_area_size integer 2097152
hash_multiblock_io_count
Those setting partially cancel each other out: the
db_file_multiblock_read_count value of 32 causes the optimizer to assign a
lower cost to full table scans and subsequently to hash joins. The setting
of optimizer_index_cost_adj to 30 causes the optimizer to assign a lower
cost to index
