Data Management - Foundations - D426 (5/6)

hash function (Hash table)

The ____ function computes the bucket containing the row from the hash key.

row-oriented storage (Storage media)

To minimize block transfers, relational databases usually store an entire row within one block, which is called ___-________ storage.

hash key (Hash table)

The ____ key is a column or group of columns, usually the primary key.

modulo function (Hash table)

The ______ function is a simple hash function with four steps.

Access time (Storage media)

______ time is the time required to access the first byte in a read or write operation.

Transfer rate (Storage media)

________ rate is the speed at which data is read or written, following initial access.

Volatile memory (Storage media)

________ memory is memory that is lost when disconnected from power.

Non-volatile memory (Storage media)

___-________ memory is retained without power.

Main memory / random-access memory (RAM)(Storage media)

____ ______, is the primary memory used when computer programs execute.

Flash memory / solid-state drive (SSD) (Storage media)

_____ ______, is less expensive and higher capacity than main memory.

Magnetic disk / hard-disk drive (HDD) (Storage media)

________ ____, also called hard-disk drive (HDD), is used to store large amounts of data.

sectors (Storage media)

Magnetic disk groups data in _______, traditionally 512 bytes per sector but 4 kilobytes with newer disk formats.

pages (Storage media)

Flash memory groups data in _____, usually between 2 kilobytes and 16 kilobytes per page.

block (Storage media)

Databases and file systems use a uniform size, called a _____, when transferring data between main memory and storage media.

column-oriented/columnar storage (Storage media)

______-________, each block stores values for a single column only.

table structure (Heap table)

A _____ structure is a scheme for organizing rows in blocks on storage media.

heap table (Heap table)

In a ____ table, no order is imposed on rows.

sorted table / sort column (Sorted table)

In a ______ table, the database designer identifies a ____ column that determines physical row order.

hash table (Hash table)

In a ____ table, rows are assigned to buckets.

bucket (Hash table)

A ______ is a block or group of blocks containing rows.

dynamic hash function (Hash table)

A _______ hash function automatically allocates more blocks to the table, creates additional buckets, and distributes rows across all buckets. With more buckets, fewer rows are assigned to each bucket and, on average, buckets contain fewer linked blocks.

Table clusters / multi-tables (Table clusters)

_____ _______, interleave rows of two or more tables in the same storage area.

cluster key (Table clusters)

Table clusters have a _______ key, a column that is available in all interleaved tables.

single-level index (Single-level indexes)

A ______-_____ index is a file containing column values, along with pointers to rows containing the column value.

multi-column index (Single-level indexes)

In a _____-______ index, each index entry is a composite of values from all indexed columns. In all other respects, _____-______ indexes behave exactly like indexes on a single column.

table scan (Query processing)

A _____ scan is a database operation that reads table blocks directly, without accessing an index.

index scan (Query processing)

An _____ scan is a database operation that reads index blocks sequentially, in order to locate the needed table blocks.

Hit ratio/filter factor/selectivity (Query processing)

___ _____, is the percentage of table rows selected by a query.

binary search (Binary search)

______ ______, the database repeatedly splits the index in two until it finds the entry containing the search value:.

primary index / clustering index (Primary and secondary indexes)

A _______ _____, is an index on a sort column.

secondary index / nonclustering index (Primary and secondary indexes)

A_________ _____, is an index that is not on the sort column.

dense index (Primary and secondary indexes)

A _____ index contains an entry for every table row.

sparse index (Primary and secondary indexes)

A ______ index contains an entry for every table block.

Multi-level index(Multi-level indexes)

A _____-_____ index stores column values and row pointers in a hierarchy.

fan-out (Number of levels)

The number of index entries per block is called the ___-___of a multi-level index.

branch (Balanced indexes)

Each path from the top-level block to a bottom-level block is called a ______.

balanced / imbalanced (Balanced indexes)

Multi-level indexes are called ________ when all branches are the same length and __________ when branches are different lengths.

B+tree (B-tree and B+tree indexes)

______. All indexed values appear in the bottom level. Pointers to table blocks appear only in the bottom level. Since some indexed values also appear in higher levels, values are occasionally repeated in the index.

