Physical Design, Storage and Performance

Introduction

A data warehouse may contain billions of rows, yet its usefulness depends less on how much data it stores than on how efficiently it can answer analytical questions. Physical design translates a logical warehouse model into files, partitions, indexes, storage structures, execution plans, and resource policies. It therefore connects data modelling with operational performance and cost.

Physical design is not simply an exercise in making queries faster. A design must balance response time, storage consumption, data-loading speed, maintenance effort, concurrency, availability, scalability, and governance. A structure that accelerates one dashboard may slow daily loading, increase storage cost, or harm other workloads. Consequently, physical design is an optimization problem involving competing objectives.

Modern platforms complicate this task further. Traditional row-oriented relational systems coexist with columnar tables, cloud object storage, massively parallel processing (MPP), automatic clustering, adaptive query optimization, and workload-aware resource management. The underlying principles remain stable: reduce unnecessary data access, exploit locality, minimize data movement, perform selective computation, and allocate resources according to business priorities.

Learning Objectives

By the end of this chapter, a student should be able to:

1. Explain the relationship between logical modelling and physical warehouse design.

2. Select appropriate indexes, partitions, clustering strategies, and compression methods.

3. Compare row-oriented and column-oriented storage for analytical workloads.

4. Design materialized views and aggregate structures with awareness of maintenance cost.

5. Explain parallel query processing, data movement, workload management, and query optimization.

6. Evaluate performance using measurable service, cost, and operational criteria.

7. Recommend a defensible physical design for a realistic warehouse scenario.

1. Physical Design as a Multi-Objective Decision

Logical design specifies entities, facts, dimensions, keys, relationships, grain, and business meaning. Physical design determines how those structures are represented by a database engine. Typical decisions include:

• file and table organization;
• row or column orientation;
• partition keys and partition granularity;
• clustering, sorting, or distribution keys;
• indexes and statistics;
• compression and encoding;
• materialized views and aggregate tables;
• parallel execution and resource allocation;
• caching and storage tiers.

A useful conceptual model is:

Total cost = storage cost + processing cost + administration cost + performance penalty cost.

This is a decision framework rather than a universal accounting formula. Its terms represent organizational cost categories and may be measured differently by different platforms. For example, performance penalty cost may include lost analyst time, missed service-level agreements (SLAs), or delayed operational decisions.

Performance must also be defined precisely. Important measures include:

• Latency: elapsed time for one query or report.
• Throughput: amount of work completed in a period, such as queries per hour.
• Concurrency: number of simultaneous workloads supported acceptably.
• Availability: proportion of time the service is usable.
• Freshness: age of data visible to users.
• Predictability: consistency of response time under varying load.

A warehouse serving executive dashboards may prioritize predictable low latency, whereas a data science platform may prioritize throughput for large scans. There is no universally optimal physical design.

2. Access Patterns and Workload Characterization

Physical structures should be selected from observed or anticipated access patterns, not from habit. A workload profile should record:

• commonly filtered columns;
• join keys and join frequency;
• grouping and aggregation patterns;
• selected columns and projection width;
• data-load frequency and volume;
• query concurrency and time-of-day peaks;
• acceptable response times;
• update, correction, and deletion patterns.

A query that selects two columns from a fact table containing one hundred columns is a strong candidate for columnar storage because unused columns need not be read. A query that retrieves a complete individual transaction row may benefit more from row locality.

Selectivity is central to index and partition decisions. Informally, a predicate is selective when it returns a small fraction of a table. If a table has 100 million rows and a predicate returns 100,000 rows, its returned fraction is 0.001, or 0.1%. High selectivity often supports indexed access, although the optimizer must also consider access overhead, row distribution, and physical layout.

The correct design process begins with representative workloads. Query logs, execution plans, table statistics, and load histories are more reliable than assumptions based solely on schema diagrams.

3. Indexing Strategies

An index is an auxiliary access structure that helps locate rows or values without scanning the entire table. It usually contains indexed values and references to associated data. Indexes can improve selective filtering, joins, ordering, and uniqueness enforcement, but they consume storage and impose maintenance overhead during data loading or modification.

3.1 B-tree and related indexes

B-tree indexes organize key values in a balanced tree. They are effective for equality and range predicates such as:

```sql

WHERE customer_id = 1042

WHERE sale_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'

```

They are less attractive when a predicate returns a large fraction of the table, when the indexed column has very low usefulness for filtering, or when a full scan is cheaper because the table is small or stored in a columnar format.

