I am currently in the process of optimizing a large query that was written for oracle 8i. My company currently uses oracle 10g which gives us a lot of options for optimization.
Please Reference the following pseudo queries:
SELECT * FROM ( SELECT * FROM ( original.slow_query1 ) UNION SELECT * FROM ( original.slow_query2 ) UNION SELECT * FROM ( original.slow_query3 ) )
SELECT * FROM ( SELECT * FROM ( improved.query1 ) UNION SELECT * FROM ( improved.query2 ) UNION SELECT * FROM ( improved.query3 ) )
I have been successful in decreasing the query time from about 30 seconds per subquery to less than 3 seconds each. However, when I use the
UNION operator to combine the results, my improvements total double the time of the original query when unioned.
I have checked the data that was returned and the number of rows columns and data in each of the
original subqueries is equal to the the
improved subqueries. However, when I use the
UNION operator on my improved version, I get a greater total of returned rows.
1. Why would the overall performance of my query be worse than the original when my subqueries are now completing in less than a tenth of the time of the original?
2. If I have gone through and compared the data returned by both queries and confirmed them to be the same, why would using the
UNION operator return more results in the improved version?
I am currently inclined to think that avoiding the
UNION operator would be the best was to go, but I would like to keep all the processing done on the database end. This means that sending the results to the client program to be sorted there is out of the picture.