Table Internals: Stores, Compression, Partitioning & MVCC

A row-store table has a single-column integer sequence key; another has a composite character key. Which gets plain BTREE and which gets CPBTREE, and why does that make CPBTREE the de-facto default for SAP?

A developer assumes the delta store is a throwaway write cache that's cleared on merge. Why is that wrong, and what actually degrades if merges are skipped?

A column table is merging too often during a bulk load. A junior DBA proposes editing auto_merge_decision_func in indexserver.ini. What's the correct response, and what should be done instead?

A delta is dangerously large and a hard merge is stuck waiting. You consider a forced merge. What does FORCED_MERGE do that a hard merge does not, and why is that exactly the risk?

A DBA reads 'merge waiting for token' and concludes the merge is hung. What do merge tokens actually limit, and why is the waiting normal?

Memory merge cuts I/O during chunked loads. What asymmetric cost do you accept, and what has to recover the data if that cost is realised?

A hard merge and a forced merge both override automerge — but only one can still get stuck. Which, and what does each actually bypass?

A developer refuses to run delta merges in business hours because 'the table locks until the merge finishes.' Why is that fear mostly wrong, and what's the real cost?

In M_SAVEPOINTS the critical phase lasts several seconds. Why is that worse than a slow savepoint overall, and what threshold turns it into an action item?

You defined table placement rules but nothing moved. What step is missing, and what's the easily-skipped gotcha when you run it?

ADD PARTITION fails on one partitioned table but works on another. Which partition types refuse new partitions, and what property explains it?

Under memory pressure HANA unloads a table you wanted to keep — it has UNLOAD PRIORITY 9. Did you set the wrong number, and what does the scale really mean?

Post-restart, the first queries on key column tables are unexpectedly slow. Which startup parameter likely changed, and what trade-off does it control?

At HANA startup, row-store and column-store tables load very differently. What's the key difference, and why must row-store indexes be rebuilt while column-store main-store ones survive?

You want to clear just one of a table's three classification attributes. Why will that fail, and what's the only removal HANA allows?

A colleague says heterogeneous partitioning is 'just letting partitions be different sizes.' Why is that wrong, and when does the real feature earn its complexity?

Round-robin forbids a primary key, but hash partitioning — also a 'spread it out' scheme — permits one. What property of round-robin makes the difference?

Adding an OTHERS partition guarantees no insert ever fails with error 544. So why does SAP recommend against it for a range-partitioned table?

A range query on the hash-partitioned key scans every partition instead of pruning. Is that a bug, and when does hash partitioning actually prune?

You try to define a range partition as VALUES > 2020 and it's rejected. Which condition forms are actually allowed, and what extra rule applies if you partition on a function?

With dynamic range partitioning, you see the OTHERS partition briefly larger than its threshold. Is the mechanism broken, and what governs the check?

You want dynamic range partitioning on a status-code column. Why won't that work, and which columns can it use?

A system-versioned table looks like one table, but physically it isn't. How is it really structured, and what is the operational consequence for the history?

A colleague uses 'temporal' and 'temporary' table interchangeably. Why is that a meaningful error, not just pedantry?

You want no-logging retention tables to never be auto-dropped under memory pressure. Which parameter governs it, and what value guarantees survival?

M_CS_TABLES shows a large table as PARTIALLY loaded even after queries hit it. Is something wrong, or is lazy loading working as designed?

Both no-logging and no-logging-retention tables lose data at restart. So what does the RETENTION option actually buy, and what extra risk does the retention variant carry?

On a row-store table you create an index without naming a type. Which type do you get, and why is naming CPBTREE explicitly usually redundant?

You want to cut an index's memory footprint with an inverted hash index. On which indexes is this valid, and where must you NOT use it?

Before sending a CSV export to a vendor you apply WITH SCRAMBLE. What does it obfuscate, what does it leave exposed, and why is reversibility a one-way decision?

Row-store MVCC version memory keeps climbing. Before reaching for RECLAIM VERSION SPACE, what's the more likely root cause?

Many databases modify a row in place and keep a before-image aside. Why is it wrong to assume HANA's row-store MVCC works the same way, and how does it change where memory grows?

Column-store and row-store MVCC differ in WHERE versions live. For a column table, where does the first change since the last merge get its before-image?

A flexible-schema table auto-added a column for values that all look numeric. What data type did it get, and what's the non-obvious upside (and trap)?

A teammate warns you to check endianness before a binary export/import between two HANA 2.0 systems. Is that warning still relevant, and why might it have been once?

You need a SELECT guaranteed to return exactly one row to test a function or use SQL as a calculator. Which built-in fits, and what's its equivalent in other DBs?

When do you actually need a bitemporal table rather than a plain system-versioned table?

Two sessions both create a local temporary table named the same thing, with different columns. Does this conflict, and what naming/lifetime rule makes it safe?

You're considering an inverted individual index instead of a standard composite (concat-attribute) index. What do you gain, and what specific query pattern do you pay for it on?

In a flexible schema table, how do new columns appear, and what operational risk does that implicit behaviour create?

You plan to repartition a multi-TB table during the day because 'reads stay available.' What two impacts make SAP advise against it?

You query M_DELTA_MERGE_STATISTICS to audit table merges but the results look noisy. What filter do you need, and what columns explain a given merge?

A script creates tables without specifying a store. On modern HANA, which store do they land in, and how do you override per-table or globally?

