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

TAKEAWAYS:

• Storage hierarchy: Data is stored in files → blocks → rows, with each block holding a fixed number of rows (e.g., 8KB in Oracle).
• Tablespaces: Logical containers for database files, allowing segmentation by function (e.g., SYSTEM, UNDO, USERS).
• Extents vs. blocks: An extent is a contiguous set of blocks (e.g., 64KB), while a block is the smallest unit of I/O (e.g., 8KB).
• Free space management: PCTFREE (reserved for updates) and PCTUSED (threshold for reuse) control block efficiency.
• Storage parameters: INITIAL, NEXT, MINEXTENTS, and MAXEXTENTS define how extents are allocated.
• Real-world impact: Poor tablespace design causes fragmentation, slowing queries (e.g., Daraz’s order-processing delays).

1. Database Storage Hierarchy: Files → Blocks → Rows

Databases store data in a three-layer hierarchy:

1. Physical files (e.g., datafile01.dbf in Oracle) on disk.
2. Blocks (fixed-size chunks, e.g., 8KB in Oracle, 16KB in PostgreSQL).
3. Rows (stored within blocks, with pointers to overflow data if too large).

graph LR
    A["Database"] --> B["Tablespaces"]
    B --> C["Datafiles"]
    C --> D["Blocks (8KB-64KB)"]
    D --> E["Rows"]
    D --> F["Free Space"]
    D --> G["Row Directory"]

Why this matters:

• A block is the smallest unit read/written to disk (I/O operation).
• If a row exceeds block size, it overflows into additional blocks (e.g., LOB columns in Oracle).
• Example: In eSewa’s transaction database, a block might store 50–100 payment records. If a block fills up, new records wait for the next I/O, causing latency.

A labelled breakdown of an Oracle block showing header, row directory, and free space. (Image: Scifipete, CC BY-SA 3.0, via Wikimedia Commons)

2. Tablespaces: Logical Containers for Storage

A tablespace is a logical storage unit that groups one or more datafiles (physical files on disk). It organizes data by:

• Function (e.g., SYSTEM for metadata, UNDO for rollback segments).
• Performance (e.g., TEMP for temporary tables).
• Security (e.g., HR_DATA for HR-only tables).

Key Tablespaces in Oracle

Example: Ncell’s Billing System

• Tablespace CUSTOMER_DATA: Stores subscriber records (tables: CUSTOMERS, SUBSCRIPTIONS).
• Tablespace TRANSACTIONS: High-frequency inserts (tables: CALL_LOG, DATA_USAGE).
• Why? Separating high-write tables (TRANSACTIONS) from read-heavy tables (CUSTOMER_DATA) reduces contention.

3. Storage Structures: Blocks, Extents, and Segments

A. Data Blocks

• Fixed size (e.g., 8KB in Oracle, configurable in PostgreSQL/MySQL).
• Contains:
  • Block header (address, transaction info).
  • Row directory (pointers to rows).
  • Actual rows (with row IDs).
  • Free space (for future inserts/updates).

Worked Example: Daraz Order Processing

• Block size: 8KB (Oracle default).
• Rows per block: If an ORDERS table row is ~200 bytes, a block holds ~40 rows.
• Problem: During Black Friday, 10,000 orders/sec arrive. If PCTFREE is too low, the database must allocate new blocks, slowing inserts.
• Solution: Set PCTFREE = 20 to reserve space for updates without frequent block splits.

B. Extents: Contiguous Blocks

• An extent is a contiguous set of blocks (e.g., 64KB = 8 blocks of 8KB).
• Allocated in one operation (no fragmentation).
• Types:
  • Uniform extents: Fixed size (e.g., 64KB for all extents).
  • Non-uniform extents: Grow dynamically (e.g., start at 64KB, then 1MB, 16MB).

C. Segments: Logical Structures

A segment is a set of extents for a specific object (e.g., a table or index).

• Table segment: Stores table data.
• Index segment: Stores index entries.
• Temporary segment: For SORT operations.

Example: NEPSE Stock Database

• Table segment STOCK_PRICES: Stores daily prices (extents allocated as the table grows).
• Index segment PRICE_IDX: Speeds up WHERE date = '2023-10-01' queries.
• Problem: If MINEXTENTS = 1 and MAXEXTENTS = 121, the table can auto-extend up to 121 extents. If not set, Oracle throws ORA-01653: unable to extend table.

4. Storage Parameters: Controlling Allocation

These parameters define how extents and blocks are managed:

Worked Example: Bank Loan Interest Calculation

• Scenario: A bank’s LOANS table has rows of ~1KB. Block size = 8KB → 8 rows/block.
• Problem: If PCTFREE = 5 (5% free space), updates may require new blocks, slowing high-frequency interest recalculations.
• Solution:

  CREATE TABLE LOANS (
      loan_id NUMBER,
      interest_rate NUMBER
  ) TABLESPACE LOAN_DATA
  STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 20
           PCTINCREASE 0 PCTFREE 20 PCTUSED 70);
  

  • Why PCTFREE 20? Allows updates without frequent block splits.
  • Why PCTUSED 70? Reuses blocks only when 70% full, reducing fragmentation.

5. Storage Types: Permanent vs. Temporary

Example: Pathao Driver Earnings Calculation

• Temporary tablespace: Used for GROUP BY driver_id queries to calculate daily earnings.
• Permanent tablespace: Stores DRIVERS and TRIPS tables.
• Why? Temporary tablespaces avoid locking permanent data during heavy analytics.

6. Free Space Management: PCTFREE and PCTUSED

• PCTFREE: % of block left empty for updates.
  • Default: 10% (Oracle), 0% (MySQL/PostgreSQL).
  • High PCTFREE: Fewer block splits (better for OLTP).
  • Low PCTFREE: More rows per block (better for read-heavy systems).
• PCTUSED: % of block that must be used before space is reused.
  • Default: 40% (Oracle).
  • High PCTUSED: Reduces fragmentation (e.g., 70% means blocks are reused only when 70% full).

Comparison Table

7. Local vs. Dictionary-Managed Tablespaces

Example: Khalti Payment Gateway

• Why LMT? Khalti’s TRANSACTIONS table has millions of rows. Dictionary-managed tablespaces would slow down metadata queries (e.g., SELECT * FROM DBA_EXTENTS).
• Solution: Use locally-managed tablespaces with uniform extents:

  CREATE TABLESPACE TRANSACTIONS
  DATAFILE 'khalti_trans.dbf' SIZE 10G
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
  

8. Storage Optimization Techniques

A. Segment Space Management

• ALTER TABLE ... SHRINK SPACE: Reclaims unused space (Oracle 11g+).
• MOVE vs. ALTER:
  • ALTER TABLE ... MOVE: Rebuilds the table in a new location (no downtime in some DBs).
  • ALTER TABLE ... ALTER TABLESPACE: Changes storage parameters.

Example: NTC Traffic Route Optimization

• Problem: The ROUTES table in NTC’s traffic management system has 50% unused space due to deleted records.
• Solution:

  ALTER TABLE ROUTES MOVE TABLESPACE ROUTES_OPTIMIZED;
  

  • Moves the table to a new tablespace with better PCTFREE settings.

B. Compression

• Basic compression: Reduces block size (e.g., COMPRESS in Oracle).
• Advanced compression: Columnar or hybrid (e.g., PostgreSQL’s TOAST).

Example: Daraz Inventory Database

• Before: Product descriptions (text) stored as-is → large blocks.
• After: Enable basic compression:

  ALTER TABLE PRODUCTS MOVE COMPRESS;
  

  • Reduces storage by 30% and speeds up scans.

In the Real World

1. eSewa Transaction Processing

   • Idea Used: Tablespaces and extents
   • How? eSewa separates:
     • TRANSACTIONS tablespace (high PCTFREE for frequent updates).
     • CUSTOMER_DATA tablespace (lower PCTFREE for read-heavy queries).
   • Impact: During Diwali, when 100,000 transactions/sec occur, proper tablespace design prevents block splits and I/O bottlenecks.

2. Ncell Billing System

   • Idea Used: Locally-managed tablespaces (LMT)
   • How? Ncell uses LMT for CALL_LOG and DATA_USAGE tables to avoid metadata overhead. Uniform extents of 1MB ensure predictable performance.
   • Impact: Reduces query time for SELECT * FROM CALL_LOG WHERE date = '2023-10-01' by 40%.

3. NEPSE Stock Data Archive

   • Idea Used: Temporary tablespaces and PCT settings
   • How? NEPSE’s daily STOCK_PRICE reports use temporary tablespaces for intermediate GROUP BY calculations. Permanent tables have PCTFREE = 5 (read-heavy).
   • Impact: Speeds up end-of-day reports by avoiding locks on permanent data.

Exam Tip

What Examiners Look For

1. Definitions:

   • Clearly distinguish block, extent, segment, and tablespace.
   • Example: "An extent is a contiguous set of blocks allocated for a segment, while a block is the smallest unit of I/O."

2. Parameters:

   • Know the default values of PCTFREE, PCTUSED, and their impact.
   • Example: "In Oracle, PCTFREE = 10 means 10% of each block is reserved for updates, reducing block splits."

3. Scenarios:

   • OLTP vs. DSS: OLTP (eSewa) needs high PCTFREE; DSS (NEPSE) needs low PCTFREE.
   • Fragmentation: Explain how high PCTUSED reduces fragmentation but may waste space.

4. SQL Commands:

   • Be able to write:
     • CREATE TABLESPACE with storage parameters.
     • ALTER TABLE ... MOVE for optimization.
     • SHRINK SPACE for reclaiming unused space.

5. Diagrams:

   • Draw the storage hierarchy (files → tablespaces → blocks → rows).
   • Sketch a block layout showing header, row directory, and free space.

Common Pitfalls

• Mixing up PCTFREE and PCTUSED: Remember PCTFREE is for updates, PCTUSED is for reuse.
• Ignoring MAXEXTENTS: Always check if a table can auto-extend or will fail with ORA-01653.
• Assuming default values: Oracle’s defaults may not suit all workloads (e.g., PCTFREE = 10 is too low for high-update systems).

Sample Exam Questions

1. Short Answer: "Explain the difference between a data block and an extent in Oracle. Provide an example of when you would use a larger extent size." Answer:

   A block is the smallest unit of I/O (e.g., 8KB), while an extent is a contiguous set of blocks (e.g., 64KB). For a large table like Daraz’s ORDERS (millions of rows), larger extents (e.g., 1MB) reduce overhead from frequent extent allocations.

2. Scenario-Based: "A bank’s LOANS table has PCTFREE = 5 and experiences slow updates during interest recalculations. Suggest two changes to improve performance." Answer:

   1. Increase PCTFREE to 20 to reduce block splits.
   2. Set PCTUSED = 70 to reuse blocks only when 70% full, minimizing fragmentation.

3. SQL Command: "Write the SQL to create a tablespace AUDIT_LOG with locally-managed extents of 512KB, initial size 1GB, and no auto-extend." Answer:

   CREATE TABLESPACE AUDIT_LOG
   DATAFILE '/u01/app/oracle/oradata/AUDIT_LOG.dbf' SIZE 1G
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
   

Final Note: Master the storage hierarchy, tablespace types, and parameter tuning. Always relate answers to real-world systems like eSewa, Ncell, or Daraz to score full marks.