Oracle SQL Recipes: A Problem-Solution Approach (Expert's Voice in Oracle)

Last November I pre-ordered this book, hoping that it would pick up where the "Mastering Oracle SQL and SQL*Plus" book ended (at roughly Oracle Database release 10.1.0.3). The expectations were very high, and this book delivers for the most part. Some parts of the book left me scratching my head, thinking that this SQL statement or paragraph just cannot be correct. Even when taking those sections into account, this book conveys a significant amount of practical, useful information.The book's format of problem statement, solution, and how/why the solution works is the key to the success of this book. You will not find page after page of SQL statement syntax. The range of problem statements covered in this compact 500 page book is impressive, touching on a wide range of topics:* Basic SELECT statements* Manipulating numbers, dates, and character data* Transaction management* XML data* Partitioning* Analytic functions* Regular expressions* Performance tuning* Database troubleshooting* Creating databases, tablespaces, tables, indexes, and constraintsPositives:* Thoroughly answers the question of why it is important to learn Oracle's dialect of SQL. The book provides 233 problem situations, most with more than one variation of the problem along with the SQL statement for each variation.* Includes interesting side stories that are not necessarily related to SQL coding, but help to keep the reader interested in the book contents. For example, a side story titled "Beer and Statistics Do Mix" that describes the invention of T-Tests.* Typically states when a feature requires the Enterprise Edition or an additional cost license beyond the Enterprise Edition (Tuning Pack, for example).* Most, if not all of the code samples from the book may be downloaded from the Apress website.* Describes the LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, SUM, MIN, MAX, NTILE and a couple of the other common analytic functions. Does not cover all of the analytic functions, for example: RATIO_TO_REPORT, FIRST_VALUE, LAST_VALUE, PERCENTILE_DISC, etc.* Recipe 5-8 shows how to create a PL/SQL function that converts from base 10 to binary, octal, and hex.* Recipe 11-9 shows an impressive example that creates a HTML chart from a SQL statement. It might be interesting to see this example extended to use floating objects with absolute positioning rather than HTML tables.* Recipes 12-3 and 12-4 include PL/SQL functions that help to determine if a VARCHAR2 value can be safely converted to a NUMBER or DATE.Problems (in order, not all code samples were tested):* In recipe 1-1, retrieving data from a table, the authors mentioned "currently set schema," a concept which probably would have been much better to introduce in a later recipe. Especially in the early sections of the book, it seems that the authors struggled with what concepts to introduce early and what concepts required a more establish foundation. As such, people just learning SQL, or even just Oracle's dialect, will struggle with the early sections of the book.* Several recipes (1-4, 2-11, 2-14, 2-15, 2-16, 6-5, 6-14) use implicit date conversions, or explicit varchar to date conversions without specifying the format of the date contained in the varchar. This is a very poor practice to teach to people who are just beginning the process of learning SQL. A change in the NLS parameters, or even a change in the connection method from Oracle's ODBC driver to Oracle's OLEDB driver is sufficient to cause such SQL statements to fail. Unfortunately, the TO_DATE function was not introduced until chapter 6.* Page 26 incorrectly stated, "for performance reasons, Oracle will implicitly sort the data to match the grouping desired." As of Oracle Database 10.1, more often than not a hashing algorithm will be used to perform GROUP BY operations, which means that there will be no implicit sort of the data.* Recipe 3-5 attempted to explain when to use three different syntaxes to delete rows from a table (IN, EXISTS, and SELECT with USING clause). As may be confirmed with a 10053 trace file, the optimizer will often automatically re-write SQL statements from one form to another, for example from an IN clause to a standard join, or an EXISTS clause to a standard join. The authors probably should have mentioned the automatic query re-write capability of the cost-based optimizer, rather than stating something like this about an EXISTS clause example "this is not as elegant as the first solution, but might be more efficient."* Recipe 5-11 demonstrates how to use the FOR UPDATE clause of a SELECT statement and demonstrates that the second session will be blocked until the first session commits or issues a rollback. At this stage of the book the NOWAIT clause is not introduced. Unfortunately, such a demonstration might be an invitation to "lost" updates.* The query output for recipe 6-6 does not match the expected output, which may lead to confusion. The book shows that the result of the timezone conversion is "13-AUG-09 06.00.00.000000 PM AMERICA/LOS_ANGELES" when it should be "13-AUG-09 06.25.00.000000 PM AMERICA/LOS_ANGELES"* Recipe 6-12, which uses the EXTRACT function, will produce incorrect/unexpected results, resulting in the MONTHS calculation increasing by one up to 15 days earlier than should be expected if used to determine the length of time an employee has worked. That recipe would have been the perfect time to introduce the MONTHS_BETWEEN SQL function (not included in the book).* Recipe 7-1 states that Oracle will never use a non-function based index on a column when that column appears inside the INSTR function that is present in a WHERE clause. That statement is incorrect, a hint, covered in recipe 19-12, is sufficient to allow Oracle to perform an index full scan operation on that column when the only predicate in the WHERE clause includes that index's column wrapped in an INSTR function (tested on Oracle releases 10.2.0.4 through 11.2.0.1).* Recipe 8-2 states, "With the value one-third stored in each column (BINARY_DOUBLE and NUMBER datatypes), we can use the VSIZE function to show it was much more complicated to store this with decimal precision, taking nearly three times the space." There are a couple of problems with this statement. First, a BINARY_DOUBLE column will always require eight bytes to store any number, while it takes a variable amount of space to store a number in a NUMBER column (for instance, the number 1,000,000 requires fewer bytes to store than the number 333). Second, the BINARY_DOUBLE column only maintained 16 significant digits to the right of the decimal point, while the NUMBER column maintained 40 significant digits to the right of the decimal point (the bytes required can be decreased by specifying a smaller number of significant digits to the right of the decimal point).* Recipe 8-6 states "The final two implicit defaults (of a CREATE SEQUENCE clause) cause Oracle to cache 20 values and not force strict first-come, first served ordering on those who call NEXTVAL." This statement is either misleading or incorrect. The default will force the sequence numbers to be assigned to calling sessions in the sequential order specified by CREATE SEQUENCE - Oracle will not provide the numbers out of sequential order. There can be some degree of out of sequential order assignment in a RAC environment.* Recipe 9-2, which includes a SQL statement that is intended to show the objects which are locked that are blocking other sessions, has a number of problems. 1) It is not RAC aware - it should be selecting from the GV$ views, rather than the V$ views. 2) The SQL statement explicitly looks for the BLOCK column to be equal to 1 - in a RAC environment it could potentially show a value of 2. 3) When performing a self-join on V$LOCK, the query is only joining on the ID1 column of V$LOCK, while the documentation shows a self-join on that view using the ID1, ID2, and TYPE columns. 4) There appears to be a missing join condition between V$LOCK and V$LOCKED_OBJECT that results in objects being identified as contributing to the blocking of the second session, when in fact that may not be the case. 5) The query seems to assume that all locks acquired are full table locks.* Page 436 suggests checking Metalink (My Oracle Support) Doc ID 555284.1 for a script to identify indexes that should be rebuilt. I suspect that that document looked very different prior to the last modification date of May 13, 2009. The script in the document does not detect indexes that are candidates for a rebuild - the script simply outputs SQL statements to rebuild every index that is not owned by SYS or SYSTEM (and a small number of other users) into an INDX tablespace with a specified initial, next, and maxextents clause.* Recipe 18-13, in the Note section, states that when a primary key constraint is created Oracle will create a unique index with the same name as the constraint. That statement is true unless a unique or non-unique index already exists for the column or a composite index that has that column as its leading column exists. Later in the recipe it was stated that the DBA could pre-create the index that will be used to help enforce the primary key constraint, but the Note section might cause a little confusion.* Recipe 19-1, the join between V$SQL_MONITOR and V$SQL is incomplete and will result in repeated rows if there are multiple child cursors for a SQL_ID, which seems to happen frequently starting with Oracle Database 11.1.0.6 due to adaptive cursor sharing, and 11.2.0.1 due to cardinality feedback. Additionally, V$SQL_MONITOR will only be populated if the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter is set to DIAGNOSTIC+TUNING to enable the extra cost licenses (Tuning Pack, for example).* Recipe 19-3 states "the V$SESSION_LONGOPS view gives you a rough idea of how long a currently running query has left to finish." Technically, this statement is both correct and incorrect. The view provides a rough idea, based on the execution plan estimates, of how much time is left for a specific stage (step/line) in the execution plan. Entries in the V$SESSION_LONGOPS view will appear and disappear as the different stages in the execution plan execute. Most index type accesses will likely complete in less than six seconds per access, and probably will not appear in this view.* Recipe 19-4, the query may produce misleading results as the query is not time-scoped. The query does not consider that the database may have been up for several years - a query that on average consumes one second of CPU time, but was executed 100 million times since the library cache was last purged will likely be flagged as a problem, while a query that on average consumes 1,000 seconds of CPU time but has only executed 1,000 times in the last 24 hours probably will not appear in the SQL results.* Recipe 19-7 states that a PLAN_TABLE must exist, and that if it does not exist the utlxplan.sql script should be run to create a PLAN_TABLE in the user's schema. As of Oracle Database 10.1.0.1 user schemas should no longer have a PLAN_TABLE in their schema - a global temporary table owned by the SYS user is used instead.* Chapter 19 demonstrates the use of EXPLAIN PLAN, AUTOTRACE, and the EXPLAIN option of TKPROF, but the chapter does not mention that using those methods for displaying the execution plan could show the wrong execution plan.* Recipes 19-10 and 19-12 suggest that a goal of tuning is reducing the calculated COST value that appears in the execution plan, and this was accomplished in the recipes by adding an index or using an index hint. This is not a good approach to tuning; it is much better to rely on actual response time rather than the calculated cost estimates.* Recipe 20-1, the SQL statements are not time-scoped, and even if the database has only been open for a short period of time, the SQL statement could yield incorrect results because the "CPU used by this session" statistic is not updated in real-time, only when a SQL statement completes, or a fetch completes.* Page 500 suggests inspecting V$SESSION to determine the type of block contention that causes buffer busy waits. It would have been nice to see the authors provide a demonstration of this technique because from what I am able to determine, the chances of a success are extremely small using V$SESSION (I assume that the authors might be suggesting to look at either the combination of P1, P2, and P3 or the combination of ROW_WAIT_OBJ#, ROW_WAIT_FILE#, and ROW_WAIT_BLOCK#).* Page 500 states that the "SQL*Net message from client" wait event indicates possible network or middle-tier bottlenecks. Client-side "think time" is probably the most significant contributor to this wait event, and I think that it would be very difficult to demonstrate that this wait event indicates network bottlenecks.* Recipe 20-8 suggests an approach to identify those indexes that are safe to drop by enabling monitoring of the indexes. One of the problems with this approach is that the index may still be used by the cost-based optimizer even when monitoring reveals that the index is never used.* Very few cross-recipe (forward and backward) references were provided, which makes the sections of the book appear to be disconnected and stand-alone.The book attempts to cover a lot of ground. While it probably requires the authors between one and eight hours per page to research, write, proof-read, test, and format a typical page in an Apress book, the authors probably could have controlled the scope of the book a little better and spent a little more time to test and expand some of the sections of the book - the sections that discuss analytic functions, for example. I did not spend a lot of time reading through the chapters "Working with XML Data", "Partitioning", or "LOBS", so I did not comment on those sections of the book. The majority of the book is exceptional - far better than any book that is strictly a SQL reference type book, and I would not hesitate to recommend the book to others who are trying to figure out: "What do I do with SQL."

