RE: Learning more about and reading 10053 trace files

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.

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