Home » RDBMS Server » Performance Tuning » UNION causing slow performance (Oracle 9i)
UNION causing slow performance [message #331725] |
Sat, 05 July 2008 01:07 |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
|
|
Dumb question.. I have a query that uses a UNION statement, combining 2 individual queries. Both individual queries are almost identical, and both, individually, complete in less than 2-5 seconds. However, when I execute them via a UNION query (which is what I need), it takes 5-8 minutes to complete! This doesn't seem right to me, so I was wondering if there's anything I can do to "force" it to execute the queries separately, then combine them. There will be no duplicates between them, so I really don't need any extra logic in the union, like a distinct, etc. Changing it to UNION ALL to avoid the distinct aspect seems to make it worse, though. Any ideas?
Below is what the query looks like. Please feel free to offer any advice to combine or change it to make it perform better:
select t1.field1, t1.field2, t2.field3
from table1 t1, table2 t2
where t1.ind = 'Y'
and t1.id = 12345
and t1.a = t2.a
UNION
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1, table2 t2
where t1.ind = 'N'
and t1.id = 12345
and t1.a = t2.a
Thanks!!
|
|
|
|
|
|
|
Re: UNION causing slow performance [message #331823 is a reply to message #331725] |
Sat, 05 July 2008 14:43 |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
Oracle can rebuild UNION queries, so without execution plan it is hard to find problem.
please try this query:
select t1.field1,
DECODE(t1.ind,'Y',t1.field2,NULL) field2,
DECODE(t1.ind,'Y',t2.field3,NULL) field3
from table1 t1, table2 t2
where t1.id = 12345
and t1.a = t2.a
if column t1.ind has more values then 'Y' or 'N' add
and t1.ind in ('Y','N')
|
|
|
|
|
|
|
Re: UNION causing slow performance [message #331833 is a reply to message #331725] |
Sat, 05 July 2008 18:04 |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
strange...
look at the trace
the query with CASE have less physical reads (about 30% less) and litle bit less concisten gets.
what about HASH JOIN...
it takes some time to calculate hash for the tables, and after ir joins very quickly.. so if you waiting only for first rows hash join will be slower then nested loops.
if you need do calculations for all t1.id then HASH JOIN will be a lot faster then nested loops. if you need to acces all rows in t1 and join them with t2, nested loops is very bad way. better way is full scan t1 and t2 and hash join them.
to join tables of 40 mln rows and 90 rows with hash join can take about 5-10 mins if server is quadcore itanium 3ghz
hash join will take almost the same time to join all rows from t1 with t2.. and 1 row from t1 and coresponding rows from t2.
so if you need seled 1 t1.id from t1 and coresponding rows from t2 then use nested loops,
and is you need to select all t1.id and all coresponding rows from t2 then use HASH JOIN.
to measure run time use create table as statement
then wait till ends 1 query, and do the same with other query.
first rows whitch shows on the screen to not show real time of execution.
|
|
|
Re: UNION causing slow performance [message #331834 is a reply to message #331725] |
Sat, 05 July 2008 18:14 |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
there is some code else...
maybe it help if you need select only 1 t1.id at the time..
select
t1.field1,
CASE WHEN t1.ind='Y' THEN t2.field2 ELSE NULL END field2,
CASE WHEN t1.ind='Y' THEN t2.field3 ELSE NULL END field3,
from (select t1.ind, t1.field1, t1.a from table1 t1 where t1.id=12345 order by t1.a ) t3
INNER JOIN table2 t2 ON t3.a = t2.a AND t2.indicator = 'Y'
I'm not sure, but if order t1 by t1.a and then join them the result must be the same...
but oracle will do ordering on lot less records.
|
|
|
Re: UNION causing slow performance [message #331835 is a reply to message #331725] |
Sat, 05 July 2008 18:30 |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
and one more thing...
using HASH JOIN for joining all rows on t1 with all rows with t2 is best then optimiser is CBO and gathered statistics for tables, gathered statistisc for indexes and gathered histograms for table columns and block size is 32k and.. so on and on and on
and one question more... what index is on t2?
one columnt in or two or more ?
[Updated on: Sat, 05 July 2008 19:01] Report message to a moderator
|
|
|
Re: UNION causing slow performance [message #331837 is a reply to message #331835] |
Sat, 05 July 2008 19:48 |
svguerin3
Messages: 44 Registered: November 2005 Location: TX
|
Member |
|
|
Great idea about separating out the offending query that does the ordering! The overall new query you posted limits the records to only ones that have an t2.indicator of Y, and unfortunately that eliminates a lot of rows we still need returned. I changed it to have a (+) on that line to fix this; is that a good approach to getting them back in there?
For example:
select
t1.field1,
CASE WHEN t1.ind='Y' THEN t2.field2 ELSE NULL END field2,
CASE WHEN t1.ind='Y' THEN t2.field3 ELSE NULL END field3,
from (select t1.ind, t1.field1, t1.a from table1 t1 where t1.id=12345 order by t1.a ) t3
INNER JOIN table2 t2 ON t3.a = t2.a AND t2.indicator (+) = 'Y'
Regardless, tests have unfortunately indicated that although the performance is good on the new query (with the (+)), the UNION ALL one still significatly outperforms it in almost every case.. I'm having a hard time understanding why that is. I can post the new explain plan if needed for the new CASE one, unless you can think of a better way to do the above (+) outer join.
To answer your question, on t2, the indexes are as follows:
Constraints/Indexes:
Name Type Index Parameters
---------- ---- ----- -------------------------------------------------------------------------------------------
c @@@@tbl2pk00 pk yes (a,seq_n)
c @@@@tbl2fk01 fk no (a) references table1 (a)
Table 1 has primary key of a. Thoughts?
|
|
|
Re: UNION causing slow performance [message #331847 is a reply to message #331837] |
Sun, 06 July 2008 00:45 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Hopefully this will clear some things up:
A hash join - in this case - will not be quicker because there is no idenpendent access path on TABLE2. To perform a HASH join, you would need to FULL TABLE SCAN table2, because it does not contain the constraining ID column.
The UNION ALL is faster because it does not have to perform the CASE statements. It probably DOES have to do extra IO (reading TABLE1 rows twice) but those rows are almost certainly cached.
The optimal SQL would probably be:
select t1.field1, t1.field2, t2.field3
from table1 t1, table2 t2
where t1.ind = 'Y'
and t1.id = 12345
and t1.a = t2.a
UNION ALL
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
and t1.id = 12345
ORDER BY 1 I suspect this is the SQL you ran to get the plan shown above. It is advantageous over the CASE version because it does not attempt to join to TABLE2 for the ind=N rows.
Of course, this may not be functionally identical to the UNION version you showed unless all values of "a" exist in TABLE2 and are unique. You be the judge.
Also, DO NOT push the ORDER BY into an inline view. If it is used as the hash table of a subsequent join, the order will be lost.
Ross Leishman
|
|
|
|
|
Re: UNION causing slow performance [message #331872 is a reply to message #331725] |
Sun, 06 July 2008 04:22 |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
rleishman,
but your second select in UNION is not corect 100% of cases
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
and t1.id = 12345
maybe there is not records in table2 for table1 record,
so your query will output more rows than needed.
and why you use OREDER BY 1 in second subselect?
It is ordering all UNION rows? or only second subselect in UNION output rows?
|
|
|
Re: UNION causing slow performance [message #331876 is a reply to message #331872] |
Sun, 06 July 2008 06:11 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Kriptas wrote on Sun, 06 July 2008 19:22 | rleishman,
but your second select in UNION is not corect 100% of cases
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
and t1.id = 12345
maybe there is not records in table2 for table1 record,
so your query will output more rows than needed.
|
rleishman wrote on Sun, 06 July 2008 15:45 |
Of course, this may not be functionally identical to the UNION version you showed unless all values of "a" exist in TABLE2 and are unique. You be the judge.
|
Kriptas wrote on Sun, 06 July 2008 19:22 |
and why you use OREDER BY 1 in second subselect?
It is ordering all UNION rows? or only second subselect in UNION output rows?
|
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2171079 - See section on "position"
Ross Leishman
|
|
|
Re: UNION causing slow performance [message #331877 is a reply to message #331725] |
Sun, 06 July 2008 06:22 |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
sorry I do not read you post wery good.
But about "order by 1" I know what is "order by 1" but I want to know
it orders only
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
and t1.id = 12345
ORDER BY 1
or whole
select t1.field1, t1.field2, t2.field3
from table1 t1, table2 t2
where t1.ind = 'Y'
and t1.id = 12345
and t1.a = t2.a
UNION ALL
select t1.field1, ' ' as field2, ' ' as field3
from table1 t1
where t1.ind = 'N'
and t1.id = 12345
ORDER BY 1
?
if I read rigth, it is need to order whole output record set.
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 22:45:40 CST 2024
|