Prev: Direct Client Requirement - Oracle DBA for St. Louis - MO
Next: Dynamic Cursor
From: The Magnet on 14 Jun 2010 17:33
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.


From: Thomas Blankschein on 15 Jun 2010 17:44

> Does anyone have a good idea on how to do this aggregation? I'm
> looking to try anything right now.
Do you know the STRAGG function from Tom Kyte? It works fine for me.


From: Vladimir M. Zakharychev on 19 Jun 2010 04:19
On Jun 15, 1:33 am, The Magnet <a...(a)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
 | 
Pages: 1
Prev: Direct Client Requirement - Oracle DBA for St. Louis - MO
Next: Dynamic Cursor