Data Storage

Question 1

Speed is measured as blank and blank

Answer 1

access time and transfer rate

Question 2

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

Answer 2

Access time

Question 3

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

Answer 3

Transfer rate

Question 4

Blank typically ranges from pennies to dollars per gigabyte of memory, depending on the media type.

Answer 4

Cost

Question 5

In principle, any media type can store any amount of data. In practice, blank is limited by cost.

Answer 5

capacity

Question 6

Blank is memory that is lost when disconnected from power.

Answer 6

Volatile memory

Question 7

Blank is retained without power.

Answer 7

Non-volatile memory

Question 8

Computer media vary on what four important dimensions:

Answer 8

Speed.
Cost.
Capacity.
Volatility.

Question 9

Three types of media are most important for database management:
Name them

Answer 9

Main memory, also called random-access memory (RAM)
Flash memory, also called solid-state drive (SSD)
Magnetic disk, also called hard-disk drive (HDD),

Question 10

Blank, also called random-access memory (RAM), is the primary memory used when computer programs execute. Blank is fast, expensive, and has limited capacity.

Answer 10

Main Memory

Question 11

Blank, also called solid-state drive (SSD), is less expensive and higher capacity than main memory. Writes to blank are much slower than reads, and both are much slower than main memory writes and reads.

Answer 11

Flash memory

Question 12

Blank, also called hard-disk drive (HDD), is used to store large amounts of data. Blank is slower, less expensive, and higher capacity than flash memory.

Answer 12

Magnetic Disk

Question 13

Main memory is blank

Answer 13

volatile

Question 14

Flash memory and magnetic disk are blank and therefore considered blank

Answer 14

non-volatile
storage media

Question 15

Databases store data permanently on blank and transfer data to and from blank during program execution.

Answer 15

storage media
main memory

Question 16

Main memory has an access time of blank microseconds, transfer rate of blank, cost per gb of blank, and is blank

Answer 16

.01 to .1
>10
>1
Volatile

Question 17

Flash memory has an access time or blank microseconds , transfer rate of blank gb/sec, cost per gb of blank, and is blank

Answer 17

20 to 100
.5 to 3
around .25
Non-volatile

Question 18

Magnetic disk has an access time or blank microseconds , transfer rate of blank gb/sec, cost per gb of blank, and is blank

Answer 18

5,000 to 10,000
.05 to .2
around .02
Non-volatile

Question 19

What type of memory - Reading one gigabyte takes about one second.

Answer 19

Flash memory

Question 20

what type of memory - Used to store petabytes (millions of gigabytes) of user data in the cloud.

Answer 20

Magnetic disk

Question 21

What type of memory - Upgrading from 16 to 32 gigabytes costs an extra $400 for an Apple laptop computer.

Answer 21

Main memory

Question 22

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

Answer 22

sectors

Question 23

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

Answer 23

pages

Question 24

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

Answer 24

block

Question 25

Blank is independent of storage media.

Answer 25

Block size

Question 26

Storage media are managed by blank, which convert between blocks and sectors or pages. This conversion is internal to the storage device, so the database is unaware of page and sector sizes.

Answer 26

controllers

Question 27

Blank must be uniform within a database but, in most database systems, can be specified by the database administrator

Answer 27

Block size

Question 28

Database systems typically support block sizes ranging from blank to blank

Answer 28

2 kilobytes to 64 kilobytes.

Question 29

Smaller block sizes are usually better for blank, which access a few rows per query.

Answer 29

transactional applications

Question 30

Larger block sizes are usually better for blank, which access many rows per query.

Answer 30

analytic applications

Question 31

Accessing storage media is relatively slow. Since data is transferred to and from storage media in blocks, databases attempt to minimize the number of blocks required for blank.

Answer 31

common queries

Question 32

Most relational databases are optimized for blank, which often read and write individual rows.

Answer 32

transactional applications

Question 33

To minimize block transfers, relational databases usually store an entire row within one block, which is called blank.

Answer 33

row-oriented storage

Question 34

Row-oriented storage performs best when row size is small relative to block size, for what two reasons:

Answer 34

Improved query performance.
Less wasted storage.

Question 35

When row size is small relative to block size, each block contains many blank. Queries that read and write multiple rows transfer fewer blocks, resulting in better performance.

Answer 35

rows

Question 36

Row-oriented storage wastes blank per block, since rows do not usually fit evenly into the available space. The wasted space is less than the row size. If row size is small relative to block size, this wasted space is insignificant.

Answer 36

a few bytes

Question 37

Database administrators might specify a blank for databases containing larger rows.

Answer 37

larger block size

Question 38

Sometimes a table contains a very large column, such as 1 megabyte documents or 10 megabyte images. For tables with large columns, each row usually contains a blank to the large column, which is stored in a different area.

