SQL_SET_OPERATOR_INTERNAL_WORK

Hi Expaert,

I have a question regarding the internals (and costs) of UNION and UNION ALL statement.

Up to now we are running some of our selects on a huge table (table1) which consists of more than 1 billion rows.
The data of this table will be split into two tables (table1_curr and table1_history).

Most of times we will just use the current data (which is about 15% of the data).
But in fact that we will still need to create reports consisting of all data, we changed the select to:

SELECT ... FROM table1_curr
WHERE ....
UNION ALL
SELECT ... FROM table1_history
WHERE ....

SELECT ... FROM table1_curr
WHERE ....
UNION
SELECT ... FROM table1_history
WHERE ....

How will this new statement be executed internally?

Thanks in Advance .
MIHIR KAR#Hadoop #Oracle_DB #UNIX beginnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
union will sort the records and take out the duplicates
how it's executed depends how it's going to eliminate the duplicates
it'll need a full table scan on both
i'm guessing this will fail, as your temp will probably not be big enough


union all will just add the records from the second table to the first
it will be a full table scan on both sets

why split, if need all data anyway ?
partitioning might be a way better option
0
Mark GeerlingsDatabase AdministratorCommented:
No, these won’t automatically be full-table scans.  That depends on the combination of:
1 What values your “where” clause provides.
2. Which columns are indexed.
3. The table statistics.
4. Your pfile or spfile parameters.
5. Your server hardware.

Queries with “union all” are always more efficient than queries with “union”, because “union” always forces a sort of the results.  Depending on how many rows are returned, you may or may not notice the performance difference.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.