Authors: Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen, Jared Stil
Publisher: Apress
Pages: 580
ISBN: 978-1430262206
Aimed at: Oracle developers
Rating: 5
Reviewed by: Kay Ewbank

If you need to write SQL code for Oracle, this book should be on your bookshelf. This is the second edition of Pro Oracle SQL, updated to cover Oracle 12c, and with an extra chapter on 'cool SQL constructs'.

Karen Morton and her team combine good explanations of concepts with excellent knowledge of Oracle SQL, and the result is a book that is very understandable while explaining ideas such as execution plans and performance management so you really improve your SQL.

The book starts with a introduction to the core SQL statements, and by Chapter 2 moves on to SQL execution, with good explanations of the buffer cache, query transformation, and how to determine the execution plan. Chapter 3 covers access methods – full scans and index scans, then moves on to Join methods.

Morton is good on giving an overview idea, and chapters 4 and 5 both illustrate this. In Chapter 4 she discusses the fact that SQL is about sets, and how to move from procedural to set-based thinking. The different set operations, and the use of sets and nulls are both covered well.
Chapter 5 is called "It's about the question". The premise is that it's too easy to focus on the SQL query at the expense of looking at what question about the data you're actually trying to answer. She gives examples of how to ask good questions, how to clarify what the user actually wants, and how to ask questions about the underlying data. It's interesting reading and something to keep at the back of your mind, though I'm not sure it's a skill you can learn from just reading about it.

For many Oracle developers, Chapter 6 is where the book really gets interesting, as it's all about SQL execution plans. It gives a detailed description of the Explain plan, then moves on to execution plans. Advanced grouping and analytic functions each get a chapter, with coverage of the Cube extension to Group By, the Grouping() function, and Grouping Sets(). The chapter on Analytic functions has examples showing the use of aggregation functions, lead and lag, and performance tuning. The Model clause gets a whole detailed chapter to itself, and is a good explanation of how to use it for multidimensional analysis in SQL statements.

The next few chapters cover subquery factoring, semijoins and autojoins, and the ‘other’ SQL commands such as Insert and Update. There’s a good chapter on transaction processing, after which Morton moves on to testing, quality assurance, plan stability, and plan control. The chapter on plan stability looks at the way Oracle’s Cost-Based Optimizer (CBO) changes execution plans in a seemingly random way, and the fact that such changes are not at all random. Morton shows how to avoid plan changes, and what to do to identify changes. The chapter on plan control continues this with looks at modifying query structures to avoid plan instability, and what you can do if you don’t have access to the problem code. The book ends with a look at miscellaneous SQL constructs such as Decode, Case, NullIf, Pivot queries, and using SQL to generate test data.

Reading the book and working through the examples should really improve your SQL. The key to getting the best out of Oracle is expert use of SQL execution plans, and this book gives you the tools to work with them.