Understanding this query

From: The Magnet <art_at_unsu.com>
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        |               |     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 |

Received on Thu May 27 2010 - 14:28:15 CDT