A manual consistency check ran and reported problems. Which two views do you read, and which one actually holds the error detail?

Why can the history table of a system-versioned table NOT have a primary key, even though the primary table does?

You suspect catalog corruption, not data corruption. Which consistency-check procedure do you call, and which would be the wrong tool?

Of all the reasons to partition a column table, which one makes partitioning the ONLY option rather than just an optimization?

Version memory is high and you need the offending table. Which view gives total version memory, and which actually names the worst table?

Why is hash-range the most popular multilevel scheme — what does each level contribute that the other can't? (Also covers the four multilevel combos.)

Asked to add time-travel history to a table on a current HANA system, which temporal mechanism should you choose and which is now discouraged?

In M_CS_TABLES a table shows LOADED = PARTIALLY. What does that value mean, and does it indicate a problem?

'It's all in memory, so why bother with indexes?' Give the two reasons HANA still needs them.

A report reads the same table twice in one transaction and gets different totals. Which isolation level is in effect, and which would have prevented it?

The index server trace logs MVCC version-threshold warnings. What are the defaults, and what should you investigate before touching them?

Beyond mandatory dictionary compression, what extra compression can the column store apply — and do you choose it?

Before scripting DROP PARTITION on a production table, what's the dangerous default behaviour and how do you protect yourself?

Rule-based repartitioning stopped adding partitions at 12. Is that a bug, and how would you raise it if needed?

How do you make EXPORT produce a single compressed archive instead of a directory tree, and how do you peek inside it?

You set a group type/subtype/name on a row-store table but placement rules never use them. Why, and what are the three attributes?

What problem does MVCC solve, and what does it pointedly never do to readers — even at the cost of keeping versions?

In multilevel partitioning, what is a partition group and why can't you place its members on different nodes?

During a large SAP BW load you want to avoid dozens of pointless delta merges. Which merge type fits, and how does it differ from leaving automerge on?

How does an application time-period table differ structurally from a system-versioned table, and which one has no separate history table?

DISABLE PERSISTENT MERGE makes a table's merges skip writing the new main store to disk permanently. What standing risk are you signing up for?

What restriction of single-level hash/range partitioning does multilevel partitioning let you escape?

How do you quickly check (a) whether a table is partitioned and (b) read its partition definition — and which view holds which?

You see hidden columns $rowid$, $trex_udiv$, and $trexexternalkey$ on a column table. Which is the MVCC/visibility column, and which signals a composite primary key?

Two HANA things are both called 'savepoint.' How does a transactional savepoint differ from the persistence savepoint, and why must you not confuse them?

An INSERT into a range-partitioned table fails with error 544 ('can't determine partition'). What's the root cause and the two fixes?

You see a merge with MOTIVATION = CRITICAL that nobody requested. What triggered it, and what does it tell you about the table?

You want to free memory by dropping just one cold column from memory. Can you, given LOAD and UNLOAD syntax?

In CHECK_TABLE_CONSISTENCY, how do you run all checks but skip a couple of expensive ones (or skip specific tables)?

Round-robin gives perfectly even distribution. So why is it the wrong choice for almost every business table?

You build a shared cache as a no-logging table. After a HANA restart, what's gone, and why is that the intended behaviour?

For placement rules, what do MIN_ROWS_FOR_PARTITIONING, INITIAL_PARTITIONS, and REPARTITIONING_THRESHOLD each control — and what's fixed about the repartition step?

To get a past version from a system-versioned table, which table do you actually SELECT from, and what's the common mistake?

For which column types does a full-text index appear automatically, and for which must you create it by hand?

You need to replicate a table's structure to another system without copying its data. Which EXPORT option, and what tells you it worked?

You want consistency checks to run automatically. Which check can be scheduled and which cannot, and where do you configure/read it?

What is the default column-store index, and why does it map value IDs rather than raw values to rows?

A DBA wants to raise savepoint_interval_s to cut I/O. What is the hidden cost of a longer savepoint interval?

Delta stores across many tables stop merging automatically. Which background process is responsible, and where would you look first?

A colleague wants to partition a large row-store table. Why won't that work, and what's the prerequisite?

Dictionary compression assigns a value ID per distinct value. What ceiling does the value-ID size impose, and when could you hit it?

You re-import an object that already exists, expecting it to refresh. What actually happens by default, and how do you force the overwrite or import only certain types?

For temporary tables, which DML is blocked by store type — DELETE, UPDATE/UPSERT/REPLACE, and TRUNCATE?

Two sessions use the same global temporary table. Do they see each other's rows, and what survives a restart — the object, the data, or both?

What are SAP's rule-of-thumb numbers for when to partition and how large each partition should be?

Which catalog/monitoring column tells you whether a table is row- or column-store, and which view has which?

You try to DROP a partition and HANA refuses even though it's empty. Which two situations make DROP PARTITION outright illegal regardless of emptiness?

For a hash-partitioned table with a primary key, what relationship must the hash key have to the PK — and which direction is NOT required?

Which column-store indexes survive on disk and which are rebuilt at load — and why does that affect load time?

In a multipartitioning (grouped) table, why does MOVE PARTITION relocate the entire partition group rather than one partition, and what would break otherwise?

What distinguishes a volatile table from a local temporary (#) table, given both are session-local and vanish at session end?

You want the fast binary export format. For which tables is it actually available, and what silently falls back to CSV?