Explain HANA Table Partitioning & Data Aging in SAP

Two techniques for very large tables. They are often mentioned together and solve different problems. Partitioning splits one table into physical pieces. Range

All topics

48 / 65

HANA DBadvanced

HANA Table Partitioning & Data Aging

Splitting very large HANA tables across partitions for parallelism and manageability, and moving cold data out of hot memory via data aging.

Two techniques for very large tables. They are often mentioned together and solve different problems.

Table partitioning is like splitting one massive filing cabinet into several smaller cabinets organized by year, so a clerk searching for '2026 records' only has to open the one relevant cabinet instead of searching the whole room; data aging is like moving several years of old, rarely-requested files to a basement archive room that's still searchable on request, just a bit slower to retrieve, freeing up prime office real estate (RAM) for this year's actively-used files.

Key Concepts

Partitioning splits one table into physical pieces.

    range        by a column's value -- one partition per year
    hash         by a hash of one or more columns -- even distribution
    round-robin  even distribution ignoring values

Range partitioning pays off through pruning. If queries filter on the same column you partitioned by, whole partitions are skipped:

    partitioned by year, query WHERE gjahr = '2026'
    -> partitions for 2019-2025 are not read at all

Hash partitioning does not do that — a hash scatters values deliberately, so a filter cannot eliminate partitions. What it gives you is even distribution and parallel processing.

So the choice follows the query pattern. Filtered constantly on a date: range. Needs spreading evenly for parallelism: hash. Round-robin is rarely the best answer for anything else.

The other reason to partition: a scale-out landscape. A table larger than one host's memory must be distributed across hosts, and partitioning is how. There is also a hard limit of about two billion rows per partition in a column-store table, which very large tables reach on their own.

Data aging solves a different problem: cost. Even with the budget, keeping seven years of history in RAM alongside this month's data is paying memory prices for records nobody reads.

Aging moves records past a configured threshold to a cold partition that stays on disk and is loaded only when a query actually asks for it.

    hot   current data, in memory, fast
    cold  historical, on disk, loaded on demand

It is not archiving, and the difference matters.

    archiving   data is removed from the database into archive files.
                Reading it needs a separate retrieval process.
    data aging  data stays in the table and is still queryable with
                ordinary SQL. It is just not resident in memory.

A report over aged data works unchanged and is slower for that partition. Nobody has to know it was aged.

Aging is application-aware, not a switch on any table. It is built into specific SAP applications — Finance, Sales and Distribution — because the application must know which records are safely cold. It is not a generic HANA feature you enable on an arbitrary table.

Deciding. Partition when a table is large enough that parallelism or distribution is the constraint, and partition by what queries filter on. Age when memory cost is the constraint and the old data is genuinely rarely read. They compose: a table can be range-partitioned by year with the older partitions aged to disk.

What the interviewer is probing.1. "Why partition a table?" Probing: the reasons. Stalls: "Performance." Moves up: the two-billion-row limit per partition, pruning for query performance, and enabling parallel delta merges and loads.

2. "What partitioning types exist?" Probing: breadth. Stalls: "Range." Moves up: hash for even distribution, range for pruning on a value such as date, round-robin for load spreading, and multi-level combinations.

3. "What does data aging do?" Probing: the distinct feature. Stalls: "Deletes data." *Moves up:* moves cold partitions out of memory onto disk while keeping them queryable, which reduces the memory licence pressure.

4. "What is the catch with data aging?" Probing: the honest limit. Stalls: "None." *Moves up:* applications must be aging-aware or they read cold data and get slow, and not all modules support it.