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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so your basically driving a car with a blindfold ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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
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!)
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!)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER