DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Performance Tip for Tuning SQL with UNION

Performance Tip for Tuning SQL with UNION

UNION statements can sometimes introduce performance penalties into your query. This post has a look at how to tune your query up!

Michal Davidek user avatar by
Michal Davidek
·
Jul. 27, 16 · Database Zone · Tutorial
Like (3)
Save
Tweet
69.42K Views

Join the DZone community and get the full member experience.

Join For Free

This post will be short. It's about a lesson learned by tuning the SQL used for loading items from unrelated tables using the same WHERE clause. It was performed on Oracle 11g, but I am pretty confident that it applies to most SQL databases.

Keynote

Using the "WHERE" clause after the whole "UNION" is performed is significantly slower than using the "WHERE" clause inside inner selects.

This, if you can ensure removal of duplicates by the inner WHERE clauses...

(SELECT * FROM TABLE_1 WHERE COL > 1) a
UNION ALL
(SELECT * FROM TABLE_2 WHERE COL > 1) b;

... is better than following ...

(SELECT * FROM TABLE_1 WHERE COL > 1) a
UNION
(SELECT * FROM TABLE_2 WHERE COL > 1) b;

... which is better than following ...

SELECT * FROM (
(SELECT * FROM TABLE_1) a
UNION
(SELECT * FROM TABLE_2) b)
WHERE COL > 1;

Explanations

  • UNION ALL is faster than UNION because plain UNION is expecting that within two joined datasets are duplicates which need to be removed. If you can ensure (by inner WHERE clauses) that there will be no duplicates, it's far better to use UNION ALL and let database engine optimize the inner selects.
  • Using a WHERE clause on the result of grouped results is too expensive because you are operating on more internal results than you need. Also, the database engine's optimization can’t be processed — the results don't have anything in common.
  • See this.
  • And this.

P.S. If you enjoyed this post, you can share this post anywhere as well as follow me on Twitter to stay in touch.

sql Database engine

Published at DZone with permission of Michal Davidek, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • The Most Popular Kubernetes Alternatives and Competitors
  • Don't Underestimate Documentation
  • DZone's Article Submission Guidelines
  • Autowiring in Spring

Comments

Database Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo