Oracle SQL High-performance Tuning Kit [Paperback]

This is a comprehensive quide to writing SQL code that's optimized for performance. It includes a unique set of software tools on CD-ROM for benchmarking SQL performance.

(NOTE: Each chapter begins with an Introduction and concludes with a Chapter Summary.)

Introduction

I. THE THEORY OF SQL TUNING.

1. Introduction to SQL Tuning.

Why Tune SQL? The SQL Tuning Process.

2. Review of SQL.

History of SQL. Types of SQL Statements. Query Operations. Views. NULLs and Three-valued Logic. Transactions. Oracle Extensions to the ANSI Standard. Some Comments on the SQL Non-Procedural Approach.

3. SQL Processing.

Overview of SQL Processing. Parsing. Executing SQL. Query Optimization.

4. Principles of Indexing and Clustering.

The B*-Tree Index. Index Clusters. Hash Clusters. Bitmapped Indexes. Index Only Tables.

5. Tracing Oracle SQL.

EXPLAIN PLAN. Using SQL Trace. Using the AUTOTRACE Facility in SQL*PLUS.

II. PRACTICAL SQL TUNING

6. Tuning Table Access.

Full Table Scans and Index Lookups. Alternatives to Full Table Scans. Optimizing Index Lookups. Optimizing Table Scans. Using Hints to Influence an Access Path.

7. Optimizing Joins and Subqueries.

Choosing the Best Join Method. Choosing the Best Join Order. Optimizing Joins with Index Clusters. Outer Joins. Star Joins. Hierarchical Queries. Simple Subqueries. Subqueries Involving the IN Operator. Correlated Subqueries.

8. Optimizing Sorting and Grouping.

Sort Operations. Aggregate Operations. Set Operations. Intersect. Minus.

9. Optimizing Data Manipulation Statements.

Optimizing Individual DML Statements. Optimizing Transactions. Other DML Performance Issues. Locking.

10. Using and Tuning PL/SQL.

Performance Characteristics of PL/SQL. USING PL/SQL in Place of "Standard" SQL. Optimizing PL/SQL.

11. Miscellaneous Topics.

Optimizing Views. Using Snapshots. Distributed SQL. Sequences. Using Decode. Data Definition Language.

12. Parallel SQL.

Understanding Parallel SQL. Parallel Query Option. Examples of Parallel Queries. Parallel DDL and DML.

13. SQL Tuning Case Studies.

Case Study 1: Using Concatenated Indexes. Case Study 2: The Rule Based Optimizer, Index Merges and Bind Variables. Case Study 3: The Slowly Degrading Query. Case Study 4: A Number Range Lookup. Case Study 5: The First Rows Approach.

III. BEYOND SQL TUNING.

14. Data Model and Application Design.

Building Tuning into the Design Process. Designing an Efficient Physical Data Model. Application Design.

15. Building A High-Performance Oracle Database Server.

A Review of the Oracle Architecture. Sizing the Host Computer. Building the Oracle Database.

16. Tuning the Database Server.

Evaluating Operating System Performance. Recognizing Oracle Database Bottlenecks.

Appendix A : Reference. Appendix B: Glossary. Appendix C: Configuring Development Tools. Appendix D: Oracle Server Configuration. Appendix E: Differences Between Oracle Versions. Appendix F: Bibliography and Resources. Appendix G: Contents of the CD-ROM. Index.