Home » RDBMS Server » Performance Tuning » UNION QUERY OPTIMIZE
icon12.gif  UNION QUERY OPTIMIZE [message #131375] Fri, 05 August 2005 12:32 Go to next message
quetzalcoatl
Messages: 5
Registered: August 2005
Junior Member
Hi

I have this query


SELECT NVL(field1,0)/NVL(field2,1)
FROM
(
SELECT field1,0 FROM table WHERE condition = 1
UNION
SELECT 0,field2 FROM table WHERE condition = 2
)


field1 and field2 are numeric.


Does someone think in a better way Cool to do that query?
Re: UNION QUERY OPTIMIZE [message #131381 is a reply to message #131375] Fri, 05 August 2005 13:06 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Can you please post the actual query, along with what it is that you are trying to do?

Thank you.
Re: UNION QUERY OPTIMIZE [message #131388 is a reply to message #131375] Fri, 05 August 2005 13:39 Go to previous message
quetzalcoatl
Messages: 5
Registered: August 2005
Junior Member

OK,


I have one table, in this table i have two kinds of products, the way to difference this two products is by two differents fields,i.e.,

field1 field2 field3 field4 field5
------ ------ ------ ------ ------
data1 data12 3 0 1
data2 data22 0 1 2
data3 data23 2 0 1
data4 data24 0 1 2




The products with field5 = 2 are sold only if you buy a product with field5 = 1

The quantity of products sold with field5 = 1 is storaged in field3 and the quantity of products sold with field5 = 2 is storaged in field4

You can note that the product with field5 = 2 has 0 in field3 and that the product with field5 = 1 has 0 in field4.

So i must calcullate how many products ok kind field5 = 2 were sold for each ok kind field5 = 1 of such a way that the formula is:

(summatory of products with field5 = 1 * 100) / (summatory of products with field5 = 1)

the query i have actually is the next


SELECT field1,field2,field3,(SUM(f4) * 100) / SUM(f5) as result
FROM
(
SELECT field1,field2,field3,SUM(field4) as f4,0 as f5 FROM table WHERE condition = 1
GROUP BY field1,field2,field3
UNION
SELECT field1,field2,field3,0 as f4,SUM(field5) as f5 FROM table WHERE condition = 2
GROUP BY field1,field2,field3
)

I know the structure of the table hava a bad design but by the moment i cannot change it, it contains millions of records

In this part

(SUM(f4) * 100) / SUM(f5)

SUM(f5) must not be zero


ThanX!
Previous Topic: Regarding load testing
Next Topic: What is the use of index
Goto Forum:
  


Current Time: Fri Dec 09 14:35:23 CST 2022