SQL Tuning

After you have determined the overall locking and I/O strategies, make sure that the individual SQL statements run as efficiently as possible.

The following sections describe how to streamline your SQL statements:

• Tune Statements and Use Indexes

• Collect and Evaluate Sampling of Runtimes for SQL Statements

• Creating Tables Without Indexes for Performance When Loading Data

• Select the Appropriate Type of Index

• Size Hash Indexes Appropriately

• Use Foreign Key Constraint Appropriately

• Compute Exact or Estimated Statistics

• Update Table Statistics for Large Tables in Parallel

• Create Script to Regenerate Current Table Statistics

• Control the Invalidation of Commands in the SQL Command Cache

• Avoid ALTER TABLE

• Avoid Nested Queries

• Prepare Statements in Advance

• Avoid Unnecessary Prepare Operations

• Store Data Efficiently with Column-Based Compression of Tables

• Control Read Optimization During Concurrent Write Operations

• Choose SQL and PL/SQL Timeout Values