Databases, Chapter 11

A set of instructions generated at application compilation time that is created and managed by a DBMS. The access plan predetermines how an application’s query will access the database at run time.

A method by which a DBMS finds the most efficient access path for the execution of a query.

An index that uses a bit array (0s and 1s) to represent the existence of a value or condition.

An ordered data structure organized as an upside-down tree.

A shared, reserved memory area that stores the most recently accessed data blocks in RAM.

In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.

A query optimization mode that uses an algorithm based on statistics about the objects being accessed, including number of rows, indexes available, index sparsity, and so on.

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.

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, number of columns in each row, and number of distinct values in each column. Such statistics provide a snapshot of database characteristics.

The smallest logical unit of assignable storage space in the database. Also known as a page.

A named physical storage space that stores a database’s data. It can reside in a different directory on one or more storage locations. All data in a database is stored in data files. A typical enterprise database is normally composed of several data files. A data file can contain rows from one or more tables.

A column distribution of values or the number of different values a column can have.

Activities to ensure that clients’ requests are addressed as quickly as possible while making optimum use of existing resources.

The process of determining the SQL access strategy at run time, using the most up-to-date information about the database.

In a DBMS, the capability to automatically evaluate and update the database access statistics after each data access operation.

In a DBMS environment, refers to a contiguous collection of data blocks or pages that are assigned to a database object when it is created or when it is expanded.

In a DBMS, a logical storage space composed of one or more data files used to group related data.

A type of index based on a specific SQL function or expression.

An index based on an ordered list of hash values.

A database optimized to store large portions (if not all) of the database in primary (RAM) storage rather than secondary (disk) storage.

In a DBMS, a type of table storage organization that stores end-user data and index data in consecutive locations in permanent storage.

A measure of how likely an index is to be used in query processing.

A low-level data access operation that reads or writes data to and from computer devices.

A device used to improve throughput for input/output operations.

An operation mode that requires the end user or programmer to define the access path for the execution of a query.

A mode of generating statistical data access information for query optimization. In this mode, the DBA must periodically run a routine to generate the data access statistics—for example, running the RUNSTAT command in an IBM DB2 database.

Special instructions for the query optimizer that are embedded inside the SQL command text.

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.

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.

In query optimization, a delay introduced in the processing of an I/O operation that causes the overall system to slow down.

An acronym for Redundant Array of Independent Disks. RAID systems use multiple disks to create virtual disks (storage volumes) from several individual disks. RAID systems provide performance improvement, fault tolerance, and a balance between the two.

A query optimization mode based on the rule-based query optimization algorithm.

A query optimization technique that uses preset rules and points to determine the best approach to executing a query.

A query optimization mode in which the access path to a database is predetermined at compilation time.

A query optimization technique that uses statistical information about a database. The DBMS then uses these statistics to determine the best access strategy.

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions.

Activities to help generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.

In a DBMS, a logical storage space composed of one or more data files used to group related data.