Re: Understanding this query

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 27 May 2010 23:28:56 +0200
Message-ID: <4bfee420$0$22943$e4fe514c_at_news.xs4all.nl>



Op 27-5-2010 21:28, The Magnet schreef:
> 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 | | 1
> | 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 |
>
>

Looks like tag_list takes a long time to read from disk, or a lot of CPU processing time (as you noted yourself, it's a dynamic column, so I guess it's CPU). If it's not used in the outer query, it will not be 'created' as you suggest. It will be ignored. Do both explain plans have corresponding 'Bytes' ?

Shakespeare Received on Thu May 27 2010 - 16:28:56 CDT

Original text of this message