Re: How to speed up Query with WHERE AND/OR, with Subqueries using IN
Hi Rick,
thanks for your help. I have found another possible solution for my problem, by redesigning the statement, and using UNION and HAVING Statements. Now i have three single statements cleaned up with UNION DISTINCT instead of one big query with slow subs...
---------------
SELECT p.productID FROM productattributes p WHERE (atrID = 'A00056' and valID = 'A') OR (atrID = 'A03413' and valID = 'B') GROUP BY p.productID HAVING COUNT(p.productID) = 2
UNION DISTINCT
SELECT p.productID FROM productattributes p WHERE (atrID = 'A00056' and valID = 'A') OR (atrID = 'A03413' and valID = 'B') GROUP BY p.productID HAVING COUNT(p.productID) = 2
UNION DISTINCT
SELECT p.productID FROM productattributes p WHERE (atrID = 'A00056' and valID = 'A') OR (atrID = 'A03413' and valID = 'C') GROUP BY p.productID HAVING COUNT(p.productID) = 2;
---------------
This is much more faster... below 1 sec for about 100 results out of my 160.000 records table.
For the moment thats okay, but i will think about storing data in a more performance optimized way by using optimized table designs for search purpose...
Regards,
Martin
Subject
Views
Written By
Posted
3980
May 17, 2010 04:25AM
1275
May 18, 2010 09:24AM
Re: How to speed up Query with WHERE AND/OR, with Subqueries using IN
1298
May 21, 2010 03:21AM
1347
May 21, 2010 09:21AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.