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