Link to home
Start Free TrialLog in
Avatar of DB-aha
DB-aha

asked on

Oracle SQL Multiple Unions

I have a large query that looks something like this:

Select
     columns
 FROM(
                    --Dataset 1
                   SELECT
                       Columns FROM TableName
                  UNION
                 --Dataset 2
                   SELECT
                       Columns FROM TableName
                 UNION
                 --Dataset 3
                   SELECT
                       Columns FROM TableName
             ) AS ALIAS#1
                  (
                    --Dataset 4
                    SELECT
                       Columns FROM TableName
                  UNION
                   --Dataset 5
                   SELECT
                       Columns FROM TableName
                  UNION
                  --Dataset 6
                    SELECT
                       Columns FROM TableName
                  )AS ALIAS#2
WHERE
CONDITION #1
CONDITION #2
ETC..

The question is, what is the order of operations within each block that deliver an alias

There are 3 select statements in each, how are they run and then union compared?

Thanks in advance,
SOLUTION
Avatar of Steve Wales
Steve Wales
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DB-aha
DB-aha

ASKER

My issue though is that I do not have access to the actual data, I am just looking at the code to see where data is being changed, updated, manipulated etc. I cannot run code even in read only access so deciphering this is not possible tools. Thanks
so your basically driving a car with a blindfold ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
> Steve Wales
your sample shows index skip scan.
usually, this is for me an indication of a problem

it's using an index with more fields than fields required to do the search
eg: search criteria contains 2 fields, while index contains 5 fields
creating a new index with only the required fields gives better performance
It's a third party, pre-packaged application, I have no flexibility in modification to the tables in it - and the query I used is highly unlikely to be a life-like query - it's the largest table in the database and was something I could quickly use on the test box as an answer to the original topic with a demo explain plan.

Real world queries would use the full primary key almost all the time :)  (but we're getting away from the original question ... thanks for your concern though!)
Avatar of DB-aha

ASKER

I am still unclear. The reason I asked this question was because I thought there was a fundamental order that is applied w hen you deal with say 3 unions as it exists in part of the code I provided.  I guess the irst question should have been, In the first part of the code, is there a tried and true order on how that runs?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial