Re: Understanding this query
Date: Fri, 28 May 2010 13:35:08 -0700 (PDT)
Message-ID: <5b4fbc2c-c6d4-4428-9828-2c2621f925e9_at_w3g2000vbd.googlegroups.com>
On May 27, 9:28 pm, The Magnet <a..._at_unsu.com> wrote:
> Take a look at the inner part of the query, there is a dynamic column
> with some XMLAGG functions called "tag_list".
>
> If I do not place that column in the outer most part of the query, it
> runs in like .1. However, if I put the "tag_list" column as part of
> the return set it takes 2 seconds.
>
> Why? The column is already created. How can adding it to the outer
> query list cause a 2 second increase in execution time? The explain
> plan is the same.
To answer your question above: Because Oracle most of the time is smart - and if you don't refer to a column that is defined in an inner part from an outer part then Oracle realizes that it doesn't have to process it and simply doesn't do anything about it although it is defined in the inner part of the query.
So the difference in execution time - as already suggested by others - is caused by the fact that in one case the nested subquery is executed potentially for every row of the result set whereas in the other case it is simply ignored.
You don't mention the database version, but in recent versions (from 10.2 on at least) I would expect TAGS and ARTICLE_TAGS to show up in the execution plan when they are really going to get used.
And as already suggested by others - tracing the execution is always a good idea when trying to understand where the time is going.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Fri May 28 2010 - 15:35:08 CDT