SQL Lab — Table Partitioning & Archiving (Hard)

🖥️

SQL Terminal

📚

Reference

📌 OBJECTIVES — NO HINTS

○Analyse partition candidates (EXPLAIN)

○Create RANGE partition by year/month

○Verify partition pruning in EXPLAIN

○Add MAXVALUE catch-all partition

○REORGANIZE PARTITION to split a range

○Archive + drop oldest 3 partitions

○Validate row counts across partitions

0 / 7 complete

SQL TERMINAL — TABLE PARTITIONING & ARCHIVING (HARD)

MySQL 8.0 — Lab environment loaded Table Partitioning & Archiving (Hard) — Hard tier · No syntax hints provided ⚠ This lab tests independent problem-solving. Reference window available. ──────────────────────────────

mysql>

COMMAND REFERENCE

AVAILABLE COMMANDS

EXPLAIN SELECT * FROM sensor_events WHERE recorded_at > '2024-11-01';
CREATE TABLE sensor_events_p (...) PARTITION BY RANGE (YEAR(recorded_at)*100+MONTH(recorded_at)) (PARTITION p202201 VALUES LESS THAN (202202), ...);
ALTER TABLE sensor_events PARTITION BY RANGE (...)
SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME='sensor_events';
ALTER TABLE sensor_events REORGANIZE PARTITION p_future INTO (...)
ALTER TABLE sensor_events TRUNCATE PARTITION p202201;
ALTER TABLE sensor_events DROP PARTITION p202201;

Type keywords from the command list. The terminal will recognise your intent even if the exact syntax differs.

MISSION BRIEFING

PARTITIONING — TELEMETRIX IOTCLOUD · HARD ⭐⭐⭐

SCENARIO

TelemetriX IoT Cloud stores 3 years of sensor readings in a single sensor_events table — now 900M rows. Queries for the current month scan the whole table. Range partitioning by month will limit scans to the relevant partition. You must also archive partitions older than 18 months to a cold storage table.

OBJECTIVES

Analyse partition candidates (EXPLAIN)
Create RANGE partition by year/month
Verify partition pruning in EXPLAIN
Add MAXVALUE catch-all partition
REORGANIZE PARTITION to split a range
Archive + drop oldest 3 partitions
Validate row counts across partitions

HARD MODE RULES

No step-by-step guidance. Use the Reference window for command syntax only. You must determine the approach, the correct columns and order, and verify your own results.

Display Mode