Avoid "Horror Queries": Keep Your SQL Simple and Fast

Session Number: 8067
Track: Database
Sub-Categorization: DB Dev
Session Type: Tips, Techniques and Tuning
Primary Presenter: Dani Schnider [Senior Principal Consultant - Trivadis AG]
Time: Jun 24, 2019 (03:45 PM - 04:45 PM)
Room: 617, Level 6

Speaker Bio: Dani Schnider works as a senior principal consultant and data warehouse lead architect for Trivadis, a Swiss IT consulting company. He finished his studies in computer science at ETH Zurich in 1990. Since 1994, he has been developing data models and applications with Oracle databases (version 6 to 12c), focusing mainly on data warehousing projects since 1997.

Most of Dani’s time is spent doing jobs for customers in design, data modeling, ETL development, architecture reviews, and performance optimization of Oracle data warehouses. He is the teacher of several Oracle trainings at Trivadis and co-author of the German books Data Warehousing mit Oracle—Business Intelligence in der Praxis (January 2011, ISBN 978-3446425620) and Data Warehouse Blueprints (September 2016, ISBN 978-3446450752).

Dani is an Oracle ACE and writes about data warehousing with Oracle on his blog, publishes articles and white papers, and gives presentations at conferences.

Technologies or Products Used: Oracle Database 12c (Release 12.1 and 12.2)

Session Summary for Attendees:  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.

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 you how to improve the performance of a query by factors with appropriate SQL statements and powerful Oracle SQL features.