A composite index contains multiple columns. Column order matters. An index on `(region, sale_date)` generally supports predicates beginning with `region` more directly than a predicate on `sale_date` alone. The best order depends on filtering, join, ordering, and engine-specific behavior; the column with the highest cardinality is not automatically the correct first column.

3.2 Bitmap and encoded approaches

Bitmap indexes represent the presence of values using bit patterns. They can be effective for low- or moderate-cardinality attributes, such as gender, status, or region, especially in read-intensive warehouses. They may perform poorly under frequent concurrent updates because many rows can be affected by one value change. Some columnar systems use bitmap-like encodings internally, but this should not be confused with creating a user-visible bitmap index.

3.3 Join, covering, and specialized indexes

Join indexes or equivalent structures can reduce the cost of repeatedly joining large fact and dimension tables. A covering index includes all columns required by a query, allowing the engine to answer it without accessing the base table. Spatial, full-text, and approximate indexes may be appropriate for specialized workloads, but they should be introduced only when their workload and maintenance benefits are demonstrated.

3.4 Index selection principles

Indexing decisions should consider:

1. predicate selectivity and frequency;

2. join frequency and join cardinality;

3. table size and physical organization;

4. load and refresh overhead;

5. storage cost;

6. whether partition pruning or column pruning already solves the problem.

In a highly compressed columnar warehouse, conventional indexes may contribute little because the engine scans only relevant columns and uses zone maps, min–max metadata, dictionaries, or data skipping. Indexes must therefore be evaluated in the context of the storage engine rather than treated as universally beneficial.

4. Partitioning and Clustering

Partitioning divides a table into separately managed physical units. The optimizer may eliminate irrelevant partitions, a process called partition pruning. Partitioning can improve query performance, loading, archival, and maintenance, but excessive partitioning creates metadata overhead and many small files or partitions.

4.1 Partitioning methods

Range partitioning divides data into intervals, commonly by date. It is a natural choice for time-variant facts and supports rolling retention. For example, monthly partitions permit a query for March to avoid reading January, February, and later months.

List partitioning assigns explicit values or categories to partitions, such as country or business unit. It is useful when categories are stable and sufficiently large, but it can produce imbalance when one category dominates.

Hash partitioning applies a hash function to a key and distributes rows across partitions. It can balance data and support parallelism, although it generally does not provide useful pruning for range predicates.

Composite partitioning combines methods, such as range by month and hash by customer. It can address multiple requirements but increases design and administration complexity.

Partition pruning depends on predicate form and data types. A direct predicate on the partition key is usually easier to prune than a function applied to that key. For example, a half-open interval such as `sale_date >= '2025-01-01' AND sale_date < '2025-02-01'` is often preferable to applying a function to `sale_date`, although exact behavior is platform dependent.

4.2 Clustering and sorting

Clustering places rows with similar values physically near one another, often within partitions or files. Sorting by date, customer, or region can improve data skipping and compression. Unlike strict partitioning, clustering may be approximate or maintained incrementally. It is especially useful when queries filter on several columns but creating partitions for every combination would be impractical.

A major risk is data skew: a small number of keys receive a disproportionate number of rows. Skew causes uneven storage, overloaded processing nodes, and stragglers that delay a parallel query. Distribution keys should therefore be tested against real frequency distributions.

4.3 Choosing partition keys

A good partition key is commonly used in filters, has a stable value, supports lifecycle management, and produces reasonably sized partitions. Date is often effective in event warehouses, but it is not automatically correct. If almost every query filters by customer and only a minority filters by date, customer clustering or distribution may be more valuable. Partition size should also be operationally meaningful: tiny partitions increase overhead, while huge partitions reduce pruning and maintenance benefits.

5. Compression and Storage Representation

Compression reduces physical storage and data transferred from storage to processing units. In analytical systems, compression can improve performance because fewer bytes must be read, even though decompression consumes CPU.

Common techniques include:

• Dictionary encoding: replaces repeated values with compact codes.
• Run-length encoding: stores a value and its repetition count; it benefits from sorted repeated values.
• Delta encoding: stores differences between successive values; it suits ordered numeric or temporal data.
• Bit packing: stores small-range integers using fewer bits.
• General-purpose block compression: compresses blocks using algorithms such as LZ-family or modern alternatives.

Compression effectiveness depends on cardinality, ordering, type, and data distribution. A low-cardinality status column usually compresses well with dictionary encoding. A high-entropy identifier column may compress poorly. Sorting can improve both compression and skipping, but sorting itself creates load and maintenance cost.

Compression ratio is commonly defined as:

Compression ratio = uncompressed size / compressed size.

For example, 1,000 GB compressed to 250 GB has a ratio of 4:1. The ratio is dimensionless, and it should be stated whether metadata, indexes, replicas, and encoding dictionaries are included. Compression ratio alone is not a sufficient performance measure; CPU overhead, scan bandwidth, and workload behavior must also be tested.

6. Row-Oriented and Columnar Storage

Row-oriented storage places the attributes of a record together. It is appropriate when applications frequently retrieve or modify complete records, perform point lookups, or execute transactional operations.

Columnar storage places values from the same attribute together. Analytical queries often access a small subset of columns across many rows, so columnar storage reduces I/O through column pruning. It also supports vectorized execution, compression, and data skipping.

Consider a fact table with 100 columns and a query that reads 5 columns. An idealized columnar system reads approximately 5/100 of the column payload, or 5%, before accounting for metadata, compression, joins, and filters. This is not a guaranteed runtime ratio; it is an explanation of why columnar storage can be efficient.

Columnar storage is not universally superior. Very wide point lookups, frequent single-row updates, and workloads requiring complete records may favor row organization. Many modern warehouses use immutable or append-oriented columnar files and handle corrections through merges, versioning, or periodic compaction.

Important columnar concepts include:

• Column pruning: reading only referenced columns.
• Predicate pushdown: applying filters as close to storage as possible.
• Vectorized execution: processing batches of values using CPU-efficient operations.
• Zone maps or min–max metadata: skipping blocks whose value ranges cannot satisfy a predicate.
• Small-file problem: performance degradation caused by excessive small files and metadata operations.

7. Materialized Views and Aggregation

A materialized view stores the result of a query physically rather than recomputing it for every request. It can accelerate repeated joins, filters, and aggregations. An aggregate table is a deliberately designed stored summary, such as monthly sales by product and region.

Suppose a fact table contains daily transactions, while most reports request monthly revenue by product category. A materialized monthly summary can avoid scanning transaction-level rows. However, its benefits depend on query matching, refresh frequency, incremental maintenance capability, and storage cost.

Materialized views may be refreshed:

• On demand: explicitly refreshed by a job or administrator.
• Scheduled: refreshed at fixed intervals.
• Incrementally: updated using only changed data, when supported.
• Continuously: maintained as source changes arrive, where platform capability permits.

The design must document freshness. A dashboard showing data delayed by one hour may be acceptable, while regulatory reporting may require a controlled and reproducible snapshot.

Aggregation requires attention to additive behavior. Additive measures can be summed across all relevant dimensions, such as sales amount. Semi-additive measures can be summed across some dimensions but not time, such as account balance. Non-additive measures, such as ratios and percentages, should generally be recomputed from appropriate base components rather than summed. For example, an overall conversion rate should be calculated as total conversions divided by total eligible events, not as the average of daily conversion rates unless weighting conditions justify it.

Aggregate design can be viewed as a workload-selection problem: materialize summaries that eliminate substantial repeated work and are frequently used, while avoiding a proliferation of overlapping structures.

8. Parallel Processing and Distributed Execution

Large warehouses commonly use parallel processing. A query is divided into tasks executed by multiple workers. Typical stages include scanning, filtering, repartitioning, joining, aggregating, and writing results.

Parallelism is effective when work is divided evenly and communication is limited. A distributed join may use:

• Broadcast join: copy a small table to workers holding the large table.
• Shuffle or repartition join: redistribute both inputs by join key.
• Local join: join data already colocated by the relevant key.

Broadcasting avoids redistribution of the large table but becomes expensive when the supposedly small table is larger than expected. Shuffling supports general joins but can generate substantial network traffic. Data skew can cause one worker to receive much more data than others, creating a straggler.

Amdahl’s law provides a conceptual limit to speedup. If fraction `s` of a workload is inherently serial and the remaining fraction `1-s` can use `p` processors, ideal speedup is:

S(p) = 1 / [s + (1-s)/p].

Here, `0 ≤ s ≤ 1`, `p ≥ 1`, and the assumptions are fixed workload, ideal balancing, no additional parallel overhead, and a clearly defined serial fraction. Real systems achieve less than this bound because of synchronization, network transfer, startup, skew, storage contention, and scheduling. If `s = 0.10` and `p = 8`, ideal speedup is `1/(0.10 + 0.90/8) = 1/0.2125 ≈ 4.71`, not 8.

Parallel execution is therefore not simply a matter of adding nodes. Data layout, distribution keys, join strategy, partition balance, and resource contention determine actual benefit.

9. Workload Management

A warehouse commonly serves competing workloads: scheduled extracts, interactive dashboards, ad hoc analysis, data loading, machine-learning preparation, and administrative jobs. Without management, a large scan can consume resources needed by time-sensitive reports.

Workload management mechanisms include:

• queues and workload classes;
• priorities and admission control;
• concurrency limits;
• memory grants and query caps;
• resource pools or virtual warehouses;
• isolation of loading and reporting workloads;
• timeout and cancellation policies;
• workload-aware autoscaling.

A sound policy begins with service objectives. For example, executive dashboards may receive high priority during business hours, while historical backfills may run in an off-peak class. Priority should not become an excuse to ignore inefficient SQL; resource governance and query tuning must operate together.

Cost-aware systems may scale compute elastically, but autoscaling can hide poor design and increase expenditure. Monitoring should distinguish genuine workload growth from regressions caused by changed plans, missing statistics, skew, or uncontrolled query patterns.

10. Query Optimization and Performance Diagnosis

A query optimizer estimates alternative execution plans using table statistics, metadata, cost models, and rewrite rules. It may choose an index scan, partition scan, full scan, join order, join algorithm, aggregation strategy, and degree of parallelism.

Important optimizer inputs include:

• row counts and column cardinalities;
• value distributions and histograms;
• null fractions;
• partition metadata;
• clustering or min–max statistics;
• estimated selectivity of predicates;
• available memory and current workload.

Stale statistics can cause incorrect cardinality estimates. An underestimated relation may receive too little memory and spill to disk; an overestimated small relation may prevent a beneficial broadcast join. Performance diagnosis should compare estimated and actual row counts at major plan operators.

A disciplined tuning cycle is:

1. Define the business service target and representative workload.

2. Measure baseline latency, throughput, resource use, and cost.

3. Inspect the actual execution plan, not only the SQL text.

4. Identify the dominant constraint: I/O, CPU, memory, network, skew, contention, or poor cardinality estimation.

5. Apply one controlled change.

6. Retest with representative data and concurrency.

7. Validate correctness, freshness, maintainability, and cost.

8. Record the result and monitor for regression.

Common SQL-level practices include selecting only required columns, filtering early, avoiding unnecessary functions on partition keys, using appropriate join predicates, preventing accidental many-to-many joins, and checking whether a correlated subquery can be expressed more efficiently. Hints should be used cautiously because they may become harmful as data distributions change.

11. Worked Application: Designing a Retail Sales Warehouse

Consider an illustrative case. A retailer stores 8 billion sales-line records over eight years. The fact table contains sale date, store, product, customer, quantity, net amount, promotion, and payment attributes. Analysts frequently ask for monthly sales by product category and region. Store managers query the latest 30 days by store. Nightly loading adds 20 million records, while corrections affect a small proportion of recent data.

Problem analysis

The main requirements are:

• interactive monthly summaries within 10 seconds for common dashboards;
• recent-store analysis within 5 seconds;
• nightly loads completed within four hours;
• retention of eight years;
• controlled cost and auditable refresh times.

A row-oriented design would read unnecessary attributes for broad analytical scans. A single unpartitioned table would force large scans and complicate retention. An index on every column would consume storage and slow loading.

Proposed design

The fact table is stored in a compressed columnar format and range-partitioned by sale month. Recent partitions are more frequently maintained, while older partitions are treated as stable. Data is clustered or sorted by store within each recent partition because store-level queries are common. A distribution strategy is selected using measured key frequency to avoid skew; customer is rejected if a small number of corporate accounts dominate the data.

A materialized aggregate stores monthly net sales and quantity by product category, region, and month. Since net amount is additive across these dimensions, it can be safely summed. If a report needs average transaction value, it stores or derives total amount and transaction count, then computes:

Average transaction value = total net amount / transaction count, provided transaction count is nonzero and both values use the same population and time period.

A separate recent-data structure or automatic clustering supports store managers. The aggregate is refreshed incrementally after successful load validation. Dashboard queries are routed to a high-priority interactive workload, while historical backfills use a lower-priority batch class.

Evaluation

