Oracle Scratchpad

Optimal SQL

Back to main Tutorial Page

Designing Optimal SQL – Agenda

This tutorial has two targets. First: how do you improve the performance of a production system by attacking inefficient SQL; secondly: how do you design a system so that you can get data into and out of it efficiently.

For most people, the design has already happened, nevertheless you need to be aware that the two targets overlap significantly. As you struggle to address a particularly inefficient piece of SQL you need to be aware that there are structural features of the database that may help you find a cost effective solution despite the problems imposed by the SQL itself.

The course will focus on the SQL – falling back to structural issues as an aid to improving the mechanical efficiency of the SQL solution – and will spend some time explaining the pros and cons of various structural options as they become relevant to the SQL.

The course will cover methods of reviewing data distribution patterns, use of indexes, use of views (stored and inline), analytic functions, subquery factoring, as well as a brief review on hints and execution plans.

Session 1
1.5 hours


In which we examine the need for a global view point and a general strategy for minimising work while recognizing that there will always be a conflict between local and global optimization. We follow up with a quick review of  various uses of indexes, including some of the less common uses; and we close with ideas, and methods, for investigating and understanding the data and business requirements.

Break – coffee and informal discussion: 30 minutes
Session 2
1.5 hours

Basic Practices

How to approach the task of translating business requirements into SQL. The need for clarity in code, with a few suggestions (and justifications) for standards of presentation to improve ease of understanding. An introduction to the essential strategy for writing SQL that gets its result by doing the minimum work, and a case stufy of investigating and re-engineering badly performing statements.

Break – Lunch and informal discussion: 1 hour
Session 3
1.5 hours

Sundry Methods

A brief discussion of the mechanics (and costing) of nested loop, merge, and hash joins, and why it’s so easy for the optimizer to pick the wrong option. A few examples demonstrating classic problems, and solutions that take advantage of some of the slightly exotic options of SQL and Oracle’s available features.

Break – coffee and informal discussion: 30 minutes
Session 4
1.5 hours

More Methods

The need for the front-end code to co-operate with the database engine in certain circumstances. Thinking about indexes as if they were just another table – making a query more complex to make it faster. Profiles and Baselines and the hints that Oracle uses, and a closing look at the threats of hints and how to use them properly.

Back to main Tutorial Page

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by