Is this surprising? I still remember an Oracle presentation when 8i was
first coming out and the Oracle rep answered a question about hints by
saying that no one should use hints any more because the optimizer always
used the best path.
Half the room burst out laughing :).
Jay Miller
There seems to be a perfectly good theoretical reason
for this. But it would be interesting to know:
Number of blocks below HWM
Setting for db_file_multiblock_read_count
Maximum usable value for db_file_mbrc
The cost given by Explain Plan for the tablescan
before I confuse the
Hi Johnathan,
here is the skinny:
db_file_multiblock_read_count = 64
number of rows=15m
blocks=251071
empty_blocks=0
db_block_size=16384
total plan cost=4924
tablescan cost = 4924
Jack
--- Jonathan Lewis [EMAIL PROTECTED]
wrote:
There seems to be a perfectly good theoretical
reason
Undocumented behavior:
setting optimizer_index_cost_adj = 1 is simply begging the optimizer to use
any index, just kidding :}
I think it's simply b/c the total cost which is = cost of sequential read *
number of reported rows in the index (adjusted by optimizer_index_cost_adj)
is the lowest
: Re: too low optimizer_index_cost_adj causing bizarre index
choice
There seems to be a perfectly good theoretical reason
for this. But it would be interesting to know:
Number of blocks below HWM
Setting for db_file_multiblock_read_count
Maximum usable value for db_file_mbrc
I believe that Waleed's response is essentially correct.
The numbers still don't make sense, but ...
a) 250,000 blocks with the mbrc = 64. The t/s cost
(according to my observations on 9.0.1 and
8.1.7) should be based on an adjusted mbrc
of 15.5
250,000 / 15.5 = 16,000
I think the interesting thing is, why did it look at
an index not in select/where/sort operations at
all?Best idea I have heard so far is that somehow it
thinks that gathering the rowids from the index and
doing a table lookup is better than a fts. LElking
floated the idea that it might have to
