Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

March 19

14:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


Avoid Horror Queries: Keep Your SQL Simple and Fast
Dani Schnider will join us for this session to discuss how to avoid SQL "horror" queries:

SQL is a powerful and efficient query language when used correctly. But inefficient and inappropriate SQL statements can cause massive performance problems. Instead of solving such issues with additional indexes, optimizer hints, SQL profiles or SQL plan baselines, a much better and more sustainable solution is often to rewrite the SQL statements and to reduce the complexity of the queries.

During performance tuning tasks for my customers, I’m often confronted with complex SQL statements. Many of these statements were developed years ago and changed several times by people that either had not enough SQL skills or had not the time to understand the original query in detail. This can lead to queries that are not only very slow, but also hard to understand - for the developers as well as for the query optimizer. By transforming these SQL statements into simpler queries, the optimizer can calculate better estimations and generate faster execution plans.

In this presentation, some real-life examples of terrible "horror queries" will be shown and rewritten into simpler, more elegant and faster SQL statements. The goal of the presentation is to show how to improve the performance of a query by factors with appropriate SQL statements and powerful Oracle SQL

Highlights include:

04:30 - Improving performance by reducing complexity
15:50 - Performance tuning without database access
29:30 - Making if faster step-by-step: going for incremental improvements
42:10 - Tuning SQL containing PL/SQL function calls

Your Experts

Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
Keith Laker
Keith Laker, Senior Principal Product Manager    
I have been working with Oracle data warehouse technology for over 20 years working on a wide variety of data warehouse projects both as a consultant and an onsite support engineer. I am now part of the Data Warehouse Product Management Team where I am responsible for analytical SQL. I am based in the UK at our Manchester office. A key part of my role is to work with our sales teams to brief our customers on data warehousing and analytical SQL: explaining the wide variety of new and exciting opportunities that our DW and analytical solutions can support. I regularly deliver sales training for data warehousing and analytical SQL across all our sales regions and provide competitive intelligence support across all the major data warehouse vendors.