Understanding this query
Date: Thu, 27 May 2010 12:28:15 -0700 (PDT)
Message-ID: <5782699f-7a96-4728-8dd4-418a385e91a8_at_c7g2000vbc.googlegroups.com>
Hi,
Ok, we have a VERY complex query here which is dynamically made. The issue we are having is this:
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.
Here is the query with the explain plan afterwards. Again, remove the "tag_)list" from the outer most query, it runs in .1. Add it to the outer most query and it runs in 2.0 with the same execution plan.
SELECT article_id, publish_date, status_id, user_id, first_name, last_name,
status_text, tag_list
FROM (SELECT article_list, article_id, publish_date, status_id,
user_id, first_name, last_name,
status_text, tag_list, ROW_NUMBER() OVER (ORDER BY revision_date DESC) article_rows
FROM (SELECT a.article_id, a.date_entered, a.publish_date, s.status_id, u.user_id,
first_name, last_name, s.status_text, revision_date, ROW_NUMBER() OVER (PARTITION BY a.article_id ORDER BY revision_date DESC) article_list, (SELECT RTRIM (XMLAGG (XMLELEMENT (e, t.tag_id||':'||tag_name || ',')).EXTRACT ('//text()'), ',') FROM tags t, article_tags at WHERE a.article_id = at.article_id AND at.tag_id = t.tag_id) tag_list FROM articles a, users u, revisions r, status s WHERE a.status_id = s.status_id AND a.article_id = r.article_id AND a.owner_id = u.user_id) WHERE article_list = 1)
WHERE article_rows BETWEEN 1 AND 25
ORDER BY article_id DESC, article_id ASC;
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 |
2144 | 62 (5)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 |
2144 | 62 (5)| 00:00:01 |
|* 2 | VIEW | | 1 | 2144 | 61 (4)| 00:00:01 | |* 3 | WINDOW SORT PUSHED RANK | | 1 | 2153 | 61 (4)| 00:00:01 | |* 4 | VIEW | | 1 | 2153 | 60 (2)| 00:00:01 | |* 5 | WINDOW SORT PUSHED RANK | | 1Received on Thu May 27 2010 - 14:28:15 CDT
| 177 | 60 (2)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | REVISIONS | 1
| 22 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1
| 177 | 59 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1
| 155 | 57 (0)| 00:00:01 |
| 9 | MERGE JOIN CARTESIAN | | 1
| 107 | 5 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | STATUS | 1
| 40 | 2 (0)| 00:00:01 |
| 11 | BUFFER SORT | | 80 |
5360 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | USERS | 80 |
5360 | 3 (0)| 00:00:01 | |* 13 | TABLE ACCESS BY INDEX ROWID| ARTICLES | 223 | 10704 | 52 (0)| 00:00:01 | |* 14 | INDEX RANGE SCAN | ARTICLES_IDX3 | 445
| | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | REVISIONS_IDX | 1
| | 1 (0)| 00:00:01 |