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 Goodchild