Nothing like a little bit of humble pie.....
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@xxxxxxxxxxx> 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