• Copyright
• License
• Relationship between Java DB and Derby
• About this guide
  • Purpose of this guide
  • Audience
  • How this guide 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
      • Changing the system-wide properties by using 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 are 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
    • Avoid inserts in autocommit mode if possible
  • More tips
    • 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
      • 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 the RUNTIMESTATISTICS attribute
    • Analyzing the information
      • Statistics timing
      • Statement execution plan
      • Optimizer estimates
      • Optimizer overrides
• 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
    • Locking a table for the duration of a transaction
  • Non-cost-based optimizations
    • Non-cost-based sort avoidance (tuple filtering)
      • DISTINCT
      • 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
• 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.database.sqlAuthorization
    • derby.infolog.append
    • derby.jdbc.xaTransactionTimeout
    • derby.language.logQueryPlan
    • 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.field
    • derby.stream.error.file
    • derby.stream.error.method
    • derby.stream.error.logSeverityLevel
    • derby.system.durability
    • derby.system.home
    • derby.user.UserName
      • Caching user DNs
• 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