Pro Oracle SQL (Expert's Voice in Oracle) and over 2 million other books are available for Amazon Kindle . Learn more


or
Sign in to turn on 1-Click ordering.
Trade in Yours
For a 6.00 Gift Card
Trade in
More Buying Choices
Have one to sell? Sell yours here
Sorry, this item is not available in
Image not available for
Colour:
Image not available

 
Start reading Pro Oracle SQL (Expert's Voice in Oracle) on your Kindle in under a minute.

Don't have a Kindle? Get your Kindle here, or download a FREE Kindle Reading App.

Pro Oracle SQL (Expert's Voice in Oracle) [Paperback]

Karen Morton

RRP: 39.49
Price: 24.93 & FREE Delivery in the UK. Details
You Save: 14.56 (37%)
o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o
In stock.
Dispatched from and sold by Amazon. Gift-wrap available.
Want it tomorrow, 24 Aug.? Choose Express delivery at checkout. Details

Formats

Amazon Price New from Used from
Kindle Edition 23.68  
Paperback 24.93  
Trade In this Item for up to 6.00
Trade in Pro Oracle SQL (Expert's Voice in Oracle) for an Amazon Gift Card of up to 6.00, which you can then spend on millions of items across the site. Trade-in values may vary (terms apply). Learn more

Book Description

15 Dec 2010 1430232285 978-1430232285
Pro Oracle SQL unlocks the power of SQL in the Oracle Database one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language features, learn the supporting features that Oracle provides to help use the language effectively, and learn to think and work in sets. Karen Morton and her team help you master powerful aspects of Oracle SQL not found in competing databases. You'll learn analytic functions, the MODEL clause, and advanced grouping syntax features that will help in creating good queries for reporting and business intelligence applications. Pro Oracle SQL also helps you minimize parsing overhead, read execution plans, test for correct results, and exert control over SQL execution in your database. You'll learn when to create indexes, how to verify that they make a difference, how to use SQL Profiles to optimize SQL in packaged applications, and much more. You'll also understand how SQL is optimized for working in sets, and that the key to getting accurate results lies in making sure that queries ask clear and precise questions. What's the bottom-line? Pro Oracle SQL helps you work at a truly professional level in Oracle dialect of SQL. You'll master the language, the tools to work effectively with the language, and the right way to think about a problem in SQL. Pro Oracle SQL helps you rise above the crowd to provide stellar service in your chosen profession. Endorsed by the OakTable Network, a group of Oracle technologists well-known for their rigorous and scientific approach to Oracle Database performance Comprehensive goes beyond the language with a focus on what you need to know to write successful queries and data manipulation statements. What you'll learn Master powerful SQL features implemented only in Oracle Database Read and interpret SQL execution plans Quickly diagnose and fix badly performing SQL Control execution plans through hi

Frequently Bought Together

Pro Oracle SQL (Expert's Voice in Oracle) + Mastering Oracle SQL
Buy the selected items together
  • Mastering Oracle SQL 18.18


Product details


More About the Authors

Discover books, learn about writers, and more.

Product Description

About the Author

Karen Morton is a consultant and educator specializing in application optimization in both shoulder-to-shoulder consulting engagements and classroom settings. She is a Senior Technical Consultant for Enkitec. For more than 20 years, Karen has worked in information technology. Starting as a mainframe programmer and developer, she has been a database administrator, a data architect and now is a researcher, educator and consultant. Having used Oracle since the early 90s, she began teaching others how to use Oracle over a decade ago. Karen is a frequent speaker at conferences and user groups, an Oracle ACE, and a member of the OakTable network (an informal association of "Oracle scientists" that are well known throughout the Oracle community). She blogs at karenmorton.blogspot.com.

Inside This Book (Learn More)
Browse Sample Pages
Front Cover | Copyright | Table of Contents | Excerpt | Index
Search inside this book:

What Other Items Do Customers Buy After Viewing This Item?


Customer Reviews

There are no customer reviews yet on Amazon.co.uk.
5 star
4 star
3 star
2 star
1 star
Most Helpful Customer Reviews on Amazon.com (beta)
Amazon.com: 4.8 out of 5 stars  9 reviews
8 of 8 people found the following review helpful
5.0 out of 5 stars Master Oracle SQL and Execution Plans 23 Feb 2011
By Dean Alomerovic - Published on Amazon.com
Format: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.
8 of 9 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 16 Jan 2011
By Charles Hooper - Published on Amazon.com
Format:Paperback|Verified Purchase
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 into equivalent SQL statements and suggests looking in a 10053 trace file to see the actions performed by the optimizer.
* Page 349: NOT IN and NOT EXISTS may return different results, and are not functionally equivalent due to the different handling of NULL values.
* Page 355: Prior to 11g, anti-joins could not be performed on NOT IN queries unless the optimizer was sure that NULL values could not be returned.
* Page 378: Shows how to index NULL values in a B*Tree index by using a constant as the second column in the index.
* Page 388: Using DBMS_ROWID.ROWID_OBJECT(ROWID) to show how table rows are distributed with hash partitioning.
* Page 388: Using ORA_HASH( column_name, 31, 0) to predict the partition into which a row will be placed in a hash partitioning scheme with 32 partitions.
* Page 392: Function based indexes add a virtual column to the table, and those columns are viewable through the DBA_TAB_COLS view.
* Page 393: Shows how to create a virtual column that always returns a specified calculated value.
* Page 397: Starting in Oracle Database 11g it is possible to alter an index to make it invisible - this can be used to reduce the risks associate with just dropping the index.
* Page 403: Direct path inserts are invoked with the APPEND hint, causing rows to be inserted above the high water mark.
* Page 408: DBMS_ERRLOG.CREATE_ERROR_LOG procedure creates a logging table that is the destination for rows that fail during an INSERT, UPDATE, or DELETE when the LOG ERRORS INTO clause is included in the INSERT, UPDATE, or DELETE.
* Pages 418-421: Demonstrates with a test case that when a large percentage of rows in a table need to be updated, creating a new table and using INSERT APPEND may be much faster than a typical INSERT statement if a little down time is acceptable during the creation of the new table.
* Page 434: Definition of ACID (Atomicity, Consistency, Isolation, Durability)
* Page 436: ANSI isolation levels explained.
* Page 482: Demonstrates one of the potential problems associated with using statically defined views in a query, where a query is later extended to return additional information by joining directly to one of the base tables specified in the view.
* Page 498: Oracle 10g and 11g use rolling invalidation of cursors when statistics are collected on the objects referenced by the cursors.
* Chapter 15: Instrumenting code, comparing execution plan pre-change and post-change to determine the performance impacts of changes, regression testing of code changes, identifying methods to cause applications to fail.
* Page 501: Mentions V$SQL_OPTIMIZER_ENV and the underlying structure X$KQLFSQCE.
* Page 505: Very good explanation of adaptive cursor sharing.
* Page 510: Identifying SQL statements with large swings in execution time.
* Page 514: Bind variables are not appropriate in all situations.
* Page 514: "Hints are actually directives to the optimizer. As long as the hint is valid, the optimizer will obey it."
* Page 516: Placing the word COMMENT in front of a set of hints in a SQL statement prevents the optimizer from using the hints that follow.
* Page 517: Script to extract the OTHER_XML column from V$SQL.
* Page 527: Example of creating a stored outline from a SQL statement in the library cache.
* Page 538: SQL Profiles do not lock an execution plan in place - instead they lock a cardinality estimate adjustment in place through the use of embedded hints. It is also possible to embed specific hints into SQL statements by creating a SQL profile using the undocumented DBMS_SQLTUNE.IMPORT_SQL_PROFILE function.

Execution Plan Operations Described (after chapter 6 "SQL Execution Plans"):
* FILTER (page 195)
* GENERATE CUBE (page 198)
* WINDOW SORT (page 244)
* SQL MODEL ACYCLIC (page 272)
* SQL MODEL CYCLIC (page 273)
* SQL MODEL ORDERED (page 274)
* MAT_VIEW REWRITE ACCESS FULL (page 277)
* TEMP TABLE TRANSFORMATION (page 288)
* NESTED LOOPS ANTI, MERGE JOIN ANTI NA (page 355)
* BITMAP CONVERSION TO ROWIDS, BITMAP AND (page 381)
* COUNT STOP KEY (page 385)

Analytic and Other Advanced SQL Constructs Described:
* CUBE function (page 197)
* GROUPING (page 209)
* GROUPING_ID (page 212)
* SUM analytic function (page 225)
* MAX analytic function (page 225)
* LAG analytic function (page 227)
* LEAD analytic function (page 229)
* FIRST_VALUE analytic function (page 231)
* LAST_VALUE analytic function (page 231)
* NTH_VALUE analytic function (page 232 - new in Oracle Database 11.2)
* RANK analytic function (page 234)
* DENSE_RANK analytic function (page 235)
* ROW_NUMBER analytic function (page 236)
* RATIO_TO_REPORT analytic function (page 237)
* PERCENT_RANK analytic function (page 238)
* PERCENTILE_CONT analytic function (page 238)
* PERCENTILE_DISC analytic function (page 240)
* NTILE analytic function (page 241)
* STDDEV analytic function (page 242)
* LISTAGG analytic function (page 243 - new in Oracle Database 11.2)
* MODEL clause (page 253)
* PIVOT clause (page 284)
* SYS_CONNECT_BY_PATH (page 311)
* CONNECT_BY_ROOT operator (page 313)
* NOCYCLE parameter (page 316)
* CONNECT_BY_ISCYCLE pseudo column (page 316)
* CONNECT_BY_ISLEAF pseudo column (page 319)

Hints Described:
* NO_QUERY_TRANSFORMATION (page 40)
* NO_MERGE (page 42)
* MERGE (page 43)
* NO_PUSH_PRED (page 49)
* REWRITE (page 51)
* FULL (page 86)
* ORDERED (page 91)
* USE_NL (page 91)
* GATHER_PLAN_STATISTICS (page 167)
* PARALLEL (page 277)
* INLINE (page 286)
* MATERIALIZE (page 286)
* SEMIJOIN (page 339)
* NO_SEMIJOIN (page 339)
* ANTIJOIN (page 363)
* USE_ANTI (page 363)
* NL_AJ (page 363)
* HASH_AJ (page 365)
* INDEX (pages 374, 551)
* APPEND (page 403)
* APPEND_VALUES (page 403 - new in Oracle Database 11.2)
* QB_NAME (page 516)
* OPT_ESTIMATE (page 538)

Parameters Described:
* _UNNEST_SUBQUERY (page 47)
* DB_FILE_MULTIBLOCK_READ_COUNT (page 66)
* _ALWAYS_SEMI_JOIN (page 342)
* _ALWAYS_ANTI_JOIN, _ALWAYS_SEMI_JOIN, _OPTIMIZER_NULL_AWARE_ANTIJOIN, _OPTIMIZER_OUTER_TO_ANTI_ENABLED (page 364)
* OPTIMIZER_USE_INVISIBLE_INDEXES (page 397)
* _USE_NOSEGMENT_INDEXES (page 398)
* USE_STORED_OUTLINES (page 529)
* SQLTUNE_CATEGORY (page 539)
* OPTIMIZER_USE_SQL_PLAN_BASELINE (page 556)

Miscellaneous Useful Functions:
* ORA_HASH function (pages 95, 388 - used to predict the partition into which a row will be placed in a hash partitioning scheme)
* DBMS.APPLICATION_INFO (page 440)
* DBMS_RANDOM.VALUE (page 473)
* DBMS_UTILITY.GET_TIME and DBMS_UTILITY.GET_CPU_TIME (page 491)

While the "Pro Oracle SQL" book is an overall excellent book, clearly demonstrating that there was a great deal of care put into the book, there are a couple of small problem areas in the book (note that the authors appear to have addressed most of these issues on the errata page for the book on the Apress website):
* While not significantly affecting the comprehension of the material presented in the book, there are a couple of typos in the book. For instance, on page 9 the book states "In this book" rather than "In this chapter"; page 31 of the book uses the word "effect" rather than "affect"; and page 221 of the book demonstrates that simple connecting words were sometimes lost, "... would require multiple self-joins to [the] employees table." None of these typos change the intended message of the paragraphs. However, the writing style in some of the chapters is significantly more fluid than in other chapters.
* Pages 51-52: When the optimizer did not select to automatically use the materialized view, the author used a REWRITE hint to force the optimizer to use that materialized view. It probably would have been a good idea to mention that the optimizer likely did not automatically use the materialized view because the calculated cost for that execution plan (1935) exceeded the cost for the execution plan that directly accessed the tables (485).
* Page 73: States, "Block accesses made via an index scan are made using single-block reads." Technically, index range scans may employ multi-block reads in certain cases (such as index pre-fetching), but stating that at this point in the book might lead to unnecessary confusion.
* Page 85: A missing word might lead to misunderstanding: "However, in the cases where the number of subindexes needed would be smaller, the operation can be many times more efficient than a full scan as scanning [a] smaller [number of] index blocks can be more efficient than scanning [a] larger [number of] table blocks."
* Page 94: Missing words in an explanation regarding how hash joins work might lead to misunderstanding: "Based on table and index statistics, the table that is determined to return the fewest rows will be hashed in its entirety into memory. This hash table includes all the row data for that table..." Based on testing, it appears that the hash table only includes the selected columns and the columns that are joined to other row sources. Additionally, the row source that is hashed into the hash table might not be from a table. Including this additional detail might be too much information for the scope of the book (reference).
* Page 102: There is a risk that the full outer join Oracle syntax equivalent implementation, when the approach is applied to other data, could yield different results from the ANSI full outer join if each row returned is not unique. One way to work around that limitation is to replace the UNION with a UNION ALL and add AND E2.ROWID IS NULL to the final WHERE clause.
* Page 163: States, "The operation [in the execution plan] that is most indented is actually the first operation that will be executed. If there are multiple operations at the same level, the operations are executed in a top-down order." The Oracle Database Performance Tuning Guide for 11.2 from the Oracle documentation library states essentially the same fact, and is also incorrect. This guideline is correct in some cases, but incorrect in other cases as can be confirmed with a 10046 extended SQL trace. The first operation executed in an execution plan is actually the first operation from the top of the execution plan that has no child operations (reference reference2).
* Page 379: States, "B-tree indexes are suitable for columns with lower selectivity. If the columns are not selective enough, the index range scan will be slower. Further, less selective columns will retrieve numerous rowids from the leaf blocks leading to excessive single block access to the table." Based on the second and third quoted sentences, it appears that the author intended to state that "B-tree indexes are GENERALLY NOT suitable for columns with lower selectivity."
* Page 397: States, "There is another use case for the invisible indexes. These indexes are useful to reduce the risk while dropping unused indexes... From Oracle Database version 11g onwards, you can mark the index as invisible, wait for few weeks, and then drop the index if no process is affected with less risk." It is important that the book states "less risk" and not that there is no risk - it would have been helpful if the book discussed what risks remain (but again this might exceed the intended scope of the book). Just because an index is not used for a couple of weeks does not mean that the index will not be used during month-end closing, year-end processing, or some other infrequently occurring activity. Invisible indexes on foreign key columns may still be used to prevent table locking problems on the child table when the parent's primary key columns are updated. Additionally, the statistics from the invisible indexes may be used by the optimizer to more accurately determine cardinality estimates (reference reference2).
* Page 437: The third paragraph contains an apparent typo regarding the location of the online redo logs: "Undo blocks retain the before condition of the data, while the redo information is stored in the online redo logs in the system global area (SGA)."
* Page 484: Specifies 10044 tracing when 10046 tracing was likely intended. The third paragraph states, "I've also added code to enable 10044 tracing..." Event 10044 appears to enable tracing of free list undo operations - I believe that the author intended to write, "I've also added code to enable 10046 tracing..."
* Page 529: The outline_startup_trigger.sql script is not included in the script library for the book.
* Page 539: The create_tuning_task.sql and accept_sql_profile.sql scripts are not included in the script library for the book.

As indicated by the above, most of the problem areas are related to unintentional word substitutions. The majority of the other problem areas are cases where the authors had to draw the line of discussion at a selected level of detail in order to limit potential confusion and control the scope of the book. The remaining problem areas are minor in the overall context of the book, and might elicit a response from the authors along the lines of "I thought that I modified that phrase in draft 20 of the chapter". A fantastic Oracle SQL book that ventures well beyond simple syntax diagrams, and the book is an excellent value for the price.
3 of 3 people found the following review helpful
4.0 out of 5 stars A tool for learning to write BETTER SQL 30 Dec 2010
By Scott - Published on Amazon.com
Format:Paperback
This is not a book for learning to write SQL - you need to know how to do that before you read this book. This is a book for learning to write better SQL with Oracle. Nearly every example is accompanied by SQL that enable you to determine that this specific example is in fact better and why. So you won't just learn that SQL technique, you'll also learn to investigate other SQL statements. It is also is solely focused on Oracle - it won't help you with other DBMS's.

Ignore chapter One - it's not indicative of the rest of the book. It covers only things you ought to already know and rightly redirects you elsewhere if you don't understand any of it.

You'll want to have an Oracle system available to you as you read this book, so that you can try out the examples. It should be a system where you can freely experiment because you will want to create tables, run the SQL and then look at the explain results in the Plan tables.

I did especially like Chapter 5 - "It's about the Question" -- you do really need to understand what you are trying to retrieve before you write the query. That chapter is about determining what the question really is. This is perhaps the only chapter that is independent of the database system.

There's a lot of information in this book and many times I found I was picking up things that were new to me, even though I have been working with Oracle for some years (and SQL for many more years).
2 of 2 people found the following review helpful
5.0 out of 5 stars Great resource on Oracle sql 13 Sep 2011
By David Raj Daniel - Published on Amazon.com
Format:Paperback|Verified Purchase
Few books shaped my approach to Understand Oracle architecture and systematize Oracle tuning. 1. Expert one on one -Tom Kyte 2. Effective Oracle by design -Tom Kyte 3. Optimizing oracle performance -Cary Milsap 4.CBO fundemantals -Jonathan Lewis 5. TOP -Christian Antognini. This book also should definetly be in one's Oracle Library. It covers a good skill range. One need not be an expert to read this.It has contents which provides information to the one with Basic skills,Intermediate skills or Advanced.A good understanding of SQL is a prerequisite before understanding advanced concepts like RAC,Exadata etc.
This books does a great job in refreshing what you already know and enriching with
lot of additional information.Though all the chapeters in the book were good. The chapters from Kerry Osborne is Excellent. Any developer/DBA working on a complex production system should read Chaper 16. Plan stability and Control. Trouble shooting Oracle perfomance from Christian Antognini addresses this topic in great length ,but still there are some additional information.
The scripts provided alongside is extremely useful as well.

The chapters from the other authors were also pretty interesting and well researched.

I am planning to write another review on Expert Oracle Exadata by Kerry Osborne,Randy Johnson,Tanel Poder which is the Bible for Exadata as of today.

Overall excellent value for money.
1 of 1 people found the following review helpful
5.0 out of 5 stars Addition to your knowledge 7 May 2012
By Sergey Tatarinov - Published on Amazon.com
Format:Paperback|Verified Purchase
This book is great. If you are DBA or developer , you will find a lot of things in this book you have ever skip for latter understanding. All core aspects of explain plans and execution plans covered perfectly. Also, "thinking in sets" gives you new look to the SQL core compare to the favorite "thinking as 3GL language". Highly recommended.
Were these reviews helpful?   Let us know

Customer Discussions

This product's forum
Discussion Replies Latest Post
No discussions yet

Ask questions, Share opinions, Gain insight
Start a new discussion
Topic:
First post:
Prompts for sign-in
 

Search Customer Discussions
Search all Amazon discussions
   


Look for similar items by category


Feedback