Re: String Concatenation

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Sat, 19 Jun 2010 01:19:15 -0700 (PDT)
Message-ID: <2bc84737-bebd-40ab-aa1f-b142605b697f_at_j4g2000yqh.googlegroups.com>

On Jun 15, 1:33 am, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> We're on Oracle 10g.  I have a huge dynamic query that is built in a
> PL/SQL procedure.  The query works and it dynamically puts together
> the criteria based on parameters passed into the procedure.
>
> However, there is one table in which the values need to be returned in
> A:B,C:D,E:F,G:H
>
> So, the query uses ROW_NUMBER() to get the first record in each set.
> But, for one of the joined tables I need to combine the rows into 1
> column in that delimited format.
>
> Right now I am using this as one of the columns in the query:
>
> (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
>                        AND t.tag_id IN (' || v_tag_id || ')) tag_list
>
> Let me tell you, with that it slows the query down where it runs
> nearly 5 minutes.  I'm hoping not to have to code yet another query,
> with the same dynamic criteria, just to get the list.
>
> Does anyone have a good idea on how to do this aggregation?  I'm
> looking to try anything right now.

So you still didn't resolve this issue...? Same questions as before:

1. What's the plan for this query? How much I/O and CPU?
2. Are TAGS and ARTICLE_TAGS properly indexed so that they could be efficiently joined? How big are they? How Oracle joins them? (can be found out from the plan.) How long a

SELECT t.tag_id, tag_name
  FROM tags t, article_tags at
 WHERE <const> = at.article_id
   AND at.tag_id = t.tag_id
   AND t.tag_id IN (...)

takes? About the same time as XMLAGG version or considerably less? 3) How big is the result set from which the XML fragment is assembled? How long will it take to XMLAGG a constant (literal) XML fragment of the same size? It might take significant time to parse and process a large XML fragment, especially when your system is CPU-bound.

While researching these questions you most probably will find the reason why it takes so long and arrive at the solution.

And a side note: you are using literal inlist in a dynamic query, this is not efficient and should be replaced with static query with subquery from a global temporary table or an in-memory nested table filled with search values.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sat Jun 19 2010 - 03:19:15 CDT