RE: Learning more about and reading 10053 trace files
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- Date: Tue, 5 Sep 2006 11:06:47 -0700
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:
