Storage Structures & Tablespaces: Files, Segments, Extents, Blocks

TAKEAWAYS:

• Storage hierarchy: Databases organize data in files → tablespaces → segments → extents → blocks, each serving a specific role in performance and management.
• Tablespaces: Logical containers for database objects (tables, indexes) that control storage location, size, and access methods (e.g., SYSTEM, UNDO, TEMPORARY).
• Segments: Physical structures (tables, indexes, partitions) that store data and metadata, growing dynamically via extents.
• Extents and blocks: Contiguous memory allocations (extents) divided into fixed-size chunks (blocks, typically 2–32 KB) for efficient I/O.
• Optimization trade-offs: Smaller blocks reduce overhead but increase I/O; larger blocks speed up reads but waste space.
• Real-world impact: Poor storage design causes fragmentation, slow queries, and failed backups—critical for banks (e.g., Nabil Bank’s loan data) and e-commerce (e.g., Daraz’s order history).

1. Database Storage Hierarchy: Files to Blocks

Databases don’t store data in raw memory like programs—they use a layered storage model to manage persistence, concurrency, and performance. Here’s how data flows from physical disks to logical queries:

graph TD
    A["Physical Disk\n(HDD/SSD)"] --> B["Database Files\n(.dbf, .log, .ctl)"]
    B --> C["Tablespaces\n(Logical containers)"]
    C --> D["Segments\n(Tables, Indexes, Partitions)"]
    D --> E["Extents\n(Contiguous blocks)"]
    E --> F["Blocks\n(Fixed-size chunks, e.g., 8KB)"]
    F --> G["Rows/Records\n(Actual data + metadata)"]

Key terms:

• Database files: Physical files on disk (e.g., SYSTEM01.DBF, UNDOTBS01.DBF in Oracle).
• Tablespaces: Logical groupings of files (e.g., USERS, TEMP).
• Segments: Storage for a single object (e.g., EMPLOYEES table, EMP_IDX index).
• Extents: Contiguous blocks allocated to a segment (grow when full).
• Blocks: Fixed-size units (e.g., 8KB in Oracle) holding rows + metadata.

2. Tablespaces: Logical Storage Containers

Tablespaces are the first layer of abstraction—they let DBAs:

• Isolate storage: Place frequently accessed tables (e.g., CUSTOMER in USERS tablespace) on fast SSDs.
• Manage growth: Auto-extend files or set manual limits.
• Enforce security: Restrict access to sensitive tablespaces (e.g., PAYROLL).

Types of Tablespaces

Worked Example: Nabil Bank’s Loan Data Nabil Bank uses separate tablespaces for:

• LOAN_APPLICATIONS (Permanent, SSD-backed for fast approvals).
• LOAN_TRANSACTIONS (Temporary, for nightly batch processing).
• UNDO_LOANS (Undo tablespace to roll back failed transactions).

Why?

• Performance: Loan applications (high I/O) live on SSDs.
• Recovery: Undo tablespace ensures no lost transactions if a query fails.

3. Segments: Physical Structures for Objects

Segments are the storage units for database objects. Each segment has:

• A segment header (metadata like size, owner).
• Extents (contiguous blocks).
• Free space (for new rows).

Segment Types:

1. Table segments: Store rows + row headers.
2. Index segments: Store B-tree structures for fast lookups.
3. LOB segments: Store large objects (e.g., PDFs, videos) in out-of-line storage.
4. Partitioned segments: Split tables/indexes by range/hash (e.g., SALES_BY_MONTH).

4. Extents and Blocks: The Building Blocks

Extents: Contiguous Allocations

• Purpose: Reduce fragmentation by allocating blocks in chunks.
• Growth: Segments start with 1 extent, then add more as data grows.
• Preallocation: DBAs can preallocate extents to avoid dynamic growth delays.

Example: Daraz Order Queue Daraz’s ORDERS table uses preallocated extents to handle Black Friday traffic:

• Initial extent: 100MB (for 100K orders).
• Next extents: Auto-added in 50MB chunks when full.

Blocks: Fixed-Size Data Units

• Size: Typically 2–32KB (configurable; Oracle default: 8KB).
• Contents:
  • Row data (e.g., CUSTOMER_ID, NAME).
  • Row header (row ID, transaction status).
  • Block header (block address, free space).
  • Row directory (offsets to rows).

Block Size Trade-offs:

Worked Example: Kathmandu Traffic Routes Imagine a navigation app (like Pathao Maps) storing road data:

• Small blocks (4KB): Store individual road segments (efficient for updates).
• Large blocks (16KB): Store city-wide route clusters (faster queries for "KTM to Pokhara").

5. Storage Optimization Techniques

A. Tablespace Management

1. Local vs. Dictionary-Managed:
   • Local: Extents managed by tablespace (faster, but less flexible).
   • Dictionary: Extents managed by data dictionary (slower, but supports features like AUTOEXTEND).
2. Compression:
   • OLTP: Use row-level compression (saves 40–60% space).
   • Data Warehouse: Use hybrid columnar compression (90%+ savings).

B. Segment Optimization

• PCTFREE: % of block left free for updates (default: 10%).

  CREATE TABLE EMPLOYEES (ID NUMBER, NAME VARCHAR2(100))
  PCTFREE 20 STORAGE (INITIAL 1M NEXT 1M);
  

• PCTUSED: % of block used before reuse (default: 40%).

C. Partitioning

Split large tables by:

• Range: SALES_BY_MONTH (2020, 2021, 2022 partitions).
• Hash: CUSTOMERS (distributed by CUST_ID % 10).
• List: PRODUCTS (categories: Electronics, Grocery).

Example: NEPSE Stock Data NEPSE’s DAILY_PRICES table is partitioned by date:

CREATE TABLE DAILY_PRICES (
    SYMBOL VARCHAR2(10),
    DATE DATE,
    PRICE NUMBER
) PARTITION BY RANGE (DATE) (
    PARTITION Q1_2023 VALUES LESS THAN (TO_DATE('01-APR-2023', 'DD-MON-YYYY')),
    PARTITION Q2_2023 VALUES LESS THAN (TO_DATE('01-JUL-2023', 'DD-MON-YYYY'))
);

Benefits:

• Faster queries (scans only relevant partitions).
• Easier backups (drop old partitions).

6. Real-World Applications

A. E-Commerce: Daraz Order Processing

• Tablespaces:
  • ORDERS (Permanent, SSD-backed for high throughput).
  • TEMP_ORDERS (Temporary, for sorting during checkout).
• Blocks: 16KB to minimize I/O for order lookups.
• Partitioning: Orders split by ORDER_DATE (monthly partitions).

B. Banking: Nabil Bank Loan System

• Segments:
  • LOAN_APPLICATIONS (table segment).
  • LOAN_INDEX (B-tree index segment for fast searches).
• Undo Tablespace: Critical for rolling back failed transactions.
• LOB Storage: Loan documents (PDFs) stored in SECURE_LOBS tablespace.

C. Government: eSewa Transaction Logs

• Tablespaces:
  • TRANSACTIONS (Permanent, replicated for high availability).
  • AUDIT_LOG (Read-only, archived monthly).
• Compression: Row-level compression reduces storage costs by 50%.

7. Common Pitfalls and Best Practices

Best Practices:

1. Monitor free space: Use DBA_FREE_SPACE (Oracle) or SHOW TABLE STATUS (MySQL).
2. Preallocate extents: Avoid dynamic growth during peak hours.
3. Use appropriate block sizes: 8KB for OLTP, 32KB for data warehouses.
4. Partition large tables: Improves query performance and backup speed.

Exam Tip

How This Unit is Tested:

1. Definitions: Expect 2–3 marks on terms like tablespace, extent, PCTFREE.
   • Example: "Define ‘segment’ and explain its role in storage allocation."
2. Diagrams: Draw the storage hierarchy (files → tablespaces → segments → extents → blocks) for 5 marks.
3. SQL Commands: Write commands to:
   • Create a tablespace: CREATE TABLESPACE USERS DATAFILE 'users01.dbf' SIZE 100M.
   • Modify storage parameters: ALTER TABLE EMPLOYEES STORAGE (INITIAL 5M PCTINCREASE 0).
4. Scenario-Based: Given a real-world case (e.g., "Ncell’s call logs"), design tablespaces/segments.
   • Example: "Ncell stores 10M call records daily. Design storage structures for fast queries and backups."
5. Optimization: Compare block sizes or partitioning strategies (e.g., "Why use hash partitioning for a 100GB table?").

Key Formulas to Remember:

• Block efficiency: $\text{Space wasted}=\left(1-\frac{\text{Avg row size}}{\text{Block size}}\right)\times 100\%$
• Extent growth: $\text{Next extent size}=\text{Initial size}\times (1+\text{PCTINCREASE}/100)$

Common Exam Mistakes:

• Confusing tablespaces (logical) with files (physical).
• Forgetting undo tablespaces are critical for rollback.
• Ignoring block size impact on I/O (e.g., small blocks = more I/O).

Visual Summary:

mindmap
  root((Storage Structures))
    Files
      Physical storage (HDD/SSD)
      Types: .dbf, .log, .ctl
    Tablespaces
      Logical containers
      Types: Permanent, Temporary, Undo
    Segments
      Objects: Tables, Indexes, LOBs
      Growth: Extents
    Extents
      Contiguous blocks
      Preallocation vs. dynamic
    Blocks
      Fixed size (2–32KB)
      Contents: Header, Row Directory, Data
    Optimization
      Compression
      Partitioning
      PCTFREE/PCTUSED