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