Answer 38

link

Question 39

In blank, also called columnar storage, each block stores values for a single column only.

Answer 39

column-oriented storage

Question 40

Column-oriented storage benefits analytic applications in what two ways

Answer 40

Faster data access.
Better data compression.

Question 41

More column values are transferred per block, reducing time to blank.

Answer 41

access storage media

Question 42

Databases often apply blank when storing data.

Answer 42

data compression algorithms

Question 43

Data compression is usually more effective when all values have blank. As a result, more values are stored per block, which reduces storage and access time.

Answer 43

same data type

Question 44

With column-oriented storage, reading or writing an entire row requires blank. Consequently, column-oriented storage is a poor design for most transactional applications.

Answer 44

accessing multiple blocks

Question 45

PostgreSQL and Vertica are examples of relational databases that support blank. Many NoSQL databases, described elsewhere in this material, are optimized for analytic applications and use column-oriented storage.

Answer 45

column-oriented storage

Question 46

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

Answer 46

table structure

Question 47

Databases commonly support four alternative table structures. Name them

Answer 47

Heap table
Sorted table
Hash table
Table cluster

Question 48

Each table in a database can have a blank. Databases assign a default structure to all tables. Database administrators can override the default structure to optimize performance for specific queries.

Answer 48

Different structure

Question 49

In a heap table, no order is imposed on blank.

Answer 49

rows

Question 50

The database maintains a list of blocks assigned to the table, along with the address of the first available space for inserts. If all blocks are full, the database allocates a blank and inserts rows in it.

Answer 50

new block

Question 51

When a row is deleted, the space occupied by the row is marked as blank. Typically, blank space is tracked as a linked list, as in the animation below. Inserts are stored in the first space in the list, and the head of the list is set to the next space.

Answer 51

free

Question 52

Heap tables optimize blank operations.

Answer 52

insert

Question 53

Heap tables are particularly fast for blank of many rows, since rows are stored in load order.

Answer 53

bulk load

Question 54

Heap tables are not optimal for queries that read rows in a blank, such as a range of primary key values, since rows are scattered randomly across storage media.

Answer 54

specific order

Question 55

In a sorted table, the database designer identifies a blank that determines physical row order

Answer 55

sort column

Question 56

The sort column is usually the blank but can be a non-key column or group of columns.

Answer 56

primary key

Question 57

Rows are assigned to blocks according to the blank of the sort column. Each block contains all rows with values in a given range. Within each block, rows are located in order of sort column values.

Answer 57

value

Question 58

Blank are optimal for queries that read data in order of the sort column, such as:

JOIN on the sort column
SELECT with range of sort column values in the WHERE clause
SELECT with ORDER BY the sort column

Answer 58

Sorted tables

Question 59

Maintaining correct sort order of rows within each block can be slow. When a new row is inserted or when the sort column of an existing row is updated, free space may not be available in the correct location. To maintain the correct order efficiently, databases maintain pointers to the next row within each block, as in the animation below. With this technique, inserts and updates change blank rather than move entire rows.

Answer 59

two address values

Question 60

When an attempt is made to insert a row into a full block, the block splits in two. The database moves half the rows from the blank to a new block, creating space for the insert.

Answer 60

initial block

Question 61

Sorted tables are optimized for blank at the expense of insert and update operations.

Answer 61

read queries

Question 62

In a blank, rows are assigned to buckets

Answer 62

hash table

Question 63

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

Answer 63

bucket

Question 64

Initially, each bucket has one block. As a table grows, some buckets eventually fill up with rows, and the database allocates additional blocks. New blocks are blank to the initial block, and the bucket becomes a chain of linked blocks.

Answer 64

linked

Question 65

The bucket containing each row is determined by a blank function and a blank key.

Answer 65

hash

Question 66

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

Answer 66

hash key

Question 67

The blank computes the bucket containing the row from the hash key.

Answer 67

hash function

Question 68

Blank are designed to scramble row locations and evenly distribute rows across blocks

Answer 68

Hash functions

Question 69

The blank is a simple hash function with four steps:

Answer 69

modulo function

Question 70

Name the four steps of the modulo function

Answer 70

1) Convert the hash key by interpreting the key’s bits as an integer value.
2) Divide the integer by the number of buckets.
3) Interpret the division remainder as the bucket number.
4) Convert the bucket number to the physical address of the block containing the row.

Question 71

As tables grow, a blank allocates more rows to each bucket, creating deep buckets consisting of long chains of linked blocks.

Answer 71

fixed hash function

Question 72

Blank are inefficient since a query may read several blocks to access a single row. To avoid deep buckets, databases may use dynamic hash functions

Answer 72

Deep buckets

Question 73

A blank 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.

Answer 73

dynamic hash function

