At the Trivadis CBO Days in June, well-known speakers (Tom Kyte, Jonathan Lewis, Christian Antognini, Mohamed Zait and Markus Winand) talked about different aspects of SQL performance tuning and the Oracle cost-based optimizer.
Most of them I already heard on conferences and trainings before, but one of the speakers – Markus Winand – I didn’t know before. I was very impressed about his presentation “Shaping the Optimizer’s Search Space” and decided to order his book SQL Performance Explained just after the CBO Days. Now, after reading the book, a short summary and a review from my personal point of view.
When I received the book, I was surprised about its size. 200 pages to explain SQL performance – is that possible? Soon, I realized that the book explains only one, but an important aspect of performance tuning: The usage of indexes in database applications. However, this aspect is explained with a lot of interesting detail information.
Markus Winand starts with an important message in the foreword: Indexing is a task that has to be done by the developers because they know the application and the desired access paths. For a database administrator or an external consultant it is much more difficult to decide what indexes are required by the application. As one of these external consultants I fully agree with this message. But unfortunately, good knowledge about indexing is sometimes missing in the development teams. This book helps to fill this gap.
After explaining the structure of a B-tree index, the book continues with an extensive chapter about WHERE conditions, the most important part on an SQL statement regarding performance tuning. Different types of WHERE conditions, such as equality or range searches on one or multiple columns, LIKE filters, conditions with NULL values, bind variables and SQL functions are investigated. Many fundamentals of SQL performance tuning are explained here, always related to the fact whether an index can be used or not. Although most of the principles are independent from the particular database system, differences between the most common relational database systems are explained. Most of the examples are described with Oracle syntax and executions plans, but the book also covers other databases such as SQL Server, MySQL and PostgreSQL. Interesting for me is the explanation about handling NULL values in Oracle, compared to other databases. I wasn’t aware that the Oracle implementation is so different to the SQL standard.
Another very interesting subject is the chapter about partial results of SQL queries. It contains many useful suggestions about top-n queries and paging through results. This chapter illustrates very clearly how these kind of queries can be implemented with MySQL, Oracle, PostgreSQL and SQL Server in an efficient way, i.e. that the SQL statements can be executed as pipelined queries using an adequate index. Although there is a SQL standard defined since years, not all databases support the FETCH FIRST/NEXT syntax yet. (Note: In Oracle 12c, this features was finally introduced, but this was after the book was published. Therefore the Oracle examples in this book still use the more complex syntax with ROWNUM and analytical window functions).
Other important aspects of performance tuning, such as join methods, scalability, data clustering, sorting/grouping and DML statements are explained in Markus Winand’s book, too. Here again, the focus is on the usage of indexes. For example, the performance of an INSERT statement is demonstrated dependent on the number of indexes on the table. This inspired me to write a new demo script that I will show in future Trivadis courses about SQL performance.
The target audience of this book are developers of OLTP applications, using Oracle, SQL Server, MySQL or PostgreSQL databases. The book was published in 2012 and is available as printed book or PDF document in English, German and French. Further information you can find on www.winand.at.
It was a pleasure for me to read “SQL Performance Explained”, and in my opinion it is a very good enrichment in the book shelf of every SQL developer.
Data Warehousing with Oracle 