Book Review: Beginning PL/SQL: From Novice to Professional

30 11 2009

Very Well Written, with a Lot of Advice – Some will Force the DBA to Cringe, January 25, 2009

This book is very well written, with a couple minor problems which kept it from receiving a five star rating. The book takes the approach of here is how something works (with a detailed code example), here is a problem which needs to be solved using something similar to what you have just learned, and here is how I would write the solution for the problem. The book is easily read from cover to cover through the use of the author’s humor, which seemed to dry up a bit half of the way through the book.

Comments about the book which I recorded as I read through, in no particular order:

  • Page 282 suggests sticking to a standard set of VARCHAR2 column lengths, such as 2000 for comments (4000 for international). However, doing so may lead to excessive memory consumption problems as variable anchors are used in PL/SQL modules to declare variables.
  • Testing and documentation are both demonstrated and stressed as necessary for the developer. The author states that roughly twice as much time should be spent testing a solution as the time required to code the solution.
  • The author provides a brief description of basic SQL, just in case the author’s advice of being comfortable with SQL was ignored.
  • The book provides updated content for developers using Oracle 10g.
  • Chapter titles appearing at the top of each page probably should have been labeled a little better to describe the contents of the chapter, rather than attempting to use a bit of witty humor for the chapter titles. This change would have made it easier to find a specific syntax example, although the index at the back of the book eliminates much of this being an issue.
  • The author created a PL function in one of the early chapters of the book as a shorthand method of calling DBMS_OUTPUT.PUT_LINE, and this PL function was used in many of the later chapters of the book without indicating that PL is not a built-in PL/SQL function. There is a chance that this might cause some confusion for people who attempt to use the book for reference purposes, rather than as a book which should be read cover to cover.
  • The author casually demonstrates a lot of good programming practices with Oracle databases, without drawing much attention to some of those good programming practices.
  • The author stresses modularization of program processes, if the code will be used in more than one place, that code should be stored in a PL/SQL function or procedure. Unfortunately, without much caution, there is a chance that a developer will take that message to an extreme, coding all kinds of black box type procedures which then might be called in a for loop, repeatedly sending the same query to the database rather than sending the query once and storing the result (yes, this does happen with production code).
  • Page 420 suggests creating a temporary table to store an intermediate result, using an example that if the table’s average rowsize is 297 bytes, and only 13 bytes per row are needed, that the results should be placed into a temporary table to improve performance. The author stopped short of stating that the temporary tables should be created on the fly as needed, which would definitely not be a good suggestion.

Submitted to the publisher as errata:

  • The script on page 43, despite the description, the TO_NUMBER_OR_NULL function error handler does not catch errors when non-numeric values are passed into the function (such as the letter A). The author’s code is only attempting to catch the exception INVALID_NUMBER, which is apparently insufficient on Oracle – the author later indicated that he did not know why the code did not work when placed into a package, and modified the code to catch the PL/SQL error.
  • The script on page 94 references the column WORKER_TYPE_T.WORKER_TYPE_ID, but no such column exists when the tables are created using the scripts in the script library. The downloadable script library also contains the same error. The column listed in the script does not match the ERD diagram on page 27, which shows that the script should have referenced WORKER_TYPE_T.ID.
  • The script on page 119 references the column GENDER_T.GENDER_ID, but no such column exists when the tables are created using the scripts in the script library. The downloadable script library also contains the same error. The column listed in the script does not match the ERD diagram on page 27, which shows that the script should have referenced GENDER_T.ID.
  • The scripts which create the tables assume that the tablespace “USERS” exists in the database, but the book does not mention that such a tablespace needs to exist. Some of the columns listed in the tables created by the scripts are Oracle reserved words (SELECT * FROM V$RESERVED_WORDS ORDER BY KEYWORD;), which generally should be avoided as column names. The ERD diagram on page 27 shows a couple such reserved words (ID, NAME).
  • The scripts to create the tables/indexes in the database create the objects with odd extent sizes which will contribute to fragmentation in dictionary managed tablespaces, and poor full tablescan/fast full index scan performance. The scripts on pages 29-36 create objects with 10KB extent sizes, the scripts on pages 188-190 also create objects with 10KB extent sizes, another script created one or more objects with a 100KB extent size, while the script on pages 237-238 creates a table and an index with a 1MB extent size. All objects in a single tablespace should have the same extent size (unless they are in an Oracle controlled ASSM tablespace with auto controlled extent sizes), and the extent size should be a power of 2 – the extent size controls the maximum multi-block read size, so this needs to be considered as well.
  • The script on pages 159-160 is described as a plain old SQL solution, which was created in an effort to show that an all SQL solution (no PL/SQL) would out perform a PL/SQL solution. It did that, but just barely. The problem is that the plain old SQL solution calls 2 PL/SQL functions for each row inserted into the WORKER_T table. A pure SQL solution could have been constructed for this particular insert which would have better demonstrated the author’s point.
  • On pages 266-268 the author attempts to force the Oracle optimizer to use an index access for a SQL statement containing “WHERE NAME LIKE ‘%DOE%’. The author stated that “the Optimizer is wrong” for selecting to use a full tablescan rather than an index type access, citing that the index type access would require the retrieval of about 1,080 4KB blocks to determine which table blocks to access, compared to the 5,500 4KB blocks during a full tablescan. On page 266 the author stated “Instead, it resorted to the worst of all options: a full table scan. Ouch!”. On page 268 the author stated “On the other hand, you’re an intelligent programmer who is much more knowledgeable and can therefore consider things like physics.” The author fails to recognize that if the table’s extent size were set at 1MB, with a 4KB block size, Oracle 10g R2 would have auto-set the db_file_multiblock_read_count to 256 (on earlier versions of Oracle the DBA could have set the same value). While the author’s forced INDEX FULL SCAN was reading the 4KB blocks one at a time, the full tablescan would have been reading up to 256 blocks at a time, in roughly the same amount of time that it would have taken to fetch a couple blocks of the index from disk. The author’s advice could put the developer reading this book at odds with a DBA who has read a couple performance tuning books and articles written by Oaktable Network members.



%d bloggers like this: