Effective Oracle by Design Book Review

Design and Build High-Performance Oracle Applications

Effective Oracle by Design is written by Tom Kyte who is well known for his ‘Ask Tom’ column in Oracle and Magazine and his website. When I first noticed this book I expected it to be a book just about how to design an Oracle database from a design perspective, but Effective Oracle by Design does not teach you structured methodology but instead tells you how to get the most out of your database by using the correct programming techniques before you start to write your code in order to produce a high performance application.

Effective Oracle by Design starts by giving you a guide on the right approach to building applications and then it talks about the performance toolkit that you can use to improve performance with tools such as SQLPlus, Explain Plans, TKPROF, StatsPack, DBMS_PROFILER and JDeveloper. The third chapter takes you more into architectural decisions such as how to achieve high concurrency, handling storage space effectively and taking advantage of parallel operations with parallel processing in Oracle.

The Effective Administration chapter gives tips and hints on how to make your job easier as a DBA, such as using SPFILES to start the database, letting Oracle manage your data files, making sure your backups are bullet proof and letting Oracle manage your rollback segments. The following Statement Processing chapter takes you through what exactly happens when you submit your SQL to Oracle and it breaks this down into DDL and DML sections.

There is a nice chapter about how to get the most out of the cost based optimiser. The closest chapter to design principles is the Effective Schema Design chapter which introduces you to fundamental schema design principles and takes you through setting up referential integrity checks in the database; it then discusses the different Oracle table types and indexing techniques.

The final two large chapters of Effective Oracle by Design are about writing effective SQL and PLSQL. The effective SQL chapter takes you through looking at full table scans, and how to use joins between tables correctly and other topics such as using analytic SQL and how to use ROWNUM effectively in your SQL. The PLSQL chapter tells you to write as little plsql code as possible and don’t over bloat your code and also to use bulk processing feature when applicable as it has dramatic effects on the processing time of your code. The chapter finishes by discussing implicit vs. explicit cursors, and using %ROWTYPE and %TYPE.

Overall Effective Oracle by Design is a good book, the sort of book that once you have read you will hopefully have learnt a lot, well I did anyway. Effective Oracle by Design tells a lot of truths about the Oracle programming languages and also gives you a lot of examples and tips that you can use everyday. One thing Effective Oracle by Design is not is a structured methodology book about how to design databases.