6 of 6 people found the following review
helpful:
5.0 out of 5
stars Hard-Hitting,
Beyond the Basics Advice for Breaking the Black Box Approach to Database
Programming, Leveraging the SQL Language, January 16,
2011
This
review is from: Pro Oracle SQL (Expert's Voice in Oracle) (Paperback)
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.
* Page 336-339:
Demonstration that EXISTS and IN queries may be automatically rewritten...
Read
more ›
Help other customers find the most helpful reviews
Was this review helpful to
you? Yes No
3 of 3 people found the following review
helpful:
5.0 out of 5
stars Master Oracle
SQL and Execution Plans, February 23, 2011
This
review is from: Pro Oracle SQL (Expert's Voice in Oracle) (Paperback)
The book is for Oracle professionals almost exclusively - the other RDBMS
are not covered. I've found the book very useful to both developers and DBAs (I
happened to be wearing both hats) in day to day activities.
The authors
have assumed you are already in the Oracle field. If you are not, then the
material may often overwhelm you. Novices can find more suitable books out here
for sure. By the time you are through the book, you'll be up several levels when
it comes to writing better SQL or understanding better already written SQL.
I give all 5 STARS to the authors of the book for the great job done on
demystifying SQL execution plans. The book shows and analyzes execution plans
over and over again until they become your second nature. It took chapters 2
through 6 to build the necessary background and take readers to the very hart of
execution plans. The rest of the book also uses the plans as the main tool for
SQL characterization and performance analysis.
Page 165 shows how to
read an exec plan as a plain English narrative. In my experience, this is highly
useful for:
1. Putting yourself in the shoes of Oracle Optimizer - think the
way HE thinks.
2. Presenting in plain English the SQL execution problems to
inquisitive but less-technical folks, from VPs and directors to functional
users.
My experience has thought me that being able to understand,
interpret, assess and compare SQL execution plans is a great skill to have.
Despite being a seasoned DBA (at least I'd like to think that way after
having spent decades with Oracle), I've found plenty of new-to-me, hard to get
and harder to verify useful info in the book, such as:
a) Execution plans
stability - why does my SQL performance suddenly deteriorates and how to control
that?
b) Histograms, bind variables, statistics, and exec plans - all are
interrelated, but how - get ready for surprises.
c) Transactions - Oracle
isolation levels form default up and what are possibilities for tuning.
d)
Instrument your SQL code - Oracle RDBMS has done it for the world's benefit; you
can do the same!
e) Index partitioning and access paths - From Fast Full
Scan to Virtual and Invisible Indexes, amazing stuff!
f) Index/attribute
cardinality and selectivity - cousins or in-laws?
g) All about joins -
Hash-joins through Anti-joins
h) Analytic functions, Model clause - Cube,
Rank and Spreadsheets, just to get you started.
g) You are on SQL code quest
- get your offense and defense lines in order - brief but very insightful.
Help other customers find the most helpful reviews
Was this review helpful to
you? Yes No