Nothing like a little bit of humble pie.....
On 9/5/06, Charles Schultz <sacrophyte_at_gmail.com> wrote:
>
> 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
>
--
Charles Schultz
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 05 2006 - 12:56:18 CDT
