Data Storage Flashcards
1
Q
Speed is measured as blank and blank
A
access time and transfer rate
2
Q
Blank is the time required to access the first byte in a read or write operation.
A
Access time
3
Q
Blank is the speed at which data is read or written, following initial access.
A
Transfer rate
4
Q
Blank typically ranges from pennies to dollars per gigabyte of memory, depending on the media type.
A
Cost
5
Q
In principle, any media type can store any amount of data. In practice, blank is limited by cost.
A
capacity
6
Q
Blank is memory that is lost when disconnected from power.
A
Volatile memory
7
Q
Blank is retained without power.
A
Non-volatile memory
8
Q
Computer media vary on what four important dimensions:
A
Speed.
Cost.
Capacity.
Volatility.
9
Q
Three types of media are most important for database management:
Name them
A
Main memory, also called random-access memory (RAM)
Flash memory, also called solid-state drive (SSD)
Magnetic disk, also called hard-disk drive (HDD),
10
Q
Blank, also called random-access memory (RAM), is the primary memory used when computer programs execute. Blank is fast, expensive, and has limited capacity.
A
Main Memory
11
Q
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.
A
Flash memory
12
Q
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.
A
Magnetic Disk
13
Q
Main memory is blank
A
volatile
14
Q
Flash memory and magnetic disk are blank and therefore considered blank
A
non-volatile
storage media
15
Q
Databases store data permanently on blank and transfer data to and from blank during program execution.
A
storage media
main memory
16
Q
Main memory has an access time of blank microseconds, transfer rate of blank, cost per gb of blank, and is blank
A
.01 to .1
>10
>1
Volatile
17
Q
Flash memory has an access time or blank microseconds , transfer rate of blank gb/sec, cost per gb of blank, and is blank
A
20 to 100
.5 to 3
around .25
Non-volatile
18
Q
Magnetic disk has an access time or blank microseconds , transfer rate of blank gb/sec, cost per gb of blank, and is blank
A
5,000 to 10,000
.05 to .2
around .02
Non-volatile
19
Q
What type of memory - Reading one gigabyte takes about one second.
A
Flash memory
20
Q
what type of memory - Used to store petabytes (millions of gigabytes) of user data in the cloud.
A
Magnetic disk
21
Q
What type of memory - Upgrading from 16 to 32 gigabytes costs an extra $400 for an Apple laptop computer.
A
Main memory
22
Q
Magnetic disk groups data in black, traditionally 512 bytes per sector but 4 kilobytes with newer disk formats.
A
sectors
23
Q
Flash memory groups data in blank, usually between 2 kilobytes and 16 kilobytes per page.
A
pages
24
Q
Databases and file systems use a uniform size, called a blank, when transferring data between main memory and storage media
A
block
25
Blank is independent of storage media.
Block size
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.
controllers
27
Blank must be uniform within a database but, in most database systems, can be specified by the database administrator
Block size
28
Database systems typically support block sizes ranging from blank to blank
2 kilobytes to 64 kilobytes.
29
Smaller block sizes are usually better for blank, which access a few rows per query.
transactional applications
30
Larger block sizes are usually better for blank, which access many rows per query.
analytic applications
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.
common queries
32
Most relational databases are optimized for blank, which often read and write individual rows.
transactional applications
33
To minimize block transfers, relational databases usually store an entire row within one block, which is called blank.
row-oriented storage
34
Row-oriented storage performs best when row size is small relative to block size, for what two reasons:
Improved query performance.
Less wasted storage.
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.
rows
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.
a few bytes
37
Database administrators might specify a blank for databases containing larger rows.
larger block size
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.
link
39
In blank, also called columnar storage, each block stores values for a single column only.
column-oriented storage
40
Column-oriented storage benefits analytic applications in what two ways
Faster data access.
Better data compression.
41
More column values are transferred per block, reducing time to blank.
access storage media
42
Databases often apply blank when storing data.
data compression algorithms
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.
same data type
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.
accessing multiple blocks
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.
column-oriented storage
46
A blank is a scheme for organizing rows in blocks on storage media.
table structure
47
Databases commonly support four alternative table structures. Name them
Heap table
Sorted table
Hash table
Table cluster
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.
Different structure
49
In a heap table, no order is imposed on blank.
rows
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.
new block
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.
free
52
Heap tables optimize blank operations.
insert
53
Heap tables are particularly fast for blank of many rows, since rows are stored in load order.
bulk load
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.
specific order
55
In a sorted table, the database designer identifies a blank that determines physical row order
sort column
56
The sort column is usually the blank but can be a non-key column or group of columns.
primary key
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.
value
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
Sorted tables
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.
two address values
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.
initial block
61
Sorted tables are optimized for blank at the expense of insert and update operations.
read queries
62
In a blank, rows are assigned to buckets
hash table
63
A blank is a block or group of blocks containing rows.
bucket
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.
linked
65
The bucket containing each row is determined by a blank function and a blank key.
hash
66
The blank is a column or group of columns, usually the primary key.
hash key
67
The blank computes the bucket containing the row from the hash key.
hash function
68
Blank are designed to scramble row locations and evenly distribute rows across blocks
Hash functions
69
The blank is a simple hash function with four steps:
modulo function
70
Name the four steps of the modulo function
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.
71
As tables grow, a blank allocates more rows to each bucket, creating deep buckets consisting of long chains of linked blocks.
fixed hash function
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
Deep buckets
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.
dynamic hash function
74
Blank are optimal for inserts and deletes of individual rows, since row location is quickly determined from the hash key.
Hash tables
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.
WHERE clause
76
Blank, also called multi-tables, interleave rows of two or more tables in the same storage area.
Table clusters
77
Table clusters have a blank, a column that is available in all interleaved tables.
cluster key
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,
primary key
foreign key
79
Table clusters are optimal when blank interleaved tables on the cluster key, since physical row location is the same as output order.
joining
80
Table clusters perform poorly for many other queries. Name them.
Join on columns other than cluster key.
Read multiple rows of a single table.
Update cluster key.
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.
single-level index
82
In some indexes, the pointer also identifies the blank of the row within the block
exact location
83
Indexes are created by database designers with the blank command.
CREATE INDEX
84
Single-level indexes are normally sorted on the blank.
column value
85
A sorted index is not the same as an index on a sorted blank.
table
86
If an indexed column is blank, the index has one entry for each column value.
unique
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.
multiple entries
88
An index is usually defined on a single column, but an index can be defined on blank.
multiple columns
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.
multi-column index
90
To execute a SELECT query, the database can perform a blank scan or an blank scan
table
index
91
A blank is a database operation that reads table blocks directly, without accessing an index.
Table scan
92
An blank is a database operation that reads index blocks sequentially, in order to locate the needed table blocks.
index scan
93
Blank, also called filter factor or selectivity, is the percentage of table rows selected by a query.
Hit ratio
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.
table scan
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:
Looks for an indexed column in the WHERE clause.
Scans the index.
Finds values that match the WHERE clause.
Reads the corresponding table blocks.
96
If the WHERE clause does not contain an blank, the database must perform a table scan.
indexed column
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.
much faster
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.
main memory
99
If a single-level index is sorted, each value can be located with a blank.
binary search
100
In a binary search, the database repeatedly splits the index blank until it finds the entry containing the search value.
in two
101
How does the binary search work? Four steps
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.
102
For an index with N blocks, a binary search reads blank, rounded up to the nearest integer
log2 N blocks
103
Indexes on a sorted table may be blank or blank
primary or secondary
104
A blank, also called a clustering index, is an index on a sort column.
primary index
105
A blank, also called a nonclustering index, is an index that is not on the sort column.
secondary index
106
A sorted table can have only one sort column, and therefore only one blank.
primary index
107
Usually, the blank is on the primary key column(s).
primary index
108
In some database systems, the blank may be created on any column.
primary index
109
All indexes of a heap or hash table are blank, since heap and hash tables have no sort column.
secondary
110
Indexes may also be blank or blank
dense or sparse
111
A blank index contains an entry for every table row.
dense index
112
A blank index contains an entry for every table block.
sparse
113
Primary indexes are on sort columns and usually blank. Secondary indexes are on non-sort columns and therefore are always blank.
sparse
dense
114
Blank indexes are much faster than blank indexes since blank indexes have fewer entries and occupy fewer blocks.
Sparse
dense
sparse
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.
primary key
116
Index with one entry for each table row. What type?
Dense
117
Index with one entry for each table block. What type?
Sparse
118
An index on the table sort column. What type?
Primary
119
An index that is not on the table sort column. What type?
Secondary
120
A clustering index is not the same as a blank. A blank refers to a table cluster storage structure
cluster key
121
When a row is inserted into a table, a new blank is created.
index entry
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.
splits
new block
123
When a row is deleted, the row's index entry must be blank or blank
physically removed or marked deleted
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.
marked as 'deleted'
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.
initial value
updated value
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.
blocks split or merge
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.
multi-level index
sorted single-level index
sparse
dense
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.
sparse sorted index
sparse
one block
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.
top-level block
pointer to the correct table block
130
A blank index has more bottom-level entries than a blank index, and may have more levels.
dense
sparse
131
For a dense multilevel index, number of levels =
logfan-out (number of rows)
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:
fan-out
133
For a sparse multilevel index, number of levels =
logfan-out (number of rows / rows per block)
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.
log
135
Dense indexes usually have blank levels or less. Sparse indexes usually have blank levels or less.
four levels
three levels
136
Multi-level indexes are blank than single-level indexes on most queries.
faster
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.
one index block
storage media
138
Because blank indexes are faster than blank indexes on most queries, databases commonly use blank rather than blank indexes.
multi-level
single-level
multi-level
single-level
139
Number of rows / rows per table block is what type of search
Table scan
140
Number of index blocks plus referenced table blocks is what type of search
Single-level index scan
141
log base 2 (number of index blocks) plus referenced table blocks is what type of search
Single-level index binary search
142
log base fan-out (number of rows), rounded up, plus referenced table blocks is what type of search
multi-level dense index search
143
log base fan-out (number of rows / rows per table block), rounded up, plus referenced table block is what type of search
multi-level sparse index search
144
Each path from the top-level block to a bottom-level block is called a blank
branch
145
Multi-level indexes are called blank when all branches are the same length and blank when branches are different lengths.
balanced
imbalanced
146
Imbalanced indexes are undesirable, since blank is unpredictable.
processing time
147
If a query follows a long branch, the query is blank.
relatively slow
148
Blank are managed to maintain balanced indexes
inserts
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.
dense
sparse
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.
block splits
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.
bottom level
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.
top level
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.
top of the hierarchy
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.
block mergers
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.
balanced
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.
bottom level
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.
not repeated
pointer
158
B-trees are more compact than B+trees since blank are not repeated.
index values
159
B+trees are simpler, since all pointers to table blocks appear in the same blank level.
(bottom)
160
The B+tree structure has two benefits over the B-tree. Name them
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.
161
Multi-level indexes are usually implemented as blank.
B+trees
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.
B-tree
163
The blank index is the most commonly used index type.
multi-level
164
In a blank, index entries are assigned to buckets.
hash index
165
A blank is a block or group of blocks containing index entries.
bucket
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.
one block
blocks
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.
hash function
168
In a hash index, To locate a row containing a column value, the database does what four things:
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.
169
A hash index stores blank in each bucket, while a hash table stores blank in each bucket.
index entries
table rows
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.
hash index
hash key
171
A blank is a grid of bits
bitmap index
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.
unique table row
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.
distinct table value
174
Bitmap indexes contain blank and blank.
ones and zeros
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.
'One'
176
In a bitmap index, blank indicates the row does not contain the value.
'Zero'
177
In a bitmap index, tyo locate rows containing a table value, the database does what five things:
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.
178
An efficient bitmap index has what two characteristics:
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.
179
Bitmap indexes with the strong characteristics enable blank and can easily be retained in memory.
fast reads
180
A single- or multi-level index normally contains pointers to table blocks and is called a blank.
physical index
181
A blank is a single- or multi-level index in which pointers to table blocks are replaced with primary key values.
logical index
182
Logical indexes are always blank indexes and require a separate primary index on the same table.
secondary
183
In a logical index, to locate a row containing a column value, the database must do what three things:
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.
184
Logical indexes change only when blank values are updated, which occurs infrequently.
primary key
185
Physical indexes change whenever a row moves to a new block, which can occurs in what three ways:
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.
186
If a table has several indexes, the time required to update blank is significant, and blank are more efficient.
physical indexes
logical indexes
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.
primary index
188
Which index is an index on a unique sort column.
Primary index
189
Which index is an index on a non-sort column.
Secondary index
190
Which index is an index with primary key values rather than block pointers.
Logical Index
191
Which index is an index with table block pointers.
Physical index
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.
function indexes
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.
function
194
In principle, blank can be used with any index type, including single-level, multi-level, hash, bitmap, and logical indexes.
functions
195
A blank is a database object that maps one or more tables to a single file.
tablespace
196
The blank statement names a tablespace and assigns the tablespace to a file.
CREATE TABLESPACE
197
The CREATE TABLE statement assigns a table to a blank. Indexes are stored in the same tablespace as the indexed table.
tablespace
198
Give the syntax for a CREATE TABLESPACE
CREATE TABLESPACE TablespaceName
[ ADD DATAFILE 'FileName' ];
199
Give the syntax for a CREATE TABLE assigning a tablespace
CREATE TABLE TableName
( ColumnName ColumnDefintion, ... )
[ TABLESPACE TablespaceName ];
200
By default, most databases automatically create one blank for each table, so each table is stored in a separate file.
tablespace
201
Database administrators can blank tablespaces and assign one or multiple tables to each tablespace
manually create
202
Database administrators can improve blank by assigning frequently accessed tables to tablespaces stored on fast storage media.
query performance
203
In most cases, databases perform better with a blank per tablespace
single table
204
With a single table per tablespace, Individual tables can be backed up blank of other tables.
independently
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.
storage
206
With a single table per tablespace, blank of multiple tables are usually faster when each table is stored in a separate file.
concurrent updates
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.
fragmented
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.
overhead
209
Storing many small tables in one tablespace reduces overhead and, if the tables are commonly accessed in the blank, may improve query performance
same query
210
If the tables are read-only, assigning the tables to one tablespace does not increase blank.
fragmentation
211
A blank is a subset of table data.
partition
212
One table has many partitions that do not overlap and, together, contain all blank.
table data
213
A blank partition is a subset of table rows.
horizontal
214
A blank partition is a subset of table columns.
vertical
215
MySQL and most relational databases partition tables blank, not blank.
horizontally
vertically
216
Each partition is stored in a separate blank, specified either explicitly by the database administrator or automatically by the database.
tablespace
217
When a table is partitioned, table blank are also partitioned. Each partition contains index entries only for rows in the partition.
indexes
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.
specific column
219
Partitions improve blank by reducing the amount of data accessed by INSERT, UPDATE, DELETE, and SELECT statements
query performance
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
partition
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.
shard
222
Unlike partitions, which are stored on different storage devices of a single computer, shards are stored on different computers of a blank
distributed database
223
To partition a table, the database administrator specifies a blank based on one or more blank.
partition expression
partition columns
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.
expression
225
Rows are assigned to partitions in one of what three ways:
A range partition
A list partition
A hash partition
226
A blank partition associates each partition with a range of partition expression values.
Range
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.
VALUES LESS THAN
MAXVALUE
VALUES LESS THAN MAXVALUE
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.
list
VALUES IN
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.
hash
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.
range
231
A blank partition is similar to a hash partition, except the partition expression is determined automatically by the database.
key
232
Give the syntax for adding a partition
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 ]
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.
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.
234
Blank specifies tables, columns, and keys.
Logical design
235
Blank specifies indexes, table structures, and partitions.
Physical design
236
Physical design affects query performance but never affects blank.
query results
237
A blank or storage manager translates instructions generated by a query processor into low-level commands that access data on storage media.
storage engine
238
Storage engines support different index and table structures, so blank is dependent on a specific storage engine.
physical design
239
MySQL can be configured with several different storage engines, including what three:
InnoDB
MyISAM
MEMORY
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.
InnoDB
241
Blank has limited transaction management and locking capabilities. Blank is commonly used for analytic applications with limited data updates.
MyISAM
242
Blank stores all data in main memory. Blank is used for fast access with databases small enough to fit in main memory.
MEMORY
243
Different databases and storage engines support different blank and blank.
table structures and index types
244
Blank supports heap, sorted, hash, and cluster tables. Blank supports only heap and sorted tables.
Oracle Database
MySQL with InnoDB
245
Blank or Blank supports only B+tree indexes. Blank supports both B+tree and hash indexes.
MySQL with InnoDB or MyISAM
MySQL with MEMORY
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.
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.
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.
CREATE INDEX
248
The blank statement deletes a table's index.
DROP INDEX
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.
SHOW INDEX
250
Give the syntax for creating an index
CREATE INDEX IndexName
ON TableName (Column1, Column2, ..., ColumnN);
251
Give the syntax for deleting an index
DROP INDEX IndexName ON TableName;
252
Give the syntax for showing an index
SHOW INDEX FROM TableName;
253
Name the eight columns in the results table of SHOW INDEX
Table
Non_unique
Key_name
Seq_in_index
Column_name
Cardinality
Null
Index_type
254
The blank statement generates a result table that describes how a statement is executed by the storage engine.
EXPLAIN
255
EXPLAIN syntax is simple and uniform in most databases. Name it
EXPLAIN statement;
256
The statement in an EXPLAIN query can be any blank, blank, blank, or blank statement.
SELECT, INSERT, UPDATE, or DELETE
257
Name the eight columns in an EXPLAIN statement results table
select_type
table
type
possible_keys
key
ref
rows
filtered
258
In the select type column of an EXPLAIN table result, what three values are possible and what do they mean
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
259
In the type column of an explain statement results column, what are the possible join values and what do they mean
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
260
In the key column of the EXPLAIN statement, what does NULL mean
NULL indicates a table scan is performed
261
What is the formula for the filtered column in an explain statement results table
Estimated number of rows selected by WHERE clause / estimated number of rows read from table
262
What are the five possible steps a database admin may take in the physical design process?
Create initial physical design.
Identify slow queries.
EXPLAIN slow queries.
Create and drop indexes based on the EXPLAIN result table.
Partition large tables.
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.
primary index
secondary index
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.
MySQL slow query log
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.
EXPLAIN
filtered
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.
index
never used
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.
Partitions
partition
partition
range partition
268
Test
Tes