B-tree (B-tree and B+tree indexes)

______. If an indexed value appears in a higher level, the value is not repeated at lower levels. Instead, a pointer to the corresponding table block appears in the higher level along with the value.

hash index (Hash indexes)

In a ____ index, index entries are assigned to buckets.

bucket(Hash indexes)

A ______ is a block or group of blocks containing index entries.

hash function(Hash indexes)

The bucket containing each index entry is determined by a ____ function, which computes a bucket number from the value of the indexed column.

hash key (Hash indexes)

A hash ___ is a column that determines the physical location of rows in a hash table.

bitmap index (Bitmap indexes)

A ______ index is a grid of bits:.

physical index (Logical indexes)

A single- or multi-level index normally contains pointers to table blocks and is called a ________ index.

logical index (Logical indexes)

A _______ index is a single- or multi-level index in which pointers to table blocks are replaced with primary key

function index (Function indexes)

In a ________ index, the database designer specifies a function on the column value. Index entries contain the result of the function applied to column values, rather than the column values.

tablespace (Tablespaces)

A __________ is a database object that maps one or more tables to a single file.

fragmented (Tablespaces)

As files are updated, blocks become scattered, or __________, across many tracks.

partition (Partitions)

A _________ is a subset of table data. One table has many partitions that do not overlap and, together, contain all table data.

horizontal partition (Partitions)

A __________ partition is a subset of table rows.

vertical partition (Partitions)

A ________ partition is a subset of table columns.

shard (Partitions)

Like a partition, a _____ is a subset of table data, usually a subset of rows rather than columns. Unlike partitions, which are stored on different storage devices of a single computer, _____s are stored on different computers of a distributed database.

partition expression (Partition types)

To partition a table, the database administrator specifies a partition __________ based on one or more partition columns.

partition columns (Partition types)

The partition expression may be simple, such as the value of a single partition column, or a complex expression based on several partition _______.

range partition (Partition types)

A _____ partition associates each partition with a range of partition expression values. The VALUES LESS THAN keywords specify the upper bound of each range. The MAXVALUE keyword represents the highest column value, and VALUES LESS THAN MAXVALUE specifies the highest range. Each partition is explicitly named by the database administrator.

list partition (Partition types)

A ____ partition associates each partition with an explicit list of partition expression values using the VALUES IN keywords. Like a range partition, each partition is explicitly named.

hash partition (Partition types)

A ____ partition requires a partition expression with positive integer values. The database administrator specifies the number of partitions, N, and partitions are automatically named p0 through p(N-1). The partition number for each row is computed as: (partition expression value) modulo N.

key partition (Partition types)

A ___ partition is similar to a hash partition, except the partition expression is determined automatically by the database.

Logical design (Physical design)

_______ design specifies tables, columns, and keys.

Physical design (Physical design)

________ design specifies indexes, table structures, and partitions. ________ design affects query performance but never affects query results.

storage engine /storage manager (Physical design)

A _______ ______ translates instructions generated by a query processor into low-level commands that access data on storage media. \n\n_______ ______ support different index and table structures, so physical design is dependent on a specific storage engine.

CREATE INDEX (statements)

The _______ _____ statement creates an index by specifying the index name and table columns that compose the index.

DROP INDEX (statements)

The ____ _____ statement deletes a table's index.

SHOW INDEX (statements)

The ____ _____ statement displays a table's index.

CREATE INDEX IndexName \n\nON TableName (Column1, Column2, ..., ColumnN);

Create an index

DROP INDEX IndexName ON TableName;

Delete an index

SHOW INDEX FROM TableName;

Show an index

EXPLAIN (statement)

The _______ statement generates a result table that describes how a statement is executed by the storage engine.

slow query log(Physical design process)

The MySQL ____ _____ log is a file that records all long-running queries submitted to the database.

Physical design process (Physical design process)

1. Create initial physical design \n\n2. Identify slow queries \n\n3. EXPLAIN slow queries \n\n4. Create and drop indexes \n\n5. Partition large tables