Beating the Optimizer with Jonathan Lewis

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.

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 minimize the work we do, noting that the possible benefit isn’t always as great as we might first think. 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. 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 minimize the work done.

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.