Question 74

Blank are optimal for inserts and deletes of individual rows, since row location is quickly determined from the hash key.

Answer 74

Hash tables

Question 75

Hash tables are optimal for selecting a single row when the hash key value is specified in the blank. Hash tables are slow on queries that select many rows with a range of values, since rows are randomly distributed across many blocks.

Answer 75

WHERE clause

Question 76

Blank, also called multi-tables, interleave rows of two or more tables in the same storage area.

Answer 76

Table clusters

Question 77

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

Answer 77

cluster key

Question 78

The cluster key determines the order in which rows are interleaved. Rows with the same cluster key value are stored together. Usually the cluster key is the blank of one table and the corresponding blank of another,

Answer 78

primary key
foreign key

Question 79

Table clusters are optimal when blank interleaved tables on the cluster key, since physical row location is the same as output order.

Answer 79

joining

Question 80

Table clusters perform poorly for many other queries. Name them.

Answer 80

Join on columns other than cluster key.
Read multiple rows of a single table.
Update cluster key.

Question 81

A blank is a file containing column values, along with pointers to rows containing the column value. The pointer identifies the block containing the row.

Answer 81

single-level index

Question 82

In some indexes, the pointer also identifies the blank of the row within the block

Answer 82

exact location

Question 83

Indexes are created by database designers with the blank command.

Answer 83

CREATE INDEX

Question 84

Single-level indexes are normally sorted on the blank.

Answer 84

column value

Question 85

A sorted index is not the same as an index on a sorted blank.

Answer 85

table

Question 86

If an indexed column is blank, the index has one entry for each column value.

Answer 86

unique

Question 87

If an indexed column is not unique, the index may have blank for some column values, or one entry for each column value, followed by multiple pointers.

Answer 87

multiple entries

Question 88

An index is usually defined on a single column, but an index can be defined on blank.

Answer 88

multiple columns

Question 89

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

Answer 89

multi-column index

Question 90

To execute a SELECT query, the database can perform a blank scan or an blank scan

Answer 90

table
index

Question 91

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

Answer 91

Table scan

Question 92

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

Answer 92

index scan

Question 93

Blank, also called filter factor or selectivity, is the percentage of table rows selected by a query.

Answer 93

Hit ratio

Question 94

When a SELECT query is executed, the database examines the WHERE clause and estimates hit ratio. If hit ratio is high, the database performs a blank.

Answer 94

table scan

Question 95

When a SELECT query is executed, the database examines the WHERE clause and estimates hit ratio. If hit ratio is low, the query needs only a few table blocks, so a table scan would be inefficient. Instead, the database does what four things:

Answer 95

Looks for an indexed column in the WHERE clause.
Scans the index.
Finds values that match the WHERE clause.
Reads the corresponding table blocks.

Question 96

If the WHERE clause does not contain an blank, the database must perform a table scan.

Answer 96

indexed column

Question 97

Since a column value and pointer occupy less space than an entire row, an index requires fewer blocks than a table. Consequently, index scans are blank than table scans.

Answer 97

much faster

Question 98

In some cases, indexes are small enough to reside in blank, and index scan time is insignificant. When hit ratio is low, additional time to read the table blocks containing selected rows is insignificant.

Answer 98

main memory

Question 99

If a single-level index is sorted, each value can be located with a blank.

Answer 99

binary search

Question 100

In a binary search, the database repeatedly splits the index blank until it finds the entry containing the search value.

Answer 100

in two

Question 101

How does the binary search work? Four steps

Answer 101

The database first compares the search value to an entry in the middle of the index.

If the search value is less than the entry value, the search value is in the first half of the index. If not, the search value is in the second half.

The database now compares the search value to the entry in the middle of the selected half, to narrow the search to one quarter of the index.

The database continues in this manner until it finds the index block containing the search value.

Question 102

For an index with N blocks, a binary search reads blank, rounded up to the nearest integer

Answer 102

log2 N blocks

Question 103

Indexes on a sorted table may be blank or blank

Answer 103

primary or secondary

Question 104

A blank, also called a clustering index, is an index on a sort column.

Answer 104

primary index

Question 105

A blank, also called a nonclustering index, is an index that is not on the sort column.

Answer 105

secondary index

Question 106

A sorted table can have only one sort column, and therefore only one blank.

Answer 106

primary index

Question 107

Usually, the blank is on the primary key column(s).

Answer 107

primary index

Question 108

In some database systems, the blank may be created on any column.

Answer 108

primary index

Question 109

All indexes of a heap or hash table are blank, since heap and hash tables have no sort column.

Answer 109

secondary

Question 110

Indexes may also be blank or blank

Answer 110

dense or sparse

Question 111

A blank index contains an entry for every table row.

Answer 111

dense index

Question 112

