Q
Problem solve Get help with specific problems with your technologies, process and projects.

The pros and cons of UNION

What are the pros and cons of using UNION in a SQL SELECT? Can unions cause your sql to retrieve data more slo...

wly?

One of the problems with the UNION clause is that it does not return duplicate rows. So if a row exists in both SELECT statement resultsets, then it will only show up once in the final result set. In order to remove the duplicates, the data must be sorted and then the duplicates are removed. Any sort operation can take a long time for SQL statements to process this. To see this in action, let's look at the EXPLAIN PLAN of a sample UNION SQL statement.

ORA9I SQL> set autotrace traceonly
ORA9I SQL> select object_name from db_objects
  2  union
  3  select object_name from db_objects;

15585 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'DB_OBJECTS'
   4    2       TABLE ACCESS (FULL) OF 'DB_OBJECTS'
ORA9I SQL> select object_name from db_objects
  2  union all
  3  select object_name from db_objects;

55556 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   UNION-ALL
   2    1     TABLE ACCESS (FULL) OF 'DB_OBJECTS'
   3    1     TABLE ACCESS (FULL) OF 'DB_OBJECTS'

See, no sort operation.

So the downside to the UNION clause is that a SORT
must be performed. But the upside is that you can take
two different SQL statements and combine their results
into one result set.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Here, we can see that a UNION ALL was performed, then the data was SORTED. A UNION ALL does not remove the duplicates. So if that clause was used, we won't see the sort operation as can be verified by the following:
This was last published in March 2002

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close