Partition pruning reduces the months considered by time-bounded queries. Column pruning avoids reading payment and promotion columns for sales summaries. Compression reduces storage and scan traffic. The materialized aggregate avoids repeated joins and billions of detailed-row scans. However, the design adds refresh logic, aggregate correctness obligations, and monitoring requirements.

Testing uses production-shaped data and concurrency. Metrics include p50 and p95 latency, bytes scanned, CPU time, spill volume, refresh duration, failed loads, storage growth, and cost per workload class. If the aggregate becomes expensive to refresh relative to its use, it may be restricted to high-value dashboard queries or replaced by a different grain.

Learning points

The case illustrates that performance comes from coordinated decisions rather than one feature. Partitioning addresses temporal locality, clustering addresses local access, columnar storage reduces projection cost, aggregation reduces repeated computation, and workload management protects service objectives. Every benefit must be assessed against loading, storage, correctness, and maintenance costs.

12. Challenges, Governance and Contemporary Developments

Physical design has several limitations. Data distributions change, making previously useful indexes or distribution keys ineffective. Partitioning can create imbalance. Materialized views can become stale or semantically incorrect. Compression and clustering may require expensive reorganization. Cloud pricing can make a faster design more expensive if it increases compute consumption.

Governance is also important. Performance structures may contain replicated sensitive data. Access controls must apply to materialized views, aggregates, caches, temporary tables, and exported results—not only to the base table. Row-level security and masking policies must remain effective when data is copied or precomputed. Retention and deletion requirements must propagate to derived structures. Auditability requires documenting refresh times, source versions, transformation logic, and access decisions.

AI-assisted features are increasingly used for workload forecasting, index or clustering recommendations, anomaly detection, automatic statistics collection, and natural-language query generation. These tools can accelerate administration but do not remove the need for human validation. An automatically recommended index may improve a benchmark while harming ingestion or increasing exposure of sensitive data. AI-generated SQL must be checked for semantic correctness, authorization, join duplication, and cost.

Contemporary warehouse architectures increasingly separate storage and compute, use open table formats, support lakehouse patterns, and apply adaptive optimization. Serverless execution and elastic virtual warehouses simplify capacity planning but make workload governance and cost observability more important. The durable engineering principles remain: measure real workloads, preserve semantic correctness, minimize unnecessary data movement, and validate changes under representative conditions.

Best-Practice Checklist

1. Start with measured workload patterns and explicit SLAs.

2. Choose storage orientation according to access behavior.

3. Partition for pruning and lifecycle management, not merely because the table is large.

4. Avoid excessive partitions and small files.

5. Test distribution keys for skew before production deployment.

6. Use indexes selectively and account for load maintenance.

7. Exploit compression, sorting, and column pruning together.

8. Materialize aggregates only when reuse justifies refresh and storage cost.

9. Maintain accurate statistics and inspect actual execution plans.

10. Separate workload classes and monitor cost as well as latency.

11. Test with realistic scale, data distribution, and concurrency.

12. Apply security, retention, lineage, and audit controls to derived structures.

Chapter Summary

Physical design determines how a logical warehouse is stored and executed. Effective design aligns storage structures with workload patterns and balances latency, throughput, freshness, scalability, maintainability, and cost. Indexes support selective access but impose storage and loading overhead. Partitioning enables pruning and lifecycle operations, while clustering and sorting improve locality and compression. Compression reduces storage and data transfer, and columnar storage is particularly effective for wide analytical scans through column pruning, vectorized execution, and data skipping.

Materialized views and aggregate tables reduce repeated computation but require careful refresh, correctness, and additive-measure decisions. Parallel processing improves capacity only when work is balanced and data movement is controlled; Amdahl’s law explains why serial work limits ideal speedup. Workload management protects important services from competing queries. Query optimization depends on accurate statistics, appropriate plans, and systematic measurement.

Professional physical design is iterative. It uses representative workloads, actual execution evidence, controlled experiments, governance controls, and continuous monitoring. The objective is not maximum speed in isolation, but reliable analytical service at an acceptable and defensible cost.

Key Terms

Physical design: Representation of logical warehouse structures in storage and execution mechanisms.

Selectivity: Fraction of rows satisfying a predicate; lower fractions generally indicate more selective predicates.

Index: Auxiliary structure that accelerates access to rows or values.

B-tree index: Balanced tree structure supporting equality and range access.

Bitmap index: Bit-based structure often useful for low-cardinality analytical attributes.

Partitioning: Division of a table into independently managed physical units.