A blank index contains an entry for every table block.

Answer 112

sparse

Question 113

Primary indexes are on sort columns and usually blank. Secondary indexes are on non-sort columns and therefore are always blank.

Answer 113

sparse
dense

Question 114

Blank indexes are much faster than blank indexes since blank indexes have fewer entries and occupy fewer blocks.

Answer 114

Sparse
dense
sparse

Question 115

Primary indexes are usually sparse and sparse indexes are fast. As a result, database designers usually create a primary index on the blank of large tables.

Answer 115

primary key

Question 116

Index with one entry for each table row. What type?

Answer 116

Dense

Question 117

Index with one entry for each table block. What type?

Answer 117

Sparse

Question 118

An index on the table sort column. What type?

Answer 118

Primary

Question 119

An index that is not on the table sort column. What type?

Answer 119

Secondary

Question 120

A clustering index is not the same as a blank. A blank refers to a table cluster storage structure

Answer 120

cluster key

Question 121

When a row is inserted into a table, a new blank is created.

Answer 121

index entry

Question 122

Since single-level indexes are sorted, a new entry must be placed in the correct location. To make space for the new entry, subsequent entries must be moved, which is too slow for large tables. Instead, the database blanks an index block and reallocates entries to the blank, creating space for the new entry.

Answer 122

splits
new block

Question 123

When a row is deleted, the row’s index entry must be blank or blank

Answer 123

physically removed or marked deleted

Question 124

Since single-level indexes are sorted, physically deleting an entry requires moving all subsequent entries, which is slow. For this reason, index entries are blank. Periodically, the database may reorganize the index to remove deleted entries and compress the index.

Answer 124

marked as ‘deleted’

Question 125

An update to a column that is not indexed does not affect the index. An update to an indexed column is like a delete followed by an insert. The index entry for the blank is deleted and an index entry for the blank is inserted.

Answer 125

initial value
updated value

Question 126

With a sparse index, each entry corresponds to a table block rather than a table row. Index entries are inserted or deleted when blank.

Answer 126

blocks split or merge

Question 127

A blank stores column values and row pointers in a hierarchy. The bottom level of the hierarchy is a blank. The bottom level is blank for primary indexes, or blank for secondary indexes.

Answer 127

multi-level index
sorted single-level index
sparse
dense

Question 128

In a multi-level index, each level above the bottom is a blank to the level below. Since all levels above the bottom are blank, levels rapidly become smaller. The top level always fits in blank.

Answer 128

sparse sorted index
sparse
one block

Question 129

In a multi-level index, to locate a row containing an indexed value, the database first reads the blank. The database compares the indexed value to entries in the block and locates the next level block containing the value. Continuing in this manner, the database eventually locates the bottom-level block containing the value. The bottom-level block contains a blank.

Answer 129

top-level block
pointer to the correct table block

Question 130

A blank index has more bottom-level entries than a blank index, and may have more levels.

Answer 130

dense
sparse

Question 131

For a dense multilevel index, number of levels =

Answer 131

logfan-out (number of rows)

Question 132

The number of index entries per block is called the blank of a multi-level index. The number of levels in a multi-level index can be computed from blank, number of rows, and rows per block:

Answer 132

fan-out

Question 133

For a sparse multilevel index, number of levels =

Answer 133

logfan-out (number of rows / rows per block)

Question 134

When calculating number of levels in a multilevel index, blank is a fractional number and must be rounded up to the nearest integer. Both formulas assume minimal free space in the index.

Answer 134

log

Question 135

Dense indexes usually have blank levels or less. Sparse indexes usually have blank levels or less.

Answer 135

four levels
three levels

Question 136

Multi-level indexes are blank than single-level indexes on most queries.

Answer 136

faster

Question 137

The multi-level index search reads blank per level. Usually the top two levels are small and retained in memory. Since the index has three levels, the query reads just one index block from blank.

Answer 137

one index block
storage media

Question 138

Because blank indexes are faster than blank indexes on most queries, databases commonly use blank rather than blank indexes.

Answer 138

multi-level
single-level
multi-level
single-level

Question 139

Number of rows / rows per table block is what type of search

Answer 139

Table scan

Question 140

Number of index blocks plus referenced table blocks is what type of search

Answer 140

Single-level index scan

Question 141

log base 2 (number of index blocks) plus referenced table blocks is what type of search

Answer 141

Single-level index binary search

Question 142

log base fan-out (number of rows), rounded up, plus referenced table blocks is what type of search

Answer 142

multi-level dense index search

Question 143

log base fan-out (number of rows / rows per table block), rounded up, plus referenced table block is what type of search

Answer 143

multi-level sparse index search

Question 144

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

Answer 144

branch

Question 145

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

Answer 145

balanced
imbalanced

Question 146

