Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

Original

SELECT
 *
FROM (

    SELECT
        *
    FROM (
        original.slow_query1
    )

    UNION

    SELECT
        *
    FROM (
        original.slow_query2
    )

    UNION

    SELECT
        *
    FROM (
        original.slow_query3
    )
)

Improved

SELECT
 *
FROM (

    SELECT
        *
    FROM (
        improved.query1
    )

    UNION

    SELECT
        *
    FROM (
        improved.query2
    )

    UNION

    SELECT
        *
    FROM (
        improved.query3
    )
)

Problems
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.

Questions
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.

share|improve this question
    
"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?" How did you compare the results to be the same? –  ypercube Jul 29 at 17:38
    
@ypercube I used PL/SQL Developer to copy the ordered data to excel where I ran a VLOOKUP on the primary keys returned and a giant sheet that shows if data on the same cell of two sheets is the same or if the data is different. –  Dodzi Dzakuma Jul 29 at 17:41
    
So you only compared the primary keys, not all the columns. The UNION checks the whole rows, not only the primary key column. So, your improved queries (at least one them) must be different from the original. –  ypercube Jul 29 at 17:46
    
@ypercube I checked all the columns. Using a third sheet which checks to see whether all the cells are equal or not. (Sorry about the edits to the comment, every time I hit the enter button, it automatically submits) –  Dodzi Dzakuma Jul 29 at 17:47
    
I'm going to check the number of returned queries to make sure I didn't make a mistake due to being in front of the computer too long. –  Dodzi Dzakuma Jul 29 at 18:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.