Greg Stark wrote:
> 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% */"
>
>
Not to mention that hints would be helpful if you want to specify a particular index for a specific
query (case in point, testing plans and response of various indices without having to drop and
create other ones). This is a bit of functionality that I'd like to see.