Oracle Scratchpad

Beating the Optimizer

Back to main Tutorial Page

Beating the Optimizer – Agenda

This is a follow-on from the course “Designing Optimal SQL”, and focuses on writing SQL to emulate transformations that are currently not available to the Oracle Optimizer. The other course was essentially a guide to writing “normal” SQL in the best possible way – this course is about writing “abnormal” SQL because that’s the only efficient thing to do.

Session 1
1.5 hours

Single table access paths

We start with a brief reminder of what we need to know to write efficient and scalable SQL, then we examine the B-tree and bitmap access paths that are automatically available to Oracle as it accesses a single table before extending Oracle’s strategies with a few manually constructed strategies that become available if we can rewrite the SQL.

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

The two-table join

In this session we examine a very simple join and note a fundamental limitation in the optimizer’s ability to find the best strategy for joining two tables. We see how we can overcome this limitation – at a cost of more complex SQL – and look at the way we need to think about joins to minimise the work we do, noting that the possible benefit isn’t always as great as we might first think. We also review a method for keeping the code simpler at a risk of variable performance – then find we can eliminate the variability by again increasing the complexity.

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

Complex Joins

After setting the groundwork with single table access paths and two table joins, we go on to more complex examples, showing how the principle can be used to emulate data warehouse patterns of query in a structure designed for OLTP data access; even to the extent of emulating a Star Transformation in Standard Edition Oracle where bitmap indexes are not implemented.

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

Structures and Features

Falling back to slight more standard SQL, we take a look at the way in which we can use features like function-based indexes, virtual columns and deterministic functions in the newer versions of Oracle to reduce work. We also look at the ways in which structures such as sorted hash clusters and partitioning allow us to re-think the way we write SQL to minimise the work done.

Back to main Tutorial Page

Blog at