Data Storage Flashcards
(128 cards)
1
Q
Access ZTime
A
Time required to access the first byte in a read or write operation
2
Q
Transfer Rate
A
speed at which data is read or written. following initial access.
3
Q
Volatile memory
A
Memory that is lost when disconnected from power.
4
Q
Non-volatile memory
A
Memory is retained when power is lost
5
Q
Main Memory/RAM
A
Primary memory used when computer programs execute
6
Q
Flash Memory/SSD
A
less expensive and higher capacity than main memory
7
Q
Magnetic Disk/HDD
A
used to store large amounts of data
8
Q
Sectors
A
Magnetic disk groups data in sectors, traditionally 512 bytes per sector but 4 kilobytes with newer disk formats
9
Q
Pages
A
Flash memory groups data in pages, usually between 2 kilobytes and 16 kilobytes per page
10
Q
Block
A
Databases and file systems use a uniform size, called a block, when transferring data between main memory and storage media
11
Q
Row-oriented storage
A
Relational databases usually store an entire row within one block, which is called row-oriented storage
12
Q
Column-oriented/Columnar Storage
A
Each block stores values for a single column only.
13
Q
Table Structure
A
Scheme for organizing rows in blocks on storage media
14
Q
heap table
A
no order is imposed on rows
15
Q
Sorted Table/Sort Column
A
Database designer identifies a sort column that determines physical row order
16
Q
Hash Table
A
Rows are assigned to buckets
17
Q
Bucket
A
Block or group of blocks containing rows
18
Q
Hash Key
A
Column or group of columns, usually the primary key
19
Q
hash Function
A
Computes the bucket containing the row from the hash key
20
Q
Modulo Function
A
Simple has function with four steps
21
Q
Dynamic hash function
A
Automatically allocates more blocks to the table, creates additional buckets, and distributes rows across al buckets. With more buckers, fewer rows are assigned to each bucket and, on average, buckets contain fewer linked blocks.
22
Q
Table clusts/Multi-Tables
A
Interleave rows of two or mote tables in the same storage area.
23
Q
Cluster key
A
Column that is available in all interleaved tables
24
Q
Single-level index
A
File containing column values, along with pointers to rows containing the column value
25
Multi-level index
Each index entry is a composite of values from all indexed columns.
26
Table scan
Database operation that reads table blocks directly, without access an index
27
Index Scan
Database operation that reads index blocks sequentially, in order to locate the needed table blocks
28
Hit Ratio/Filter facotr/Selectivity
Percentage of table rows selected by a query
29
Binary Search
Database repeatedly splits the indexed in two until it finds the entry containing the search value
30
Primary Index/Clustering index
Index on a sort column
31
Dense index
Contains an entry for every table rows
32
Sparse index
Contains an entry for every table block
33
multi-level indexd
Stores column values and rows pointer s in a hierarchy
34
Fan-out
Number of index entries per block is called the fan-out of a multi-level index
35
Branch
Top-level block to a bottom-level block is called a branch.
36
balanced/imbalanced
when all branches are the same length and imbalanced when branches are different lengths
37
B+tree
All indexed values appear in the bottom level. Pointers to table blocks appear only in the bottom level. Values are occasionally repeated in the index
38
B-Tree
Indexed values appear 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
39
Hash index
INdex entries are assigned to buckets
40
BUcket
Block or group of blocks containing index entries
41
hash Function
Index entry is determined by a hash function, which computes a bucket number from the value of the indexed column
42
Bitmap Index
Grid of Bits
43
Physical INdexd
Single or multi-level index normally contains pointers to table blocks
44
Logical index
Single or multi-level index in which pointers to table blocks are replaced with primary key values.
45
Function index
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
46
Tablespace
Database object that maps one or more tables to a single file
47
fragmented
Files are updated, blocks become scattered, or fragmented, across many tracks
48
partition
subset of table data. One table has many partitions that do not overlap, and together, contain all table data
49
Horizontal Partition
Subset of table rows
50
Vertical partition
Subset of table columns
51
Shard
Subset of table data, usually a subset of rows rather than columns. Stored on different computers in a distributed database
52
Partition Expression/Partition Columns
Specifies a partition expression based on one or more partition columns. Partition expression may be simple, such as the value of a single partition column, or a complex based on several partition columns. Rows are assigned to partitions in different ways.
53
Range partition
Associates each partition with a range of parittion expression values. VALUES LESS THAN keywords specify the upper bound of each range. MAXVALUE keyword represents the highest column value, and VALUES LESS THAN MAXVALUE specifies the highest range.
54
List Partition
Associates each partition with an explicit list of partition expression values using the VALUES IN keywords.
55
Hash Parititon
A partition expression with positive integer values. Database administrator specifies the number of partitions, N, and partitions are automatically named p0 through p(n-1). Partition number for each row is computed as: (Partition expression value) modulo N
56
Key Parition
Partition expression is determined automatically by the database
57
Logical Design
Specifies tables, columns, and keys.
58
Physical Design
Specifies indexes, table structures, and partitions. Physical design affects query performance but never affects query results
59
Storage Engine/Storage Manager
Translates instructions generated by a query processor into low-level commands that access data on storage media. Storage engines support different index and table structures, so physical design is dependent on a specific storage engine.
60
CREATE INDEX
statement creates an index by specifying the index name and table columns that compose the index
61
DROP INDEX
Statement deletes a table's index
62
SHOW INDEX
Statement displays a table's index
63
EXPLAIN
Statement generates a result table that describes how a statement is executed by the storage engine
64
slow query low
File that records all long-running queries submitted to the database
65
Time required to access the first byte in a read or write operation
Access ZTime
66
speed at which data is read or written. following initial access.
Transfer Rate
67
Memory that is lost when disconnected from power.
Volatile memory
68
Memory is retained when power is lost
Non-volatile memory
69
Primary memory used when computer programs execute
Main Memory/RAM
70
less expensive and higher capacity than main memory
Flash Memory/SSD
71
used to store large amounts of data
Magnetic Disk/HDD
72
Magnetic disk groups data in sectors, traditionally 512 bytes per sector but 4 kilobytes with newer disk formats
Sectors
73
Flash memory groups data in pages, usually between 2 kilobytes and 16 kilobytes per page
Pages
74
Databases and file systems use a uniform size, called a block, when transferring data between main memory and storage media
Block
75
Relational databases usually store an entire row within one block, which is called row-oriented storage
Row-oriented storage
76
Each block stores values for a single column only.
Column-oriented/Columnar Storage
77
Scheme for organizing rows in blocks on storage media
Table Structure
78
no order is imposed on rows
heap table
79
Database designer identifies a sort column that determines physical row order
Sorted Table/Sort Column
80
Rows are assigned to buckets
Hash Table
81
Block or group of blocks containing rows
Bucket
82
Column or group of columns, usually the primary key
Hash Key
83
Computes the bucket containing the row from the hash key
hash Function
84
Simple has function with four steps
Modulo Function
85
Automatically allocates more blocks to the table, creates additional buckets, and distributes rows across al buckets. With more buckers, fewer rows are assigned to each bucket and, on average, buckets contain fewer linked blocks.
Dynamic hash function
86
Interleave rows of two or mote tables in the same storage area.
Table clusts/Multi-Tables
87
Column that is available in all interleaved tables
Cluster key
88
File containing column values, along with pointers to rows containing the column value
Single-level index
89
Each index entry is a composite of values from all indexed columns.
Multi-level index
90
Database operation that reads table blocks directly, without access an index
Table scan
91
Database operation that reads index blocks sequentially, in order to locate the needed table blocks
Index Scan
92
Percentage of table rows selected by a query
Hit Ratio/Filter facotr/Selectivity
93
Database repeatedly splits the indexed in two until it finds the entry containing the search value
Binary Search
94
Index on a sort column
Primary Index/Clustering index
95
Contains an entry for every table rows
Dense index
96
Contains an entry for every table block
Sparse index
97
Stores column values and rows pointer s in a hierarchy
multi-level indexd
98
Number of index entries per block is called the fan-out of a multi-level index
Fan-out
99
Top-level block to a bottom-level block is called a branch.
Branch
100
when all branches are the same length and imbalanced when branches are different lengths
balanced/imbalanced
101
All indexed values appear in the bottom level. Pointers to table blocks appear only in the bottom level. Values are occasionally repeated in the index
B+tree
102
Indexed values appear 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
B-Tree
103
INdex entries are assigned to buckets
Hash index
104
Block or group of blocks containing index entries
BUcket
105
Index entry is determined by a hash function, which computes a bucket number from the value of the indexed column
hash Function
106
Grid of Bits
Bitmap Index
107
Single or multi-level index normally contains pointers to table blocks
Physical INdexd
108
Single or multi-level index in which pointers to table blocks are replaced with primary key values.
Logical index
109
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
Function index
110
Database object that maps one or more tables to a single file
Tablespace
111
Files are updated, blocks become scattered, or fragmented, across many tracks
fragmented
112
subset of table data. One table has many partitions that do not overlap, and together, contain all table data
partition
113
Subset of table rows
Horizontal Partition
114
Subset of table columns
Vertical partition
115
Subset of table data, usually a subset of rows rather than columns. Stored on different computers in a distributed database
Shard
116
Specifies a partition expression based on one or more partition columns. Partition expression may be simple, such as the value of a single partition column, or a complex based on several partition columns. Rows are assigned to partitions in different ways.
Partition Expression/Partition Columns
117
Associates each partition with a range of parittion expression values. VALUES LESS THAN keywords specify the upper bound of each range. MAXVALUE keyword represents the highest column value, and VALUES LESS THAN MAXVALUE specifies the highest range.
Range partition
118
Associates each partition with an explicit list of partition expression values using the VALUES IN keywords.
List Partition
119
A partition expression with positive integer values. Database administrator specifies the number of partitions, N, and partitions are automatically named p0 through p(n-1). Partition number for each row is computed as: (Partition expression value) modulo N
Hash Parititon
120
Partition expression is determined automatically by the database
Key Parition
121
Specifies tables, columns, and keys.
Logical Design
122
Specifies indexes, table structures, and partitions. Physical design affects query performance but never affects query results
Physical Design
123
Translates instructions generated by a query processor into low-level commands that access data on storage media. Storage engines support different index and table structures, so physical design is dependent on a specific storage engine.
Storage Engine/Storage Manager
124
statement creates an index by specifying the index name and table columns that compose the index
CREATE INDEX
125
Statement deletes a table's index
DROP INDEX
126
Statement displays a table's index
SHOW INDEX
127
Statement generates a result table that describes how a statement is executed by the storage engine
EXPLAIN
128
File that records all long-running queries submitted to the database
slow query low
