Re: Why the optimizer sux..or sux I ?

Have you used histograms for that table? what this means is that the CBO decides a full table scan is more efficient, since there might be more rows matching the query. With an indexed retrieval at least two blocks are fetched for each row, that is if the head and some of the levels of the Btree index are already in memory, so in some case Oracle chooses a full table scan if decides that a full table scan is more efficient it sometimes decides that based on histograms. If the number of rows returned by query 2 are a lot more than those returned by query 1, that explains the behaviour of the CBO, if not I have no idea, and maybe hints should be used to get predictable behaviour. Ravi.

ajung_at_suxers.de wrote:

> Help me to believe in the Oracle 8i CBO:
> Assume the following table:
>
> table ojs_main(docnum number primary key ,heading char(4));
>
> The "heading" column is also index.
> When I do a simple search like:
>
> select docnum from ojs_main where heading='CONS' or heading='PARL'
>
> I get an optimal execution plan by using a range scan on the index
> of the "heading" column. When I change the query to
>
> select docnum from ojs_main where heading='CONS' or heading='PARX'
>
> the optimizer suddenly uses a full table scan for the table ojs_main
> with much more consistent gets and physical reads. The complete
> table and all indexes are full analyzed.
>
> So can someone explain me this behaviour ?
>
> Andreas
Received on Fri Jul 21 2000 - 00:00:00 CDT