Hard-Hitting, Beyond the Basics Advice for Breaking the Black Box Approach to Database Programming, Leveraging the SQL Language on Oracle Database, and Improving Overall Performance of the SQL that You Write
I pre-ordered this book in October 2010 while searching for a way to learn some of the more advanced features of Oracle Database's SQL; I have been searching for a follow up book to the "Mastering Oracle SQL and SQL*Plus" book that I read a couple of years ago, and I think that I finally found that follow up book. Written as the Oracle Database 11.2 sequel to the book "Mastering Oracle SQL and SQL*Plus" (and that book's much updated revision titled "Beginning Oracle SQL"), this book is not written as a simple reference for the Oracle specific SQL dialect. This is not a book that you will want to use when trying to learn the basics of creating a simple SQL statement with a four table join. However, if you are able to create that simple SQL statement with a four table join in less than 30 minutes with the help of an entity-relationship diagram (ERD), and without using a DISTINCT clause, this book's contents will prove to be a valuable resource not only to expand your depth of knowledge of the SQL language, but also to extract the extra value that becomes visible when Oracle Database is viewed as much more than a black box dumping ground for data.
The authors of this book are all OakTable Network members and have between 15 and 29 years of experiencing working with Oracle products. This long term exposure to Oracle Database is quite clearly an advantage when discussing many of the subtopics that are simply missed by other Oracle specific SQL language references. While there was no discussion of the SQL language from Oracle Database 2.0, the authors are clearly comfortable with the features in Oracle Database 11.2 as well as the features that are available in Oracle Database 8i, 9i,10g, and 11g R1.
The book's contents are well organized. While each chapter identifies the author who wrote the chapter (possibly indicating that the authors did not work together on each individual chapter), the book flows well with plenty of forward and backward references between chapters, as well as including references to other resources (Metalink, books, and blogs). Chapter one of the book is a bit different from the rest of the chapters in the book, and appears to be written as a transition area for readers to become familiar with SQL*Plus and Oracle Database. Chapter one will be valuable to readers attempting to adjust from using graphical query tools (TOAD, SQL Developer, Excel, or any number of other graphical tools) to using SQL*Plus. Additionally, that chapter helps readers who are familiar with other SQL dialects (such as that used by SQL Server) take advantage of Oracle Database's special characteristics, and introduces readers to multi-table INSERTs and MERGE operations. Understanding the information presented in execution plans is an emphasis in the remaining chapters of the book; this book's explanation of execution plan content ranks among the best, if not the best, that I have seen to date. While there is not a single cartoon drawing in the book, and it does not appear that any sections of this book were borrowed from other books, there are several very well placed diagrams in the book's chapters. The book makes a significant effort to control the scope of the material presented. In most cases, that effort resulted in a very easy to understand, yet thorough discussions of complex topics while building bridges to help the reader transition into Oracle performance specific books, such as the book "Troubleshooting Oracle Performance". In a couple of areas, adding an additional half-dozen words might have saved the reader a little confusion, but those are rare occurrences in this book. Be certain to install Oracle Database's sample schema, and download the script library for this book from the Apress website (some of the scripts, especially those in chapter 16, are quite useful and may not be printed directly in the book).
Foundation knowledge, and miscellaneous comments while reading the book:
* Pages 3-8: Provides a quick demonstration of how to connect to the database using SQL*Plus, which is helpful for people who have primarily used other query tools, or other database platforms. Also demonstrates the basics of executing SQL statements, setting up the SQL*Plus environment, and executing scripts.
* The book briefly touches on why it is important to use consistent formatting and bind variables in order to reduce the number of hard parses, and why writing SQL to limit logical IO is also important.
* Page 40: Automatic query transformation often takes place, for instance, converting an IN subquery into a standard join.
* Chapter 2 tries to break open the black box approach to writing SQL statements.
* The book makes effective use of analogies.
* Page 59: The book demonstrates that the array fetch size has an impact on the number of consistent gets performed when executing a query.
* Pages 64-65: Includes a test case that shows why an index would be used in one case to retrieve 1% of the table rows, while in another case a full table scan was more appropriate to retrieve 1% of the rows.
* Page 66: Nice summary of what controls the number of blocks that are read in a single read call during a full table scan or a fast full index scan. There is one potentially confusing sentence, "This could mean that a multiblock read might only read one block at a time." The concepts were correctly stated, however it might be worthwhile to state that "a multiblock read might be truncated to a single block read due to the blocks that are already in the buffer cache."
* Pages 74-75: Describes how B*tree indexes grow from a single block in size, and what triggers the index height to increase.
* Long code sections in the early chapters with embedded comments - probably OK for the intended audience.
* Describes various type of operations found in execution plans including: TABLE ACCESS FULL, INDEX UNIQUE SCAN, INDEX RANGE SCAN, INDEX RANGE SCAN DESCENDING, INDEX FULL SCAN, INDEX FULL SCAN (MIN/MAX), INDEX FULL SCAN DESCENDING, INDEX SKIP SCAN, and INDEX FAST FULL SCAN
* Describes and lists when the various join methods (NESTED LOOPS, SORT-MERGE, HASH, CARTESIAN) are appropriate.
* Pages 120-127: Explains how NULL values behave in potentially unexpected ways in IN, NOT IN, UNION, UNION ALL, INTERSECT, MINUS, GROUP BY, ORDER BY, COUNT, SUM, AVG, MIN, and MAX.
* The book demonstrates through several examples that there is more than one way to build a SQL statement to answer a specific question, but not all methods are equally efficient.
* Page 153: States that EXPLAIN PLAN only shows the estimated plan, while the actual plan may be different.
* Page 160: Provides a demonstration that shows EXPLAIN PLAN sometimes shows the wrong execution plan.
* Page 171: Provides a warning to SET SERVEROUTPUT OFF before displaying the execution plan using DBMS_XPLAN.DISPLAY_CURSOR.
* Page 175: Describes all of the format parameters for DBMS_XPLAN, including the undocumented ADVANCED parameter.
* Page 187: Example of collecting statistics on a table and its indexes without creating histograms.
* Page 191: States that using an account with DBA privileges simplifies the process of using DBMS_XPLAN.DISPLAY_CURSOR, using data from other accounts, and selecting from system views, but advises to do so only in a test environment.
* The book typically indicates the first Oracle release version that supports a feature that is being described.
* Page 218: Includes a test case that seems to demonstrate an error in the Oracle Database 11.2 documentation.
* Pages 223-224: Very helpful brief summary of most of the analytic functions that are discussed in the chapter - this list reduces the frustration in finding the correct analytic function that is needed to produce a desired result.
* Page 225: Demonstrates how to create a running sum that automatically resets when the value of a column changes.
* Page 244: Presence of the keywords WINDOW SORT in an execution plan indicates that the SQL statement uses an analytic function.
* The KEEP keyword is not mentioned in the chapter that describes analytic functions, however an example is provided on page 173 of the book in the pln.sql script (in the script download for chapters 5 and 15).
* Page 283: WITH clause is known as subquery factoring, and other database platforms (and the ANSI standard) refer to subquery factoring as common table expression.
* Page 286: Oracle may process a WITH block as either an inline view or as a temporary table.
* Book describes several methods for optimizing query performance, and suggests revisiting queries created in older release versions of Oracle Database to take advantage of performance optimizations that are available in more recent release versions. Also suggests revisiting PL/SQL code to determine if such procedural code may be accomplished with set-based plain SQL code. Emphasizes testing for performance, rather than just using the first query that seems to produce the expected result.
* Describes SQL features through version 11.2 (as does the book "Beginning Oracle SQL").
* Page 309: Table 10-1 quickly describes the various functions, operators, and pseudo columns that are related to the Oracle CONNECT BY syntax, including SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT, CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, PRIOR, LEVEL, and NOCYCLE.
* Page 331-334: Provides various demonstrations of common mistakes with queries containing EXISTS clauses.
Read more ›