Imbalanced indexes are undesirable, since blank is unpredictable.

Answer 146

processing time

Question 147

If a query follows a long branch, the query is blank.

Answer 147

relatively slow

Question 148

Blank are managed to maintain balanced indexes

Answer 148

inserts

Question 149

In a blank index, inserts always generate new bottom-level index entries. In a blank index, inserts generate new bottom-level index entries when table blocks split.

Answer 149

dense
sparse

Question 150

If the new index entry goes in a full index block, the blank. Half of the rows move to the new block, creating space for the entry.

Answer 150

block splits

Question 151

A new block in the blank generates a new index entry the next level up. If the block in the next level up is full, the block splits and the process repeats.

Answer 151

bottom level

Question 152

If blocks are full at all index levels, the split propagates to the blank. In this case, the blank block splits and a new level is created.

Answer 152

top level

Question 153

New levels are always added at the blank rather than the bottom of one branch. As a result, all branches are always the same length, and the index is always balanced.

Answer 153

top of the hierarchy

Question 154

Deletes may cause blank. Blank are the reverse of block splits and potentially eliminate the top level of the index. Consequently, deletes also maintain a balanced index.

Answer 154

block mergers

Question 155

Updates to an indexed column behave like a delete of the initial value followed by an insert of a new value. Since updates are implemented as deletes and inserts, updates also leave the index blank.

Answer 155

balanced

Question 156

In B+tree, all indexed values appear in the blank. Pointers to table blocks appear only in the blank. Since some indexed values also appear in higher levels, values are occasionally repeated in the index.

Answer 156

bottom level

Question 157

In B-tree, if an indexed value appears in a higher level, the value is blank at lower levels. Instead, a blank to the corresponding table block appears in the higher level along with the value.

Answer 157

not repeated
pointer

Question 158

B-trees are more compact than B+trees since blank are not repeated.

Answer 158

index values

Question 159

B+trees are simpler, since all pointers to table blocks appear in the same blank level.

Answer 159

(bottom)

Question 160

The B+tree structure has two benefits over the B-tree. Name them

Answer 160

The bottom level of a B+tree is a single-level index and can be scanned or searched.

In a B-tree, inserts, updates, and deletes may cause a table pointer to change levels, which is hard to implement. B+trees do not have this problem, since table pointers are always in the bottom level.

Question 161

Multi-level indexes are usually implemented as blank.

Answer 161

B+trees

Question 162

Although most multi-level indexes are implemented as B+trees, the term blank is commonly used and often refers to a B+tree structure. B+tree is commonly written as B+-tree or B+-tree.

Answer 162

B-tree

Question 163

The blank index is the most commonly used index type.

Answer 163

multi-level

Question 164

In a blank, index entries are assigned to buckets.

Answer 164

hash index

Question 165

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

Answer 165

bucket

Question 166

In a hash index, initially, each bucket has blank. As an index grows, some buckets eventually fill up, and additional blank are allocated and linked to the initial block.

Answer 166

one block
blocks

Question 167

In a hash index, the bucket containing each index entry is determined by a blank, which computes a bucket number from the value of the indexed column.

Answer 167

hash function

Question 168

In a hash index, To locate a row containing a column value, the database does what four things:

Answer 168

Applies the hash function to the column value to compute a bucket number.
Reads the index blocks for the bucket number.
Finds the index entry for the column value and reads the table block pointer.
Reads the table block containing the row.

Question 169

A hash index stores blank in each bucket, while a hash table stores blank in each bucket.

Answer 169

index entries
table rows

Question 170

A blank is an index that is structured using a hash function. A blank is a column that determines the physical location of rows in a hash table.

Answer 170

hash index
hash key

Question 171

A blank is a grid of bits

Answer 171

bitmap index

Question 172

In a bitmap index, each index row corresponds to a blank. If the table’s primary key is an integer, the index row number might be the primary key value. Alternatively, the index row number might be an internal table row number, maintained by the database.

Answer 172

unique table row

Question 173

In a bitmap index, each index column corresponds to a blank. Ex: If the index is on AirportCode, each index column corresponds to a different three-letter airport code. The mapping of index column numbers to table values is computed with a function or stored in an internal ‘lookup’ table.

Answer 173

distinct table value

Question 174

Bitmap indexes contain blank and blank.

Answer 174

ones and zeros

Question 175

In a bitmap index, blank indicates that the table row corresponding to the index row number contains the table value corresponding to the index column number.

Answer 175

‘One’

Question 176

In a bitmap index, blank indicates the row does not contain the value.

Answer 176

‘Zero’

Question 177

In a bitmap index, tyo locate rows containing a table value, the database does what five things:

Answer 177

Determines the index column corresponding to the table value.

Reads the index column and finds index rows that are set to ‘one’.

