FAQ: SAP HANA Partitioning


1. Where do I find detailed information about partitioning?

General information related to partitioning is contained in the SAP HANA Administration Guide.

2. Which indications exist for issues related to SAP HANA partitioning?

The following SAP HANA alerts indicate problems in the memory area:

Alert Name SAP Note Description
17 Record count of non-partitioned column-store tables 1909763 Determines the number of records in non-partitioned column-store tables. Current table size is not critical. Partitioning need only be considered if tables are expected to grow rapidly (a non-partitioned table cannot contain more than 2,147,483,648 (2 billion) rows).
20 Table growth of non-partitioned column-store tables 1910140 Determines the growth rate of non-partitioned columns tables.
27 Record count of column-store table partitions 1910188 Determines the number of records in the partitions of column-store tables. A table partition cannot contain more than 2,147,483,648 (2 billion) rows.

SQL: “HANA_Configuration_MiniChecks” (SAP Notes 1969700, 1999993) returns a potentially critical issue (C = ‘X’) for one of the following individual checks:

Check ID Details
510 Tables with > 100 partitions
520 Tables / partitions > 1.5 billion rows
521 Table histories > 1.5 billion rows
2020 Partitioned SID tables
2025 Partitioned special BW tables < 1.5 bill. rows

3. How can I check and evaluate the current partitioning in a system?

Partitioning information can be retrieved in the following ways:

  • Monitoring view M_CS_PARTITIONS
  • SQL: “HANA_Tables_PartitionedTables” (SAP Note 1969700) to list partitioned tables
  • SQL: “HANA_Tables_Partitions” (SAP Note 1969700) to list individual partitions of one or multiple tables
  • SQL: “HANA_Tables_ColumnStore_TableHostMapping” (SAP Note 1969700) to show the partition distribution of tables across nodes in a scale-out scenario

4. What does partitioning in SAP HANA environments mean?

Partitioning means that tables are split into sub-tables, the so-called partitions, based on defined partitioning criteria.

5. Is partitioning available for both row store and column store?

Partitioning is only available for tables located in the column store. The row store doesn’t support partitioning.

6. Is partitioning transparent for the application?

Partitioning is transparent for the application in a way that applications work properly with all partitioning strategies.
Nevertheless partitioning can have an impact on performance, so it can make a difference for the end user and the system load – both in a positive and negative way. In order to minimize the risk of performance regressions it is important to implement a good partitioning strategy.
Moreover, applications may implement special handling for partitions, for example they may add range partitions for upcoming time periods.

7. For what reasons is partitioning required or useful?

In general partitioning is most useful for large tables. In the following situations you can take advantage of partitioning:

