| problem with UNION ALL [message #59708] |
Wed, 17 December 2003 21:04  |
ora
Messages: 47
Registered: June 2002
|
Member |
|
|
Hi Everybody,
I am facing a problem i.e. i have written procedure which select data from some tables for different scenarios. There are 9 diff scenarios means i have written 9 different SQL SELECT statements. After that i have to insert this data from diff sql queries to a single table. All the sql queries are retreiving same columns but with different base conditions. So, to insert data into table i am using UNION ALL to make a single recordset and then i am inserting this records into table using a single insert statement like :
insert into table_name (col1,col2...)
(
select col1,col2... from tab1 where....
union all
select col1,col2... from tab1 where....
union all
select col1,col2... from tab1 where....
.
.
.
);
And this type of query is taking about 4 minutes to insert the data. Out of these queries only 2 are retreiving the data and all others returns null. In other words to insert only about 15 records it is taking 4 minutes which is untolerated.
And wht surprise me is when i insert this data using 9 different insert statement means without UNION ALL then it is taking around 4 senconds.
So i want to know wht's behind this? Wht's wrong with union all?
Tables which i am using not contain more then 3 lakh records.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