Determines table rows corresponding to the index rows.

Determines pointers to blocks containing the table rows.

Reads the blocks containing the table rows.

Question 178

An efficient bitmap index has what two characteristics:

Answer 178

The database can quickly determine the block containing a table row from the index row number (steps 3 and 4). Ex: The index row number is the hash key for a hash table. The block is determined by applying the hash function to the row number. Ex: The index row number is the table primary key. The block is determined with a primary index.

The indexed column contains relatively few distinct values, typically tens or hundreds. If the indexed column contains thousands of distinct values, the bitmap index is large and inefficient.

Question 179

Bitmap indexes with the strong characteristics enable blank and can easily be retained in memory.

Answer 179

fast reads

Question 180

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

Answer 180

physical index

Question 181

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

Answer 181

logical index

Question 182

Logical indexes are always blank indexes and require a separate primary index on the same table.

Answer 182

secondary

Question 183

In a logical index, to locate a row containing a column value, the database must do what three things:

Answer 183

Looks up the column value in the logical index to find the primary key value.

Looks up the primary key value in the primary index to find the table block pointer.

Reads the table block containing the row.

Question 184

Logical indexes change only when blank values are updated, which occurs infrequently.

Answer 184

primary key

Question 185

Physical indexes change whenever a row moves to a new block, which can occurs in what three ways:

Answer 185

A row is inserted into a full block. To create space for the new row, the block splits and some rows move to a new block.

The sort column is updated. When the sort column is updated, the row may move to a new block to maintain sort order.

The table is reorganized. Occasionally, a database administrator may physically reorganize a table to recover deleted space or order blocks contiguously on magnetic disk.

Question 186

If a table has several indexes, the time required to update blank is significant, and blank are more efficient.

Answer 186

physical indexes
logical indexes

Question 187

On read queries, a logical index requires an additional read of the blank and is slower than a physical index. However, the blank is often retained in memory, mitigating the cost of the additional read.

Answer 187

primary index

Question 188

Which index is an index on a unique sort column.

Answer 188

Primary index

Question 189

Which index is an index on a non-sort column.

Answer 189

Secondary index

Question 190

Which index is an index with primary key values rather than block pointers.

Answer 190

Logical Index

Question 191

Which index is an index with table block pointers.

Answer 191

Physical index

Question 192

In some cases, values specified in a WHERE clause may be in a different format or units than values stored in the column. To address this problem, some databases support blank.

Answer 192

function indexes

Question 193

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

Answer 193

function

Question 194

In principle, blank can be used with any index type, including single-level, multi-level, hash, bitmap, and logical indexes.

Answer 194

functions

Question 195

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

Answer 195

tablespace

Question 196

The blank statement names a tablespace and assigns the tablespace to a file.

Answer 196

CREATE TABLESPACE

Question 197

The CREATE TABLE statement assigns a table to a blank. Indexes are stored in the same tablespace as the indexed table.

Answer 197

tablespace

Question 198

Give the syntax for a CREATE TABLESPACE

Answer 198

CREATE TABLESPACE TablespaceName
[ ADD DATAFILE ‘FileName’ ];

Question 199

Give the syntax for a CREATE TABLE assigning a tablespace

Answer 199

CREATE TABLE TableName
( ColumnName ColumnDefintion, … )
[ TABLESPACE TablespaceName ];

Question 200

By default, most databases automatically create one blank for each table, so each table is stored in a separate file.

Answer 200

tablespace

Question 201

Database administrators can blank tablespaces and assign one or multiple tables to each tablespace

Answer 201

manually create

Question 202

Database administrators can improve blank by assigning frequently accessed tables to tablespaces stored on fast storage media.

Answer 202

query performance

Question 203

In most cases, databases perform better with a blank per tablespace

Answer 203

single table

Question 204

With a single table per tablespace, Individual tables can be backed up blank of other tables.

Answer 204

independently

Question 205

With a single table per tablespace, when a table is dropped, the associated file is deleted and blank is released. When multiple tables are stored in one tablespace, all tables must be dropped to release blank.

Answer 205

storage

Question 206

With a single table per tablespace, blank of multiple tables are usually faster when each table is stored in a separate file.

Answer 206

concurrent updates

Question 207

With a single table per tablespace, blocks of a new file are usually allocated contiguously on a few tracks of a disk drive. As files are updated, blocks become scattered, or blank, across many tracks. Queries that scan tables on heavily blank files are slow because the disk drive must read many tracks. When tables are updated, storing one table per file minimizes blank and optimizes table scans.

Answer 207

fragmented

Question 208

In some cases, assigning multiple tables to one tablespace can improve performance. Each tablespace must be managed by the database and incurs a small amount of blank.

Answer 208

overhead

Question 209