Scenario Details
Tables with many records Each table and partition must not contain more than 2 billion records in the column store. Tables with a risk to reach the 2 billion record limit mid-term should be partitioned.
Be aware that SID tables in BW environments (typically following the naming convention /B%/S% should normally not be partitioned because:

  • They can never exceed the 2 billion records limit due to BW SID limitations (see SAP Note 1331403).
  • They typically have 2 unique indexes and so changes would require expensive remote partition checks for uniqueness.

Also other BW attribute tables (typically following the naming convention /B%/H%, /B%/I%, /B%/J%, /B%/K%, /B%/Q%, /B%X% and /B%Y%) should only be considered in special cases (e.g. if the number of records exceeds 1.5 billion and there is a risk to hit the technical limit of 2 billion rows.

Complex queries In scale-out scenarios you can distribute the load of complex requests across different nodes if you locate the table partitions on different hosts. Be aware that this kind of approach can have a negative impact on “simple” queries that also require to access multiple hosts.
Complex queries can also take advantage of parallelism. By processing data in all partitions concurrently the runtime can be reduced significantly.
Tables with hot and cold data If tables contain frequently accessed (hot) areas and other areas with hardly any access (cold) these ranges can be separated by partitioning. As a consequence only the hot data is regularly accessed and there is no overhead for loading the cold areas into memory and processing them.
The possibility to satisfy a query with accessing only a subset of partitions is also called partition pruning.
Delta merge optimization If data is mainly modified in some partitions there is less data that needs to be delta-merged.
Explicit partition handling In some cases it can be useful that the application controls the creation and existence of partitions based on specific criteria.

8. Which partitioning types exist?

The following partitioning types exist in SAP HANA environments:

Partitioning type Details Advantages  Disadvantages / Restrictions
HASH Records are assigned to partitions based on a hash algorithm on the partition key columns Easy setup
No maintenance required
Rather even row distribution if partition keys are selective without frequent values
All partitions need to be scanned unless all partition key columns are specified with “=” or “IN” conditions in the WHERE clause.
Need to be based on primary key columns (if primary key exists, for first-level partitioning)
No logical separation of data (e.g. hot vs. cold) possible
ROUNDROBIN Records are assigned in a round-robin manner to the available partitions, partition key columns are not required Easy setup
No maintenance required
Even row distribution
All partitions need to be scanned, no partition pruning possible
No logical separation of data (e.g. hot vs. cold) possible
No primary key allowed (because overhead of constraint evaluation would be significant)
RANGE Records are assigned to partitions using defined (non-overlapping) partition ranges Allows application related separation of data into different partitions (e.g. hot vs. cold)
Consequently the best way to take advantage of partition pruning and delta merge optimization
Setup of optimal ranges requires application knowledge
Regular maintenance required (e.g. definition of new partitions for upcoming year in case of time-based ranges, deletion of old partitions after archiving)
Potentially uneven row distribution
Need to be based on primary key columns (if primary key exists, single-level partitioning)

9. Is LIST partitioning available?

LIST partitioning allows to assign single or multiple values to a single partition. As part of RANGE partitioning SAP HANA allows to assign a single value to a single partition, but it is not possible to assign multiple values to a single partition. A dedicated LIST partitioning option is not available.

10. What is single-level and multi-level partitioning?

If a table is partitioned exclusively by one of the above partitioning approaches (HASH, ROUNDROBIN, RANGE), it is called single-level partitioning.
If each of the partitions itself is partitioned again by other criteria, we call it multi-level partitioning. In the following picture you can see a multi-level partitioning with a HASH partitioning on level 1 and a RANGE partitioning on level 2:

The total number of partitions is the product of the number of partitions on every level.
The partition key columns of the second level can be chosen independent of the primary key of the table in case of HASH / HASH and RANGE / HASH partitioning.
The partitions on the second level form so-called partition groups (in the example above partitions 1 and 2 are one partition group, partitions 3 and 4 are another partition group). All members of a partition group are always located on the same node and can only be moved as a unit.

11. Are locks involved when a table is partitioned?

The standard table re-partitioning sets an exclusive object lock (see SAP Note 1999998), so all modifying DML operations are blocked while the partitioning is performed. SELECTs are still possible without any restriction.

12. Which best practices exist for partitioning tables?

The following general best practices exist for partitioning tables:

Rule Details
As few partitioned tables as possible Only partition tables if you see a clear benefit without significant regressions.
As few partitions as possible An unnecessary high amount of partitions results in overhead because some queries may have to access all partitions to find the data. So consider the following general rules before defining a certain number of partitions:

  • If you partition tables due to the 2 billion limit it is usually acceptable if individual partitions contain up to 500 million records.
  • If you partition by date, you should avoid using granular ranges (e.g. days or weeks) resulting in a high amount of partitions
  • If you use RANGE partition on columns with data that is not evenly distributed (e.g. number range column with multiple different number ranges), you should check the actual value distribution and define the range limits accordingly.
As few partition key columns as possible It is often useful to keep the number of partition key columns at a minimum extent:

  • In case of HASH partitioning it is often useful to use only the most selective primary key column as partition key column to make sure that all requests (containing at least this column) have to scan only a single partition. If not all partition key columns are specified in the WHERE clause, all partitions have to be scanned.
SAP Suite on HANA: All partitions on same host In scale-out Suite on HANA environments it is typically of advantage to keep all partitions of a table on the same host. As of SPS 08 this can be achieved with an appropriate table placement configuration.
As a fallback option you can use a dummy first level partitioning (e.g. on MANDT) and perform the actual partitioning on the second level. In this case all partitions will be located on the same host.
Repartitioning: Choose new number of partitions as multiple / divider of current number of partitions If a table is already partitioned it is most efficient to choose a new number of partitions that is a factor 2 multiple or divider of the current number of partitions (e.g. 4 –> 8 or 6 –> 3 partitions), because only in this case the repartitioning can happen in parallel on different partition groups and hosts (“parallel split / merge”).
No additional unique constraints Avoid partitioning tables with additional unique constraints (e.g. unique secondary index), because the uniqueness checks impose a significant overhead.

13. How can partitioning changes be implemented?

Most convenient is the usage of the “Table Distribution” functionality of SAP HANA Studio as described in the SAP HANA Administration Guide.
Alternatively it is possible to use individual SQL statements. The table below contains some important examples. For further details check the SAP HANA SQL Reference.

Activity Example command
Create a table with HASH partitioning CREATE COLUMN TABLE … PARTITION BY HASH (<column1>, …, <columnN>) PARTITIONS <num_partitions>
Create a table with RANGE partitioning CREATE COLUMN TABLE … PARTITION BY RANGE (<column1>)
( PARTITION 1 <= VALUES < 100,
PARTITION 100 <= VALUES < 200,
Create a table with multi-level HASH / RANGE partitioning CREATE COLUMN TABLE … PARTITION BY HASH (<column1>, <column2>) PARTITIONS <num_partitions>,
RANGE (<column3>)
Add a new RANGE partition ALTER TABLE … ADD PARTITION 200 <= VALUE < 300
Drop an existing RANGE partition ALTER TABLE … DROP PARTITION 100 <= VALUE < 200
Move a partition to a different host ALTER TABLE … MOVE PARTITION <partition> TO ‘<host>:<port>’
Adjust partitioning of an already existing table ALTER TABLE … PARTITION BY …
Transfer a partitioned table in a non-partitioned table ALTER TABLE … MERGE PARTITIONS

Tables partitioned by applications for aging (time selection partitioning) cannot be re-partitioned or converted to a non-partitioned table (“If a table is partitioned with Time Selection, it is not allowed to repartition it to anything”). This is a limitation caused by the non-enforced constraint checks on historical partitions.

14. How can the consistency of partitions be checked?

The consistency of partitions, which e.g. includes the correct assignment of records to partitions, can be checked with the following CHECK_TABLE_CONSISTENCY options:


See SAP Note 1977584 for more details.

15. Is it possible to truncate a partition?

No, it is not possible to use a TRUNCATE operation on partition level.

16. How should tables be partitioned in BW environments?

BW takes care of the partitioning scheme of DSO, PSA and fact tables on its own. Typically it is not required or recommended to interfere with this process. The tables and partitions may be re-distributed though in a scale-out system and their locations configured via table placement. For example, consult SAP Notes 1908073 and 1908075. PSA tables are typically partitioned based on request (see SAP Note1767880). SAP Note 2081135 describes dynamic range partitioning for advanced DSOs active as of Rev. 83.
Other tables should normally not be partitioned. In special situations with a clear need (e.g. if there is a risk to reach the 2 billion record limit and there is no way to reduce the number of records from an application perspective) individual partitioning can be performed. See SAP Note 2019973 for more information how to handle and partition large tables in BW.
Generally avoid partitioning SID tables (/BI0/S*, /BIC/S*) because the combination of partitioning, two unique indexes and the particular change load can result in problems due to uniqueness checks (thread method “CheckRemoteUniqueConstraint”, see SAP Note 2114710). See SAP Note 1331403 for managing large SID tables from a BW perspective.

17. Is the partitioning information kept during homogeneous SAP HANA system copies?

If the homogeneous system copy is performed using backup and restore the partitioning information is kept.
A system copy based on R3load doesn’t copy the partitioning information per default. If you want to keep it you have to use the SMIGR_CREATE_DDL report.

18. Is the partitioning information kept during transports?

Partitioning information is not available in ABAP DDIC. Therefore transports don’t consider it and partitioning is not transported. This means that you have to activate partitioning in all involved systems of your ABAP system landscape individually.

19. Is the partitioning information kept during ABAP table conversions and SAP upgrades?

Partitioning information is kept when a table conversion in SAP ABAP is performed and when a SAP upgrade is performed.

20. My table has a partition specification, but it shows only PART_ID 0 in M_CS_TABLES. Is this correct?

Tables partitioned with HASH or ROUNDROBIN and only one partition are non-partitioned tables which have a partition specification.

21. Where can I see which partitions are loaded into memory?

This information is available in column LOADED of monitoring view M_CS_TABLES:

LOADED Description
NO No column of partition is loaded into memory
PARTIALLY Some columns of partition are loaded into memory
FULL Partition is completely loaded into memory

SQL: “HANA_Tables_Partitions” (SAP Note 1969700) can be used to display the LOADED state and other partition details.

22. Is it possible to load a single partition only?

No. The unload and preload operations consider all partitions of a table (exception: see next question).

23. Are cold partitions for aging and time-selection tables pre-loaded?

As of SPS 09, load and pre-load do not consider cold partitions.

24. How many partitions are allowed for one table?

There is a maximum of 1000 (SPS <= 09) or 16000 (SPS >= 10) partitions for one table. For multi-level partitioning, multiply the number of first-level partitions with the number of second-level partitions to get the total number of partitions. This maximum number for one table is independent of the table location in a scale-out landscape.

25. Which data types are allowed for partitioning columns?

The supported data types are listed in the SAP HANA Administration Guide. For SPS 09 the following types are supported:

Partitioning Type Data type
Lob columns are required to be memory LOBs and not disk LOBs.

26. Are there specific partitioning recommendations for certain SAP applications and tables?

The following SAP Notes contain suggestions for partitioning specific SAP application tables:

SAP Note Application Tables
1719282 SAP Point of Sale (POS)
SAP Customer Activity Repository (CAR)
2190377 SAP Unified Demand Forecast (UDF)
SAP Demand Data Foundation(DDF)

Leave a Reply