Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: many Union all vs. inserting results to temp table
Mark C. Stock wrote:
> <deann15_at_yahoo.com> wrote in message
> news:1142981681.639856.314160_at_v46g2000cwv.googlegroups.com...
> :I have a dynamic query which basically loop through an ID field in a
> : table then build queries dynamically then union all the queries
> : together, such as:
> :
> : select * from (complex subquery) where a = 1
> : union all
> : select * from (complex subquery) where a = 2
> : union all
> : select * from (complex subquery) where a = 3
> : ...
> :
> : Depending on what a user select, the query could have a few thousand
> : unions. My question is about scalability and performance: will it be
> : better if inserting the results of every query to a global temp table
> : then select * from the temptable instead of executing the long query
> : all at once?
> :
> : Thanks!
> :
>
> if your generating the query (whch may or may not be the best approach) why
> not just generate a single query:
>
> select * from (complex subquery) where a in (1,2,3... 9999999)
>
> or add the 'a in (...)' predicate to the 'complex subquery'?
>
> ++ mcs
Few thousand INs... see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:210612357425
and look for the February 7, 2006 entry.
(
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:953229842074
for those who have never had IN v. EXISTS arguments).
jg
-- @home.com is bogus. "Now key her up and throw away the lock." - Trevor GoodchildReceived on Wed Mar 22 2006 - 13:41:36 CST