Contents
Unnecessary Function Execution
Chapter 2: Dynamic SQL: Handling the Unknown
Performance and Resource Utilization
Comparing Implementations of Dynamic SQL
Chapter 3: PL/SQL and Parallel Processing
Laws Affecting Parallel Processing
Parallel vs. Distributed Processing
Parallel Hardware Architectures
Candidate Workloads for Parallel Processing
Parallelism and Non-OLTP Workloads
The MapReduce Programming Model
Processes Available for Parallel Activities
Using Parallel Execution Servers for MapReduce
Parallel Pipelined Table Functions Summary
Chapter 4: Warnings and Conditional Compilation
What Part of the Code is Running?
Benefits of Preprocessing Code
Final Words on Conditional Compilation
Chapter 5: PL/SQL Unit Testing
utPLSQL: Working with Command Line Code
Preparing and Maintaining the Unit Test Environment
Creating the Unit Test Repository
Maintaining the Unit Test Repository
Creating the First Implementation
Adding Startup and Teardown Processes
Collecting Code Coverage Statistics
Broadening the Scope of the Tests
Creating a Library of Components
Exporting, Importing, and Synchronizing Tests
Running Tests from the Command Line
Chapter 6: Bulk SQL Operations
Setting for the Examples in this Chapter
Getting Started with BULK Fetch
Three Collection-Style Datatypes
Monitoring Bulk Collect Overheads
Refactoring Code to Use Bulk Collect
Getting Started with Bulk Bind
Measuring Bulk Binding Performance
A Justification for Massive Collections
The Real Benefit: Client Bulk Processing
What This Chapter Will (and Will Not) Cover
Chapter 8: Contract-Oriented Programming
Enforcing Contracts Using ASSERT
Contract-Oriented Function Prototype
Example: Testing Odd and Even Integers
FUNCTION RETURN BOOLEAN Not-NULL
Check Functions: RETURN TRUE OR ASSERTFAIL
Assert Preconditions Rigorously
Adopt Function-Based Interfaces
Regression Test Your Postconditions
Avoid Correctness-Performance Tradeoffs
Oracle 11g Optimized Compilation
The Cost of Using PL/SQL Functions in SQL
Reducing the Cost of PL/SQL Functions
Chapter 10: Choosing the Right Cursor
The Anatomy of an Explicit Cursor
Explicit Cursors and Bulk Processing
The Anatomy of an Implicit Cursor
The Implicit Cursor and the Extra Fetch Theory
Cursor Variable Restrictions Laundry List
Chapter 11: PL/SQL Programming in the Large
The Database as PL/SQL-Based Application Server
Case Study: The Avaloq Banking System
Strengths of Business Logic in the Database with PL/SQL
Limits of the Database as PL/SQL-Based Application Server
Requirements of Programming in the Large
Uniformity through Conventions
Pre- and Suffixes for PL/SQL Identifiers
Modularization of Code and Data
Packages and Associated Tables as Modules
Modules Containing Multiple Packages or Submodules
Modularization with Schemas vs. within Schemas
Object-Oriented Programming with PL/SQL
Object-Oriented Programming with User-Defined Types
Object-Oriented Programming with PL/SQL Records
Chapter 12: Evolutionary Data Modeling
Lessons from Two Decades of System Development
The Database and Agile Development
Creating an Access Layer via PL/SQL
Using PL/SQL with Evolutionary Data Modeling
Chapter 13: Profiling for Performance
Extended SQL Trace Data (Event 10046)
Instrumentation Library for Oracle (ILO)
Chapter 14: Coding Conventions and Error Handling
Local Procedures and Functions
Chapter 15: Dependencies and Invalidations
Adding Components into Packages
Forcing Dependency in Triggers