• Copyright
• License
• About this guide
  • Purpose of this guide
  • Audience
  • How this guide is organized
• Performance tips and tricks
  • Use prepared statements with substitution parameters
  • Create indexes, and make sure they are being used
  • Ensure that table statistics are accurate
  • Increase the size of the data page cache
  • Tune the size of database pages
    • Performance trade-offs of large pages
      • When large page size does not improve performance
      • When large page size is not desirable
  • Avoid expensive queries
  • Use the appropriate getXXX and setXXX methods for the type
  • Tune database booting/class loading
  • Avoid inserts in autocommit mode if possible
  • Improve the performance of table functions
  • Configure Derby to use an in-memory database
  • Increase the concurrency of sequences
  • Shut down the system properly
  • Put Derby first in your classpath
• Tuning databases and applications
  • Application and database design issues
    • Avoiding table scans of large tables
      • Always create indexes
        • Create useful indexes
        • Make sure indexes are being used, and rebuild them
        • Think about index order
        • Think about join order
        • Decide whether a descending index would be useful
      • Prevent the user from issuing expensive queries
    • Avoiding compiling SQL statements
      • Using the statement cache
    • Shielding users from Derby class-loading events
  • Analyzing statement execution
  • Working with RunTimeStatistics
    • Overview of RunTimeStatistics
    • How you use the RUNTIMESTATISTICS attribute
    • How you use the XPLAIN style
    • Analyzing the information
      • Statistics timing
      • Statement execution plan
      • Optimizer estimates
      • Optimizer overrides
      • Understanding XPLAIN style database tables
• DML statements and performance
  • Performance and optimization
    • Index use and access paths
      • What is an index?
      • What's optimizable?
        • Directly optimizable predicates
        • Indirectly optimizable predicates
        • Joins
      • Covering indexes
        • Single-column index examples
        • Multiple-column index example
      • Useful indexes can use qualifiers
      • When a table scan is better
      • Indexes have a cost for inserts, updates, and deletes
    • Joins and performance
      • Join order overview
      • Join strategies
    • Derby's cost-based optimization
      • About the optimizer's choice of access path
      • About the optimizer's choice of join order
        • Join order case study
      • About the optimizer's choice of join strategy
      • About the optimizer's choice of sort avoidance
        • Cost-based ORDER BY sort avoidance
      • About the system's selection of lock granularity
        • How the system makes its decision if it has a choice
        • Lock escalation threshold
      • About the optimizer's selection of bulk fetch
  • Locking and performance
    • Transaction-based lock escalation
    • Locking a table for the duration of a transaction
  • Non-cost-based optimizations
    • Non-cost-based sort avoidance (tuple filtering)
      • DISTINCT
        • Quick DISTINCT scans
      • GROUP BY
    • The MIN() and MAX() optimizations
  • Overriding the default optimizer behavior
• Selectivity and cardinality statistics
  • Determinations of rows scanned from disk for a table scan
    • How the optimizer determines the number of rows in a table
  • Estimations of rows scanned from disk for an index scan
    • Queries with a known search condition
    • Queries with an unknown search condition
  • Statistics-based versus hard-wired selectivity
    • Selectivity from cardinality statistics
    • Selectivity from hard-wired assumptions
  • What are cardinality statistics?
  • Working with cardinality statistics
    • When cardinality statistics are automatically updated
    • When cardinality statistics go stale
• Internal language transformations
  • Predicate transformations
    • BETWEEN transformations
    • LIKE transformations
      • Character string beginning with constant
      • Character string without wildcards
      • Unknown parameter
    • Simple IN predicate transformations
    • NOT IN predicate transformations
    • OR transformations
  • Transitive closure
    • Transitive closure on join clauses
    • Transitive closure on search clauses
  • View transformations
    • View flattening
    • Predicates pushed into views or derived tables
  • Subquery processing and transformations
    • Materialization
    • Flattening a subquery into a normal join
    • Flattening a subquery into an EXISTS join
    • Flattening VALUES subqueries
    • DISTINCT elimination in IN, ANY, and EXISTS subqueries
    • IN/ANY subquery transformation
  • Outer join transformations
  • Sort avoidance
    • DISTINCT elimination based on a uniqueness condition
    • Combining ORDER BY and DISTINCT
    • Combining ORDER BY and UNION
  • Aggregate processing
    • COUNT(nonNullableColumn)
• Trademarks