RE: Learning more about and reading 10053 trace files

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <Brandon.Allen@xxxxxxxxxxx>, "'Charles Schultz'" <sacrophyte@xxxxxxxxx>
  • Date: Tue, 5 Sep 2006 15:02:32 -0400

Hmm. I'm not sure whether Oracle ever takes advantage of this, but if a hash
plan is used there is no reason to delay emitting filtered rows. They might
not be in order, and you'd have to check the hash before you passed a row on
to the result set to avoid sending it twice, but I don't see why "DISTINCT"
in and of itself is an impediment to beginning to dribble rows into the
result set right away. If Oracle used an insertion sort this would also be
plausible. Again, this does not imply you are wrong in what Oracle actually
achieves at the moment.

 

In fact, the very first row retrieved and filtered can certainly be passed
on if DISTINCT is the only issue.

 

Regards,

 

mwf

 

  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Allen, Brandon
Sent: Tuesday, September 05, 2006 2:07 PM
To: Charles Schultz
Cc: Oracle-L Freelists
Subject: RE: Learning more about and reading 10053 trace files

 

That's okay, we'll all just pretend we didn't read that first sentence ;-)

 

Yes, the DISTINCT (sort & filter) happens at the end, but the difference is
that a DISTINCT requires all rows to be fetched, sorted and filtered before
it can return the first row - so it can't return the first row until it has
processed all rows.  With a non-distinct/ordered/grouped query, Oracle can
just grab the first row from table A, find the first matching row from table
B and return that one row regardless of all the remaining rows.  You're also
right that sometimes Oracle's "FIRST_ROWS" plan will in fact return all rows
faster than it's "ALL_ROWS" plan, but that should be the exception and
usually only happens due to miscalculation in the estimated cardinality.

 

Since all rows must be fetched for a DISTINCT, Oracle will probably not even
consider performing a NL Join and will instead go with a HASH or SORT-MERGE
join, but I'm not sure about that - maybe it still considers NLs too.  I
haven't done much 10053 analysis either.

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.

Other related posts: