Query processing/optimization
Database schema before class: database_day19_end_of_class.sql
customers_schema.sql
Code from class
Slides from class
Database notes
Database performance tuning
A set of activities and procedures designed to reduce the response time of a database system — that is, to ensure that an end-user query is processed by the DBMS in the minimum amount of time.
SQL performance tuning
Activities to help generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources on the server side.
Disk cache
A shared, reserved memory area that stores the most recently read disk blocks in RAM. Also called a buffer cache.
SQL cache
A shared, reserved memory area that stores the most recently executed SQL command. Also called a procedure cache.
Query optimizer
A DBMS process that analyzes SQL queries and finds the most efficient way to access the data. The query optimizer generates the access or execution plan for the query.
Automatic query optimization
A method by which a DBMS find the most efficient execution of a query.
Manual query optimization
An operation override that requires the end user or programmer to define the execution of query.
Static query optimization
A query optimization mode in which the access path to the database is predetermined at compile time.
Dynamic query optimization
The process of determining the database access strategy at run time, using the most up-to-date information about the database.
Rule-based query optimization algorithm
A query optimization method that uses preset rules and costs to determine the best approach to executing a query.
Cost-based optimization algorithm
A run-time query optimization model that uses statistics about the objects being accessed, including number of rows, indexes available, index sparsity, ad so on.
Database statistics
In query optimization, measurements about database objects such as the number of rows in a table, number of disk blocks used, maximum and average row length. Such statistics provide a snapshot of the database characteristics and are used by cost-based query optimizers.
Access plan (execution plan)
A set of instructions generated by query optimizer that determines the order in which a query will access the database at run time.
Optimizer hints
Special instructions provided by the programmer for the query optimizer that are embedded in SQL commands.
Index selectivity
A measure of how likely an index is to be used in query processing.