Re: Learning more about and reading 10053 trace files

I was going to send this to the list, but I need to think about it some more - I do not want to look like a complete idiot in a public forum. *grin* But for your amusement.....

Thanks, Brandon, that does make sense. However, this itch in the back of my brain will not go away. Hypothetically, I would think that the optimizer would still cost out the process of doing a "first k row" optimization. Obviously, as we all well know, FIRST_ROWS_10 is not limited to returning 10 rows - it merely aims to bring back 10 rows "cheaper". Sometimes FIRST_ROWS_10 can bring back all rows "faster" (wall clock time) than ALL_ROWS. Also, the DISTINCT operation happens last, after all the joins, right? It is not clear in my explain plan, but from other examples I have worked with, this seemed to be the case.

I guess I need to do a little more reading and testing on this topic.

On 9/5/06, Allen, Brandon < Brandon.Allen_at_oneneck.com> wrote:
>
> Hi Charles - I just read your post quickly and I'm not sure about this,
> but it sounds to me like maybe there is no "First K" rows calculation for
> the DISTINCT query because anytime you have a DISTINCT, GROUP BY, ORDER BY
> or other clause that forces all the data to be fetched and sorted prior to
> being returned to the user, it makes it impossible to do any type of "FIRST
> ROWS" processing - because you can't just grab the first 1, 10, or 100 rows
> until you grab them all and process them as requested, so it makes sense
> that the CBO wouldn't even consider the cost for such an access method.
>
> Regards,
> Brandon
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l