Storing many small tables in one tablespace reduces overhead and, if the tables are commonly accessed in the blank, may improve query performance

Answer 209

same query

Question 210

If the tables are read-only, assigning the tables to one tablespace does not increase blank.

Answer 210

fragmentation

Question 211

A blank is a subset of table data.

Answer 211

partition

Question 212

One table has many partitions that do not overlap and, together, contain all blank.

Answer 212

table data

Question 213

A blank partition is a subset of table rows.

Answer 213

horizontal

Question 214

A blank partition is a subset of table columns.

Answer 214

vertical

Question 215

MySQL and most relational databases partition tables blank, not blank.

Answer 215

horizontally
vertically

Question 216

Each partition is stored in a separate blank, specified either explicitly by the database administrator or automatically by the database.

Answer 216

tablespace

Question 217

When a table is partitioned, table blank are also partitioned. Each partition contains index entries only for rows in the partition.

Answer 217

indexes

Question 218

Partitions can be defined in several ways. Often, rows are assigned to partitions based on values of a blank. Each partition may be associated with a continuous range of values or an explicit list of values.

Answer 218

specific column

Question 219

Partitions improve blank by reducing the amount of data accessed by INSERT, UPDATE, DELETE, and SELECT statements

Answer 219

query performance

Question 220

The term blank means either an individual subset of a table or, collectively, all subsets of a table. Usually, the meaning is clear from context

Answer 220

partition

Question 221

A blank is similar to a partition. Like a partition, a blank is a subset of table data, usually a subset of rows rather than columns.

Answer 221

shard

Question 222

Unlike partitions, which are stored on different storage devices of a single computer, shards are stored on different computers of a blank

Answer 222

distributed database

Question 223

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

Answer 223

partition expression
partition columns

Question 224

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

Answer 224

expression

Question 225

Rows are assigned to partitions in one of what three ways:

Answer 225

A range partition
A list partition
A hash partition

Question 226

A blank partition associates each partition with a range of partition expression values.

Answer 226

Range

Question 227

In a range partition, the blank keywords specify the upper bound of each range. The blank keyword represents the highest column value, and blank specifies the highest range. Each partition is explicitly named by the database administrator.

Answer 227

VALUES LESS THAN
MAXVALUE
VALUES LESS THAN MAXVALUE

Question 228

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

Answer 228

list
VALUES IN

Question 229

A blank 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.

Answer 229

hash

Question 230

Range, list, and hash partitions are supported in most relational databases. The blank partition is commonly used, often with a simple partition expression based on a date column.

Answer 230

range

Question 231

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

Answer 231

key

Question 232

Give the syntax for adding a partition

Answer 232

CREATE TABLE TableName
( ColumnName ColumnDefinition, … )
[ PARTITION BY
{ RANGE (Expression)
| LIST (Expression)
| HASH (Expression)
}
[ PARTITIONS NumberOfPartitions ]
[ ( PartitionDefinition, … ) ]
];

PartitionDefinition:
PARTITION PartitionName
[ VALUES
{ LESS THAN { (Expression) | MAXVALUE }
| IN ( Value, … )
}
]
[ TABLESPACE TablespaceName ]

Question 233

MySQL has two unusual restrictions that prevent partitions on many tables and columns. Name them
As a result, partitions are of limited value in MySQL.

Answer 233

A partitioned table may not contain foreign keys, and foreign keys may not refer to a partitioned table.

All partition columns must appear in all unique columns, including the primary key, of the partitioned table.

Question 234

Blank specifies tables, columns, and keys.

Answer 234

Logical design

Question 235

Blank specifies indexes, table structures, and partitions.

Answer 235

Physical design

Question 236

Physical design affects query performance but never affects blank.

Answer 236

query results

Question 237

A blank or storage manager translates instructions generated by a query processor into low-level commands that access data on storage media.

Answer 237

storage engine

Question 238

Storage engines support different index and table structures, so blank is dependent on a specific storage engine.

Answer 238

physical design

Question 239

MySQL can be configured with several different storage engines, including what three:

Answer 239

InnoDB
MyISAM
MEMORY

Question 240

Blank is the default storage engine installed with the MySQL download. Blank has full support for transaction management, foreign keys, referential integrity, and locking.

Answer 240

InnoDB

Question 241

Blank has limited transaction management and locking capabilities. Blank is commonly used for analytic applications with limited data updates.

Answer 241

MyISAM

Question 242

Blank stores all data in main memory. Blank is used for fast access with databases small enough to fit in main memory.

Answer 242

MEMORY

Question 243

Different databases and storage engines support different blank and blank.

Answer 243

table structures and index types

Question 244

Blank supports heap, sorted, hash, and cluster tables. Blank supports only heap and sorted tables.

Answer 244

