Re: How to interpret this explain analyse?

Kevin Brown <kevin(at)sysexperts(dot)com> writes:

> Ouch. Is this really a reasonable assumption? I figured the primary
> use of a cursor was to fetch small amounts of data at a time from a
> large table, so 10% seems extremely high as an average fetch size. Or
> is the optimization based on the number of rows that will be fetched
> by the cursor during the cursor's lifetime (as opposed to in a single
> fetch)?
>
> Also, one has to ask what the consequences are of assuming a value too
> low versus too high. Which ends up being worse?

This is one of the things the planner really cannot know. Ultimately it's the
kind of thing for which hints really are necessary. Oracle distinguishes
between the "minimize total time" versus "minimize startup time" with
/*+ ALL_ROWS */ and /*+ FIRST_ROWS */ hints, for example.

I would also find it reasonable to have hints to specify a selectivity for
expressions the optimizer has no hope of possibly being able to estimate.
Things like "WHERE myfunction(col1,col2,?) /*+ 10% */"

--
greg