I have read through this book just about from end to end and found it invaluable.As an 'old hand' large scale mainframe system developer that came back into development just a few years back after pursuing a successful career in another field - this book really gave me considerable insight into Oracle.The 'How it Works' explanations of the examples should be part of every technical book - and so dispell the mystery that often surrounds technical subject material.I am currently also working through Steve Feuerstein's PL/SLQ definitive reference and the two really do complement each other for somebody who is coming to the subject with a wealth of legacy database system expertise who is looking to find out how to best make use of what is available today.I would love to see other books constructed in the same format - for example, for the various programming languages such as Java - books that give examples of the everyday sort of things that you have to do.Consistency is pre-requisite to Clarity and the book is consistent in its approach throughout.Well done gentlemen Oracle SQL Recipes: A Problem-Solution Approach

Good book. I have several years of experience working with the Oracle DBMS, yet I learned a few things from this book. This book documents some of the most common technical requirements and how oracle provides solutions for them, along with explanations. The writing is simple, to the point and well researched and thought out. Even if you are an experienced DBA with lots of knowledge, you can keep it in your library for quick reference. If you are a beginner to writing SQLs, you should still get the book and the first few chapters will help you start off in the field.

This book is especially useful for intermediate/advanced SQL programmers. I'm glad I bought it. It is much easier to lookup a recipe you've forgotten than to figure it out from scratch using the manuals.However, novice database programmers may be challenged. I found that out of 16 recipes in Chapter 1, half would cause problems for beginners. 2 recipes have SQL syntax errors and 6 cannot be run on the Oracle 11g example schemas without changes or additions.See the web page in my profile for the issues I found.

Good book, pretty helpful. I registered a sql class. The professor asked us to buy a textbook that would have costed me nearly $200. I used this book to complete through the entire classes.

Very good practical guide which will walk you through examples from real life. Very helpful and easy to apply solutions sorted into logical structure. I would recommend it to professionals who dont want to loose time by reading reference guides and who dont want to spend time trying to find good solutions on internet.

If you want to learn and refresh Oracle, this is must.