Re: Learning more about and reading 10053 trace files

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • Date: Tue, 5 Sep 2006 12:44:57 -0500

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.


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

Other related posts: