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#Oracle_DB #UNIX beginnerAsked:
Who is Participating?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.