Chapter 5 Data Storage Flashcards
1
Q
What is transfer rate?
A
Speed of data reading or writing.
2
Q
What is cost in terms of storage media?
A
Price per gigabyte of storage media.
3
Q
What does capacity refer to?
A
Amount of data a media can store.
6
Q
What is non-volatile memory?
A
Memory retained without power.
7
Q
What are sectors?
A
Data groups on magnetic disks, traditionally 512 bytes.
Also hard-disk drive (HDD)
8
Q
What are pages in flash memory?
Flash aka RAM
A
Data groups in flash memory, 2-16 kilobytes.
9
Q
What are blocks in databases?
A
Uniform size for data transfer in databases.
10
Q
What is a storage controller?
A
The storage controller needs to be able to convert between blocks and the appropriate unit of storage for the type of storage device being used.
Usually between blocks and sectors.
11
Q
What is block size?
A
Uniform size specified by database administrator.
12
Q
What are transactional applications?
A
Access few rows per query, prefer smaller blocks.
15
Q
What is the standard block size in storage systems?
A
4 Kilobytes.
16
Q
What is bulk load?
A
Fast insertion of multiple rows into a table.
17
Q
What is an SQL query?
A
Command to extract or manipulate data.
18
Q
What is a primary key?
A
Unique identifier for each row in a table.
19
Q
What is a NoSQL database?
A
Non-relational database for unstructured data.
21
Q
What is a free space pointer?
A
Reference to available storage in a table.
22
Q
What is a free space linked list?
A
- The primary purpose of a free space linked list is to manage free memory blocks efficiently.
- When memory is allocated to an application, this data structure helps keep track of what portions of memory are available for future allocations.
23
Q
What is an insert operation?
A
Adding data to the first available space.
24
Q
What is an update operation?
A
Modifying existing row data in a table.
25
What is a JOIN query?
Combines rows from two or more tables based on *conditions*.
26
What is a modulo function?
Simple hash function using *division remainder*.
## Footnote
[📖 Hash Fx?](https://g.co/gemini/share/684d25b157fb)
27
What are _efficient_ inserts?
Inserts that quickly *determines row location*
## Footnote
via hash key.
28
Who is a database administrator?
Person managing database structures and performance.
## Footnote
Aka root account
29
What is data distribution?
Method of organizing data **across storage**.
30
What is block allocation?
Process of assigning **additional storage** blocks.
31
What is a cluster key?
A column shared by all interleaved tables.
## Footnote
[Interleaved](https://share.evernote.com/note/37353648-96ef-ef41-af1f-3e8e3cf44573)
32
What is table structure?
*Arrangement of data* in database tables.
33
What is a dense index?
Index with *entries for every* table _row_.
## Footnote
[📖](https://share.evernote.com/note/9a765c9b-7d94-960b-5805-b1c3e916cc94)
34
What is a sparse index?
Index with entries for every table _block_.
## Footnote
[📖](https://share.evernote.com/note/9a765c9b-7d94-960b-5805-b1c3e916cc94)
35
What is an index entry?
## Footnote
Data Address: Data value
**Pointer** to a specific data location.
36
What is block read time?
Time taken to read a block from disk.
37
What is query performance?
Efficiency of retrieving data from tables.
38
What is a bitmap index?
Uses bits to represent **data presence** in rows.
39
What is read query performance?
Logical index that requires *additional read*.
## Footnote
Slower than physical.
40
What is [primary index memory](https://share.evernote.com/note/e4624d3f-ea78-4b71-8b4d-1b78ef402694)?
Often retained in memory *to speed access*.
41
What is a hash table?
Data structure using hash functions for indexing.
## Footnote
Indexing: assigns rows to buckets
42
What is index storage?
Indexes stored in the *same tablespace* as tables.
43
What are concurrent updates?
Simultaneous modifications to multiple tables.
44
What is fragmentation?
Scattering of **data blocks** across storage.
45
What is fast storage media?
Fast storage media refers to storage hardware that *can be accessed quickly*, improving performance for frequently used data.
46
What is the size of sectors used in magnetic memory?
512 bytes
## Footnote
Magnetic memory typically uses 512-byte sectors for data storage.
47
How many sectors are required to store one megabyte in magnetic memory?
Approximately 2000 sectors
## Footnote
One megabyte requires 1,000,000 bytes, which when divided by 512 bytes/sector results in about 2000 sectors. [📖](https://g.co/gemini/share/3046018fc922)
48
What is the size of sectors used in newer storage systems?
- Older standard: 512 bytes per sector
- Newer standard: 4096 bytes (4KB) per sector
49
How many sectors are required to store one megabyte in newer systems with 4KB sectors?
250 sectors
## Footnote
[📖](https://g.co/gemini/share/eb145a226a47)
50
What is the **minimum block size** that must be transferred into memory *from flash memory*?
8 kilobytes
## Footnote
A minimum of one eight-kilobyte block **must be transferred into memory**, despite reading four kilobytes from flash memory.
51
What is the page size of flash memory?
2 kilobytes
## Footnote
Flash memory page size is typically two kilobytes.
52
If a user runs a query that reads two pages of flash memory, how many blocks are transferred to main memory?
1. One block
2. Even though two pages (4 kilobytes total) are read, only one block of eight kilobytes is transferred.
## Footnote
[📖](https://g.co/gemini/share/2e49ea477829)
53
What is transferred to the database in blocks.
**Data transfers** to the database occurs in blocks, not individual bytes.
54
What type of applications are most relational databases optimized for?
Transactional applications.
55
What is row-oriented storage?
A storage method *where an entire row* is stored within one block.
56
When does row-oriented storage perform best?
When *row size is small* relative to block size.
57
What are the two reasons *row-oriented storage* performs best with **small row sizes**?
* Improved query performance
* Less wasted storage
58
What happens to storage when *row size is small* relative to block size?
Wasted space is insignificant.
59
What is a common approach for tables *containing very large columns*?
Each *row contains a link* to the large column stored separately.
60
What types of applications are _some newer_ relational databases optimized for?
Analytic applications.
61
What is column-oriented storage?
A storage method where *each block stores values* for a single column only.
62
What are the benefits of *column-oriented storage* for analytic applications?
* Faster data access
* Better data compression
## Footnote
Also use the same data type
63
Why is data compression more effective in column-oriented storage?
Because all values have the same data type.
64
What is a disadvantage of column-oriented storage for transactional applications?
## Footnote
column-oriented storage is often associated with NoSQL databases
1. Column-oriented storage is _bad_ for *transactions needing full row access*.
2. It requires accessing multiple blocks to retrieve a single row.
3. This is because *data is stored by column*, not by row.
65
Which two relational databases support *column-oriented storage*.
* PostgreSQL
* Vertica
66
Why are NoSQL databases not optimized for transactional applications.
1. NoSQL databases are built for *managing large amounts of unstructured data*, emphasizing high scalability and availability.
2. Transactional applications, on the other hand, require strong consistency and ACID properties, which are not typically prioritized in NoSQL databases.
## Footnote
(atomicity, consistency, isolation, durability)
67
The term *column-oriented* & *columnar* represent _ ?
1. Mean a technique for organizing data on storage media.
2. Sometimes these terms mean a type of NoSQL database, commonly called *wide column database*.
68
What type of storage *performs better than column-oriented storage* for most transactional databases?
Row-oriented storage
69
What are the four alternative table structures *supported by databases*?
* Heap table
* Sorted table
* Hash table
* Table cluster
70
What is a heap table?
A table where no order is imposed on rows
## Footnote
[📖](https://share.evernote.com/note/9223d2c9-49d5-6ab7-ea38-ff63351a4fc7)
71
How does a database manage inserts in a heap table?
Maintains a **list** of blocks and the **address** of the *first available* space for inserts
## Footnote
[📖](https://lite.evernote.com/note/9223d2c9-49d5-6ab7-ea38-ff63351a4fc7)
72
What happens when all blocks in a heap table are full?
The database allocates a new block for inserts
73
What occurs when a row is deleted from a heap table?
The *space occupied by the row* is marked as free
## Footnote
[📖](https://share.evernote.com/note/9223d2c9-49d5-6ab7-ea38-ff63351a4fc7)
74
How is *free space* typically tracked in heap tables?
As a linked list
## Footnote
[📖](https://share.evernote.com/note/998024b0-3288-6d85-354e-cfce28814768)
75
Heap tables optimize which type of operations?
Insert operations
## Footnote
[📖](https://lite.evernote.com/note/9223d2c9-49d5-6ab7-ea38-ff63351a4fc7)
76
1. If *each _table_ in a database* can have a different structure, and *databases assign a default structure to all tables*.
2. How can the *default structure of a table* in a database be modified?
Database administrators can override the default structure to optimize performance for specific queries.
77
What is a heap table?
A heap table is a table structure with no specific order for rows, where *rows are stored in the order they are inserted*.
78
How does a database manage insertions in a heap table?
1. The database maintains a list of blocks and the address of the first available space for inserts.
2. If blocks are full, a new block is allocated.
79
What happens to space when a row is deleted in a heap table?
The available *free space* defined as a space from the deleted row; is recorded and managed using a separate **linked list data structure**.
80
What are the performance characteristics of heap tables?
Heap tables **optimize insert operations** and are particularly fast for bulk loads, but are *not optimal for queries that require rows in a specific order*.
81
What is a sorted table?
A sorted table organizes rows based on a **designated sort column**, typically the primary key.
82
How are rows managed in a *sorted table*?
1. Rows are assigned to **blocks** according to *the value of the sort column*.
2. The databases maintain **pointers** to efficiently manage order during inserts.
83
What are the advantages of using sorted tables?
Sorted tables are optimal for **read queries** that *utilize the sort column*, such as `JOIN`s and `SELECT` *with ranges*.
84
How does a *hash table* assign rows?
A hash table assigns rows to **buckets** based on a hash key, *usually the primary key*, using a hash function.
85
Describe *the storage mechanism* of a hash table.
Rows are distributed across buckets using a hash function, where *each bucket contains a block or group of blocks*.
86
What are the performance benefits of hash tables?
Hash tables are *optimal for inserting and deleting individual rows quickly*, but are inefficient for range-based queries.
87
What is a table cluster?
A table cluster interleaves rows from related tables **based on a cluster key**, typically involving a primary key and *a corresponding foreign key*.
## Footnote
[📖](https://lite.evernote.com/note/9223d2c9-49d5-6ab7-ea38-ff63351a4fc7)
88
How does a table cluster improve query performance?
Table clusters optimize queries that join on the cluster key by *ensuring that related rows are stored physically close together*.
## Footnote
[📖](https://lite.evernote.com/note/9223d2c9-49d5-6ab7-ea38-ff63351a4fc7)
89
What are the limitations of table clusters?
Table clusters are not optimal for queries that join on non-cluster keys or for *reading multiple rows from a single table*.
## Footnote
[📖](https://lite.evernote.com/note/9223d2c9-49d5-6ab7-ea38-ff63351a4fc7)
90
What are the different types of table structures in databases?
* Heap Table
* Sorted Table
* Hash Table
* Table Cluster
91
A sorted table is best for _______ queries.
read-heavy
92
Which table structure are useless for queries that *require specific row ordering*.
1. When you query a **heap table**, the database system may *return rows in _any order_ it finds them*.
2. This order might be based on the physical storage location or the order in which rows were inserted, but it's not predictable or reliable.
93
What happens when a s*orted table's* blocks fill up?
When a block is full, *it splits to create space* for new inserts.
94
What is a heap table?
A type of database table structure that allows for **dynamic storage of data** with free space management.
95
What *happens when* the first row is inserted into a heap table?
The first inserted row is placed at the location *pointed to by the free space pointer*, which is then reset to point to free space B.
## Footnote
[Order 📖](https://g.co/gemini/share/e5abf32979c8)
96
After the second insert in a heap table, where does the free space pointer point?
The free space pointer points to free space C.
## Footnote
[Order 📖](https://g.co/gemini/share/e5abf32979c8)
97
What happens to the free space pointer after the second insert?
It is reset to the space at the end of the block. (Right side)
## Footnote
[🤔](https://g.co/gemini/share/d3f36d793314)
98
What is the purpose of the *free space pointer* in a heap table?
To track available space for new row inserts.
99
List the order of free spaces used during the insertions in the heap table.
* Free space A
* Free space B
* Free space C
* New block
100
What is the significance of allocating a new block in a heap table?
It allows for continued storage of data when the current block becomes full.
101
What occurs after the fourth insert in a heap table?
The fourth insert goes to the beginning of the new block.
102
What is the modulo function 4 steps?
## Footnote
[📖](https://share.evernote.com/note/90eb6b03-e448-8090-ff3f-bb88abf8f9a4)
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.
103
What is a table structure in a database?
A scheme for organizing rows *in blocks* on storage media.
104
What are the four alternative table structures supported by databases?
* Heap table
* Sorted table
* Hash table
* Table cluster
105
What is the default behavior of databases regarding table structures?
Databases assign a default structure to all tables.
106
Can database administrators modify the default table structure?
Yes, to optimize performance for specific queries.
107
Describe the organization of rows in a heap table.
No order is imposed on rows; a list of blocks and first available space *for inserts is maintained*.
108
How do heap tables optimize insert operations?
They are particularly fast for **bulk load** of many rows since rows are stored in load order.
109
Are heap tables optimal for reading rows in a specific order?
No, they are not optimal for such queries as rows are scattered randomly.
110
What is the purpose of a hash function in a hash table?
To compute the bucket, *containing the row* from the hash key.
## Footnote
[📖](https://share.evernote.com/note/90eb6b03-e448-8090-ff3f-bb88abf8f9a4)
111
What does the modulo function do in hash tables?
It determines the bucket number *from the hash key*.
112
What are **deep buckets** in hash tables?
Buckets that contain *long chains of linked blocks* due to fixed hash function allocation.
## Footnote
[📖 Fixed](https://share.evernote.com/note/90eb6b03-e448-8090-ff3f-bb88abf8f9a4)
113
What is the advantage of using dynamic hash functions?
They *automatically allocate more blocks* and distribute rows across all buckets.
## Footnote
[📖](https://share.evernote.com/note/90eb6b03-e448-8090-ff3f-bb88abf8f9a4)
114
When are hash tables optimal?
* Inserts and deletes of individual rows
* Selecting a single row *with specified hash key* value
115
Why are hash tables slow for certain queries?
They are slow on queries that select many rows with a *range of values*.
116
What determines the physical row order in a sorted table?
A sort column identified by the database designer.
117
What is the *typical use* of the sort column in sorted tables?
Usually the primary key, but can be a non-key column or group of columns.
118
What is a potential downside of maintaining sorted tables?
Maintaining correct sort order can be slow during inserts and updates.
119
What happens when a block in a sorted table is full?
The block splits in two, moving *half the rows* to a new block.
120
What are table clusters also known as?
Multi-tables.
121
How are rows stored in table clusters?
Rows of two or more tables are *interleaved in the same storage area* based on a cluster key.
122
What is the function of *the cluster key* in table clusters?
It determines the order in which rows are interleaved.
123
Why are table clusters optimal for certain queries?
They are optimal when joining interleaved tables on the cluster key.
124
What are the performance _issues_ with table clusters for **other queries**?
1. Retrieving many rows from a table within the cluster, when *they are not related by the cluster key*, can be inefficient.
2. Joining tables using columns that aren't the primary *key of the cluster* can be slow.
3. Changing the value of the cluster key in a row requires moving the row to a different physical location in the storage, which can be expensive.
125
What is the steps for Calculating total size (Storage Metrics) in Database Management
126
What is the steps for Calculating the *number of rows* (Storage Metrics) in Database Management
127
Fill in the blanks
128
What is the formula for a table scan?
Number of rows `/` rows per table block
129
How do you calculate a single-level index scan?
1. Number of index blocks
2. `+` referenced table blocks
130
What is the formula for a single-level index binary search?
1. log base 2 (number of index blocks)
2. `+` referenced table blocks
## Footnote
This means you calculate the log base 2 of the number of index blocks
131
How is a "multi-level dense index" search calculated?
1. log base fan-out (number of rows), rounded up
2. `+` referenced table blocks
## Footnote
This means you calculate the log base of the number of rows +..
132
What is the formula for a "multi-level sparse" index search?
1. log base fan-out (number of rows / rows per table block), rounded up,`+`
2. referenced table block
133
Can a hash table have a primary index?
## Footnote
A primary index is a file that helps organize data in a database table
No, hash tables cannot have a primary index
## Footnote
Rows of a hash table are not stored in sort order.
134
What type of indexes can a hash table have?
Secondary indexes
## Footnote
Secondary indexes can be any structure, including hash.
135
How is a primary index usually structured?
As a sparse multi-level index
## Footnote
In principle, a primary index can be structured as a hash index.
136
What determines the location of a row containing a hash key value?
Applying a hash function to a hash key
## Footnote
Hash keys do not store table block pointers.
137
Are hash keys implemented as single- or multi-level indexes?
No, hash keys are not implemented as single- or multi-level indexes.
138
Can a hash index be sparse?
Yes, a hash index can be sparse.
139
What does each column value appear as in a hash index?
An index entry in a bucket.
140
Are hash indexes ever sparse?
No, hash indexes are never sparse.
141
Fill in the blank: A hash table can have a _______ index.
hash index.
142
1. What are the steps to locate rows containing a table value for the following index?
2. Hash Index
1. Apply the hash function to the column value to compute a bucket number
2. Read the index blocks for the bucket number.
3. Find the index entry for the column value and read the table block pointer.
4. Read the table block containing the row.
143
1. What are the steps to locate rows containing a table value for the following index?
2. Bitmap Index
1. Determine the index column coresponding to the table value.
2. Read the index column and find index rows that are set to 'one'.
3. Determine table rows corresponding to the index rows.
4. Determine pointers to blocks containing the table rows.
144
1. What are the steps to locate rows containing a table value for the following index?
2. Logical Index
1. Look up the column value in the logical index to find the primary key value.
2. Look up the primary key value in the primary index to find the table block pointer.
3. Read the table block containing the row.
145
1. What are the steps to locate rows containing a table value for the following index?
2. Function Index
1. Specify a function on the column value to transform stored values.
2. Use the transformed values in the index to process queries instead of the original column values.
146
What is a partition in the context of database tables?
A partition is a subset of table data
147
How does a range partition assign rows to partitions?
Associates each partition with a range of partition expression values using VALUES LESS THAN keywords
148
What keyword represents the highest column value in a range partition?
MAXVALUE
149
What does VALUES LESS THAN MAXVALUE specify in a range partition?
The highest range
150
How are partitions named in a range partition?
Each partition is explicitly named by the database administrator
151
What is a list partition?
Associates each partition with an explicit list of partition expression values using the VALUES IN keywords
152
What type of partition requires a partition expression with positive integer values?
Hash partition
153
How is the partition number computed in a hash partition?
(partition expression value) modulo N
154
What is a key partition in MySQL?
Similar to a hash partition, but the partition expression is determined automatically by the database
155
What is the default behavior for tablespaces in most databases?
Automatically create one tablespace for each table
156
What is one benefit of storing frequently accessed tables in separate tablespaces?
Improves query performance
157
What happens to the associated file when a table is dropped?
The associated file is deleted and storage is released
158
What is the impact of fragmentation on query performance?
Queries that scan tables on heavily fragmented files are slow
159
What is the advantage of storing one table per file?
Minimizes fragmentation and optimizes table scans
160
How do partitions improve query performance?
By reducing the amount of data accessed by INSERT, UPDATE, DELETE, and SELECT statements
161
What is the difference between horizontal and vertical partitioning?
Horizontal partitioning is a subset of table rows; vertical partitioning is a subset of table columns
162
Which type of partitioning do MySQL and most relational databases use?
Horizontal partitioning
163
What happens to table indexes when a table is partitioned?
Table indexes are also partitioned
164
Fill in the blank: Each partition contains index entries only for rows in the _______.
partition
165
What are the restrictions on partitioned tables in MySQL?
A partitioned table may not contain foreign keys and foreign keys may not refer to a partitioned table
## Footnote
This restriction limits the usability of partitions in MySQL.
166
What must all partition columns do in MySQL?
All partition columns must appear in all unique columns, including the primary key, of the partitioned table
## Footnote
This requirement can complicate table design and limits flexibility.
167
Why do partitions lack value in MySQL?
Due to the restrictions and requirements, partitions have limited value in MySQL.
168
Which storage engines support partitioning in MySQL?
InnoDB and NDB
## Footnote
MyISAM does not provide native partitioning support.
169
What does the PARTITIONS table in the INFORMATION_SCHEMA database provide?
Insights into the partitioned tables and their structures
## Footnote
This table helps users understand how their partitioning is set up.
170
True or False: MyISAM supports native partitioning in MySQL.
False
## Footnote
MyISAM does not provide native partitioning support.
171
What is the purpose of documenting known restrictions and limitations in MySQL 8.0 regarding partitioning?
User awareness
## Footnote
This helps users understand the constraints they may encounter.
172
Fill in the blank: Partitioning is supported only by _______ and NDB storage engines.
InnoDB
173
174
What does logical design specify?
Tables, columns, and keys
## Footnote
Logical design is an essential part of database schema design.
175
What does physical design specify?
Indexes, table structures, and partitions
## Footnote
Physical design focuses on how data is stored and accessed.
176
How does physical design affect query performance?
It affects query performance but never affects query results
## Footnote
This distinction is crucial for database design.
177
What is the role of a storage engine?
Translates instructions generated by a query processor into low-level commands that access data on storage media
## Footnote
Storage engines manage how data is stored and retrieved.
178
What is the default storage engine installed with MySQL?
InnoDB
## Footnote
InnoDB is widely used for its transaction support and referential integrity features.
179
What are the key features of InnoDB?
* Full support for transaction management
* Foreign keys
* Referential integrity
* Locking
## Footnote
These features make InnoDB suitable for many applications requiring data integrity.
180
What are the transaction management capabilities of MyISAM?
Limited transaction management and locking capabilities
## Footnote
MyISAM is often used in scenarios with fewer data updates.
181
What type of applications is MyISAM commonly used for?
Analytic applications with limited data updates
## Footnote
MyISAM's design is better suited for read-heavy workloads.
182
What does the MEMORY storage engine do?
Stores all data in main memory
## Footnote
MEMORY is used for fast access with databases small enough to fit in main memory.
183
Which table structures does Oracle Database support?
* Heap
* Sorted
* Hash
* Cluster
## Footnote
Oracle's flexibility in table structures is beneficial for various applications.
184
What table structures does MySQL with InnoDB support?
* Heap
* Sorted
## Footnote
InnoDB's support is limited compared to other databases.
185
What index types does MySQL with InnoDB or MyISAM support?
B+tree indexes
## Footnote
B+tree indexes are commonly used for efficient data retrieval.
186
What index types does MySQL with MEMORY support?
* B+tree
* Hash
## Footnote
The MEMORY engine's support for hash indexes allows for faster lookups in certain scenarios.
187
What does the column 'select_type' indicate in the EXPLAIN statement output?
The type of the query being executed, such as SIMPLE, PRIMARY, or SUBQUERY.
## Footnote
- SIMPLE: A straightforward query without nesting or unions.
- PRIMARY: The main or outer SELECT in a nested query.
- SUBQUERY: An inner SELECT inside a nested query.
188
What information does the 'table' column provide in the EXPLAIN output?
The name of the table being described in that row of the EXPLAIN result.
## Footnote
This helps identify which table's data is being analyzed in the query.
189
What does the 'type' column describe in the EXPLAIN statement?
The join type being used in the query, such as const, range, eq_ref, or ALL.
## Footnote
- const: The table has a row that matches at most one condition.
- range: The query uses a constant to filter rows.
- eq_ref: One row from this table is selected for each matching row of another table.
- ALL: Every row from the table is scanned.
190
What does 'possible_keys' represent in the EXPLAIN output?
Indexes that could potentially be used to speed up the query.
191
What does the 'key' column indicate in the output of the EXPLAIN statement?
The specific index that the database has decided to use for the query.
## Footnote
If it's NULL, no index was used and a full table scan was performed.
192
What information does the 'ref' column provide in the EXPLAIN output?
Constants or expressions compared with the selected index.
193
What does the 'rows' column estimate in the context of an EXPLAIN query?
How many rows will be read from the table to execute the query.
194
What does the 'filtered' column estimate in the EXPLAIN output?
The number of rows that qualify based on the query conditions.