Partition pruning: Elimination of partitions that cannot satisfy a query predicate.

Clustering: Physically locating similar values near one another.

Data skew: Uneven distribution of rows or values across storage or processing units.

Compression ratio: Uncompressed size divided by compressed size.

Columnar storage: Organization in which values of the same column are stored together.

Column pruning: Reading only columns referenced by a query.

Predicate pushdown: Applying filters close to the storage layer.

Materialized view: Persisted result of a query maintained for faster access.

Aggregate table: Stored summary at a selected dimensional grain.

Data movement: Transfer of data between processing nodes during execution.

Workload management: Policies controlling resource allocation among competing workloads.

Cardinality estimation: Prediction of row counts produced by query operators.

Amdahl’s law: Model showing the speedup limit imposed by a serial fraction of work.

Review Questions

Short Answer Questions

1. What is the difference between logical design and physical design in a data warehouse?

2. Why can excessive partitioning harm performance?

3. Define column pruning and predicate pushdown.

4. What is data skew, and why is it problematic in parallel execution?

5. Distinguish an additive measure from a semi-additive measure.

Long Answer Questions

1. Compare row-oriented and columnar storage for transactional and analytical workloads.

2. Explain how partitioning, clustering, and compression can work together in a fact table.

3. Discuss the benefits and maintenance costs of indexes in a modern columnar warehouse.

4. Explain materialized views, refresh strategies, and the conditions under which an aggregate is semantically valid.

5. Describe a systematic methodology for diagnosing and improving a slow warehouse query.

Analytical / Application Questions

1. A warehouse query reads eight columns from a table containing 80 columns. Explain why columnar storage may help, and identify factors that prevent the runtime improvement from being exactly tenfold.

2. A distributed query has a serial fraction of 0.20 and uses 5 processors. Using Amdahl’s law, calculate its ideal speedup and explain why actual speedup may be lower.

3. Design a physical strategy for a healthcare claims warehouse requiring monthly regulatory reports, recent-claim investigation, daily ingestion, strict access control, and seven-year retention. Justify partitioning, storage format, aggregation, workload management, and governance choices.

Multiple Choice Questions with Answers

Question 1

Which feature most directly enables a query to avoid reading irrelevant time partitions?

A. Partition pruning

B. Data masking

C. Broadcast joining

D. Row-level security

Correct Answer: A. Partition pruning

Question 2

Which workload is generally best suited to columnar storage?

A. Frequent single-row updates

B. Analytical scans selecting a few columns from many rows

C. Complete-record point lookups only

D. High-frequency transaction locking

Correct Answer: B. Analytical scans selecting a few columns from many rows

Question 3

What is a major risk of using a distribution key with highly uneven frequencies?

A. Improved compression in every case

B. Data skew and overloaded processing workers

C. Automatic elimination of all joins

D. Guaranteed partition pruning

Correct Answer: B. Data skew and overloaded processing workers

Question 4

A table of 1,000 GB is compressed to 250 GB. What is the compression ratio?

A. 0.25:1

B. 2:1

C. 4:1

D. 250:1

Correct Answer: C. 4:1

Question 5

Which measure is typically semi-additive?

A. Sales revenue

B. Units sold

C. Account balance

D. Number of orders

Correct Answer: C. Account balance

Question 6

If the serial fraction is 0.10 and a workload uses 8 processors, what is its ideal Amdahl speedup?

A. 2.80

B. 4.71

C. 7.20

D. 8.00

Correct Answer: B. 4.71

Question 7

Which join strategy copies a small relation to multiple workers?

A. Broadcast join

B. Range partitioning

C. Bitmap join

D. Serial nested scan

Correct Answer: A. Broadcast join

Question 8

Why are stale statistics harmful to query optimization?

A. They prevent all compression

B. They can cause inaccurate cardinality estimates and poor plans

C. They automatically delete old partitions

D. They enforce excessive security policies

Correct Answer: B. They can cause inaccurate cardinality estimates and poor plans

Question 9

Which refresh approach updates a materialized view using only changed source data when supported?

A. Full archival refresh

B. Incremental refresh

C. Static partitioning

D. Bitmap refresh

Correct Answer: B. Incremental refresh

Question 10

Which practice is most appropriate when evaluating a physical-design change?

A. Measure only one small query

B. Apply many changes simultaneously without a baseline

C. Test representative scale, distribution, and concurrency

D. Ignore storage and loading costs

Correct Answer: C. Test representative scale, distribution, and concurrency