Contents

Copyright

About This Document

• Purpose of This Document
• Audience
• How This Document Is Organized

Working with Derby Properties

• Properties overview
  • Scope of Properties
  • Persistence of Properties
  • Precedence of Properties
    • Protection of Database-Wide Properties
  • Dynamic versus Static Properties
• Ways of Setting Derby Properties
  • System-Wide Properties
    • Changing the system-wide properties programmatically
    • In the derby.properties File
    • Verifying System Properties
  • Database-Wide Properties
  • In a Client/Server Environment
  • Dynamic or Static Changes to Properties
• Properties case study

Performance tips and tricks

• The tips
  • Use prepared statements with substitution parameters
  • Create Indexes, and Make Sure They're Being Used
  • Increase the Size of the Data Page Cache
  • Tune the size of database pages
    • Performance trade-offs of large pages
  • Avoid expensive queries
  • Use the appropriate getXXX and setXXX methods for the type
  • Tune database booting/class loading
• More tips
  • Shut down the system properly
  • Put Derby first in your class path

Tuning databases and applications

• Application and database design issues
  • Avoiding table scans of large tables
    • Index, Index, Index
    • 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
  • How You Use It
  • Analyzing the Information
    • Statistics Timing
    • Statement Execution Plan
    • Optimizer estimates

DML statements and performance

• Performance and optimization
  • Index use and access paths
    • What Is an Index?
    • What's Optimizable?
    • Covering Indexes
    • 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
    • About the Optimizer's Choice of Join Strategy
    • About the Optimizer's Choice of Sort Avoidance
    • About the System's Selection of Lock Granularity
    • About the Optimizer's Selection of Bulk Fetch
• Locking and Performance
  • Transaction-Based Lock Escalation
  • LOCK TABLE Statement
• Non-Cost-Based Optimizations
  • Non-Cost-Based Sort Avoidance (Tuple Filtering)
    • DISTINCT
    • GROUP BY
  • The MIN() and MAX() Optimizations

Selectivity and Cardinality Statistics

• Overview
  • 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

Derby Properties

• Scope of Derby Properties
• Derby Properties
  • derby.authentication.ldap.searchAuthDN
  • derby.authentication.ldap.searchAuthPW
  • derby.authentication.ldap.searchBase
  • derby.authentication.ldap.searchFilter
  • derby.authentication.provider
  • derby.authentication.server
  • derby.connection.requireAuthentication
  • derby.database.defaultConnectionMode
  • derby.database.forceDatabaseLock
  • derby.database.fullAccessUsers
  • derby.database.propertiesOnly
  • derby.database.readOnlyAccessUsers
  • derby.infolog.append
  • derby.language.logStatementText
  • derby.locks.deadlockTimeout
  • derby.locks.deadlockTrace
  • derby.locks.escalationThreshold
  • derby.locks.monitor
  • derby.locks.waitTimeout
  • derby.storage.initialPages
  • derby.storage.minimumRecordSize
  • derby.storage.pageCacheSize
  • derby.storage.pageReservedSpace
  • derby.storage.pageSize
  • derby.storage.tempDirectory
  • derby.stream.error.file
  • derby.stream.error.logSeverityLevel
  • derby.system.home
  • derby.user.UserName

Appendix A. Internal Language Transformations

• Predicate Transformations
  • BETWEEN Transformations
  • LIKE Transformations
    • Character String Beginning with Constant
    • Character String Without Wildcards
    • Unknown Parameter
  • Static 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

Index