Choosing larger database block sizes Which of the following is NOT a key factor when deciding to switch from a small to a large block/page size?
-
AThe total byte length of each table row (including overhead).
-
BThe number of columns in the table, independent of their widths and usage.
-
CBlock contention under concurrent access (buffer/latch pressure).
-
DRandom row access performance and latency characteristics.
-
ETypical access pattern (sequential scans versus point lookups).
Answer
Correct Answer: The number of columns in the table, independent of their widths and usage.
Explanation
Introduction / Context:DBMSs let you choose a page size (e.g., 4 KB, 8 KB, 16 KB). Larger pages can improve sequential throughput but may increase random-read latency and contention. This question separates signal (row length, contention, access pattern) from noise (column count alone).
Given Data / Assumptions:
- Workloads vary: OLTP point lookups versus analytics scans.
- Row length and row density shape I/O efficiency.
- Concurrency and buffer behavior matter for hot pages.
Concept / Approach:
Decisions should hinge on row size, expected scan vs lookup ratios, and observed contention. The raw count of columns is not meaningful by itself; widths, nullability, and access patterns matter far more than the mere number of attributes.
Step-by-Step Solution:
1) Evaluate row size to estimate blocking factor changes.2) Assess workload: large scans like larger blocks; random lookups may prefer smaller ones.3) Measure contention and cache residency for hot objects.4) Conclude that “number of columns” alone is not a deciding factor.Verification / Alternative check:
Vendor tuning guides focus on row width, access patterns, and concurrency. Column count is at best a proxy for width and is not decisive independently.
Why Other Options Are Wrong:
- Row length directly affects how many rows fit per page.
- Contention and random access cost are central to sizing trade-offs.
- Access pattern is fundamental to page-size benefits.
Common Pitfalls:
- Assuming larger pages are always better—they can hurt cache efficiency for OLTP.
- Ignoring per-row and per-page overhead when modeling effects.
Final Answer:
The number of columns in the table, independent of their widths and usage.