Oracle Database
MySQL with InnoDB

Question 245

Blank or Blank supports only B+tree indexes. Blank supports both B+tree and hash indexes.

Answer 245

MySQL with InnoDB or MyISAM
MySQL with MEMORY

Question 246

In MySQL with InnoDB, name the five rules

Indexes are always B+tree indexes.

A primary index is automatically created on every primary key.

A secondary index is automatically created on every foreign key.

Additional secondary indexes are created manually with the CREATE INDEX statement.

Tables with a primary key have sorted structure. Tables with no primary key have a heap structure.

Answer 246

Indexes are always B+tree indexes.

A primary index is automatically created on every primary key.

A secondary index is automatically created on every foreign key.

Additional secondary indexes are created manually with the CREATE INDEX statement.

Tables with a primary key have sorted structure. Tables with no primary key have a heap structure.

Question 247

The blank statement creates an index by specifying the index name and table columns that compose the index. Most indexes specify just one column, but a composite index specifies multiple columns.

Answer 247

CREATE INDEX

Question 248

The blank statement deletes a table’s index.

Answer 248

DROP INDEX

Question 249

The blank statement displays a table’s index. Blank generates a result table with one row for each column of each index. A multi-column index has multiple rows in the result table.

Answer 249

SHOW INDEX

Question 250

Give the syntax for creating an index

Answer 250

CREATE INDEX IndexName
ON TableName (Column1, Column2, …, ColumnN);

Question 251

Give the syntax for deleting an index

Answer 251

DROP INDEX IndexName ON TableName;

Question 252

Give the syntax for showing an index

Answer 252

SHOW INDEX FROM TableName;

Question 253

Name the eight columns in the results table of SHOW INDEX

Answer 253

Table
Non_unique
Key_name
Seq_in_index
Column_name
Cardinality
Null
Index_type

Question 254

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

Answer 254

EXPLAIN

Question 255

EXPLAIN syntax is simple and uniform in most databases. Name it

Answer 255

EXPLAIN statement;

Question 256

The statement in an EXPLAIN query can be any blank, blank, blank, or blank statement.

Answer 256

SELECT, INSERT, UPDATE, or DELETE

Question 257

Name the eight columns in an EXPLAIN statement results table

Answer 257

select_type
table
type
possible_keys
key
ref
rows
filtered

Question 258

In the select type column of an EXPLAIN table result, what three values are possible and what do they mean

Answer 258

SIMPLE indicates query is neither nested nor union
PRIMARY indicates query is the outer SELECT of nested query
SUBQUERY indicates query is an inner SELECT of nested query

Question 259

In the type column of an explain statement results column, what are the possible join values and what do they mean

Answer 259

const indicates the table has at most one matching row
range indicates a join column is compared to a constant using operators such as BETWEEN, LIKE, or IN()
eq_ref indicates one table row is read for each combination of rows from other tables (typically, an equijoin)
ALL indicates a table scan is executed for each combination of rows from other tables

Question 260

In the key column of the EXPLAIN statement, what does NULL mean

Answer 260

NULL indicates a table scan is performed

Question 261

What is the formula for the filtered column in an explain statement results table

Answer 261

Estimated number of rows selected by WHERE clause / estimated number of rows read from table

Question 262

What are the five possible steps a database admin may take in the physical design process?

Answer 262

Create initial physical design.

Identify slow queries.

EXPLAIN slow queries.

Create and drop indexes based on the EXPLAIN result table.

Partition large tables.

Question 263

In the create physical design step of the design process, the DB admin create initial physical design by creating a blank on primary keys and a blank on foreign keys. In MySQL with InnoDB, these indexes are created automatically for all tables. In other databases, this step is necessary for tables larger than roughly 100 kilobytes, but can be omitted for smaller tables.

Answer 263

primary index
secondary index

Question 264

In the identify slow queries step of physical design, the blank is a file that records all long-running queries submitted to the database. Identify slow queries by inspecting the log. Most other relational databases have similar query logs.

Answer 264

MySQL slow query log

Question 265

In the EXPLAIN slow queries step of physical design, run blank on each slow query to assess the effectiveness of indexes. A high value for rows and a low value for blank indicates either a table scan or an ineffective index.

Answer 265

EXPLAIN
filtered

Question 266

In the create and drop indexes based on the EXPLAIN result table step of physical design, consider creating an blank when the rows value is high and the filtered value is low. Consider dropping indexes that are blank.

Answer 266

index
never used

Question 267

In physical design’s partition large tables step, if some queries are still slow after indexes are created, consider blank. Blank when slow queries access a small subset of rows of a large table. The blank column should appear in the WHERE clause of slow queries. Often, a blank is best.

Answer 267

Partitions
partition
partition
range partition

Question 268

Test

Answer 268

Tes