Oracle SQL and PL/SQL Optimization for Developers

• Introduction
  • Why This Guide?
  • System and User Requirements
  • Notes
• SQL
  • SQL Basics
    • Style Guide
      • Conventions
      • Capitalization
      • Semicolons
      • Asterisks
      • Thrift
      • Aliases
      • Comments
      • Constraints
      • Respect
      • Formatting
      • Coding Guidelines
    • Query Processing Order
  • Execution Plans
    • Explain Plan
      • Cardinality
      • Access Methods
      • Join Methods
      • Join Types
      • Join Orders
      • Partition Pruning
      • Parallel Execution
    • Adaptive Query Optimization
  • Indexes
    • Developer or Administrator
    • Access Paths and Indexes
    • Statistics
    • Predicates: Equality before Inequality
    • Predicates: LHS vs RHS
    • Function-Based Indexes and NULLs
    • Predicates: WHERE clause
    • Full Table Scans
    • Top-N Queries and Paginations
    • Index-Organized Tables
    • Bitmap Indexes
  • Subqueries
    • Scalar Subqueries
    • Nested and Correlated Subqueries
    • Subquery Unnesting
    • Combined Nested Subqueries
    • Subqueries with DISTINCT
    • Inline Views and Factored Subqueries
  • Joins
    • Nested Loops
    • Hash Join
      • Join Orders and Join Trees
      • Partitioned Hash Joins
    • Sort-Merge Join
    • Performance: ON vs WHERE
  • Hints
    • When To Use Hints
    • When Not To Use Hints
    • Named Query Blocks
    • Global Hints
    • Types of Hints
      • Optimization Goals
      • Optimizer Hints
      • Access Path Hints
      • Join Order Hints
      • Join Operation Hints
      • Parallel Execution Hints
      • Query Transformation Hints
      • Miscellaneous Hints
    • Optimization Techniques
• PL/SQL
  • Compilation
  • Bind Variables
    • PL/SQL Variables
    • Bind Peeking
    • Adaptive Cursor Sharing
    • Generic Static Statements
  • Loops, Cursors, and Bulk Operations
    • Collections
    • Performance Comparisons
      • Explicit vs Implicit Cursors
      • The Impact of Context Switches
      • Table Functions
    • Caveats
  • Caching
    • Side Effects
    • Alternatives
      • DETERMINISTIC Functions
      • The RESULT_CACHE Option
      • DETERMINISTIC vs RESULT_CACHE
    • The UDF Pragma
    • The NOCOPY Directive
• Database Design
  • Partitioning
    • Partitioned Indexes
    • Caveats
    • Recommendations
      • Single-Level Partitioning
      • Composite Partitioning
      • Prefixed vs Non-Prefixed Local Indexes
      • Partitioned vs Non-Partitioned Global Indexes
  • Compression
    • Compression Methods
      • BASIC and OLTP
      • Hybrid Columnar Compression
    • Performance Considerations
      • Size Reduction
      • CPU Overhead
• Glossary