For SAP HANA SP6 (Revision 60 and above), the landscape redistribution process has been fundamentally revised: you can now control the distribution of tables to the nodes of a distributed SAP HANA DB and the number of level 1 partitions of tables using the table classification and placement functionality.
Table Classification and Placement
Usually, one table is not the sole storage location for data from a particular application, but is instead closely related to other tables that are also associated with that application.
So that the landscape redistribution process can recognize similar or associated database tables as such, the table classification functionality was created. This allows the landscape redistribution to optimally distribute tables of this kind to different nodes in a distributed SAP HANA database. In this way, you can prevent tables that are regularly joined being stored on separate nodes, which would mean that they first had to be sent across the network when SQL statements are executed. In this case, the tables are placed on the same node and the join can be locally optimized and executed.
For each table, you can specify a group type, subtype, and group name. You can either do this when creating the table or later:
CREATE TABLE … GROUP TYPE <TYPE> GROUP SUBTYPE <SUBTYPE> GROUP NAME <NAME>;
ALTER TABLE … SET GROUP TYPE <TYPE> GROUP SUBTYPE <SUBTYPE> GROUP NAME <NAME>;
From revision 65 onwards also an "ALTER TABLE … UNSET GROUP" will be available. (For the precise syntax, see the SAP HANA SQL Reference.)
For example, the table classification for the tables of a BW Data Store object with the technical name ZFIGL would be:
Table Name | Group Type
(GROUP_TYPE) | Subtype
(SUBTYPE) | Group Name
(GROUP_NAME) |
/BIC/AZFIGL00 | sap.bw.dso | ACTIVE | ZFIGL |
/BIC/AZFIGL40 | sap.bw.dso | QUEUE | ZFIGL |
/BIC/B0000197000 | sap.bw.dso | CHANGE_LOG | ZFIGL |
All of the tables in this example have the group type sap.bw.dso and are therefore identified as tables of BW DataStore objects. The subtype is different, depending on the use of each of the individual tables. The group name is the technical name of the DSO in BW. This allows landscape redistribution to identify that these tables are associated. This means that all three tables are placed on the same node of the distributed SAP HANA database during the landscape redistribution process.
The table classification is stored in the TABLE_GROUPS table (see also SAP HANA System Tables and Monitoring Views documentation). There is an entry in this table for all tables for which a group type, subtype, or group name was specified. Any tables that do not have an entry in this table have not yet been classified.
Creating New Tables
The table classification and the table placement that results from it are, however, not only taken into account by the landscape redistribution. The defined rules are also used when creating new tables. In the example above, this would mean that an additional table with the group name ZFIGL would be created on the same node as the existing tables.
Number of Level 1 Partitions
Another use case for the table classification functionality is to control the number of partitions at level 1 of the partitioning specification.
The DSO ZFIGL from the example above has the following partitioning:
- Level 1: HASH (DOCNR, LINEITEM)
- Level 2: RANGE (CALMONTH)
The number of partitions at level 2 (range for the CALMONTH column) is defined and managed by the application. The number of partitions at level 1 and therefore the distribution of a table across multiple nodes, on the other hand, can be determined by the landscape redistribution. For example, small tables should only have one partition here, and should not be distributed. Very large tables, on the other hand, should be stored with as many partitions as possible on a large number of nodes.
Customizing Table Placement
The rules that are used for table placement and for determining the level 1 partitions are stored in the table TABLE_PLACEMENT in the schema _SYS_RT.
The TABLE_PLACEMENT table has the following columns for specifying groups of tables or individual tables:
- SCHEMA_NAME, TABLE_NAME, GROUP_NAME, GROUP_TYPE, and SUBTYPE
You do not need to fill all of these columns when creating and maintaining rules. A more specific entry takes precedence over a more general entry. For example, if you only fill SCHEMA_NAME, the rules apply for all tables in the schema. If there is also an entry in which SCHEMA_NAME and GROUP_TYPE are filled, this entry overwrites the first, more general entry for the corresponding tables. (For a more detailed overview of the priorities when evaluating the entries in the TABLE_PLACEMENT table, see SAP Note 1908082.)
You can control the number of level 1 partitions with the following columns:
- MIN_ROWS_FOR_PARTITIONING
This defines the minimum number of records that must exist in a table before level 1 partitioning takes place.
If the threshold value in the MIN_ROWS_FOR_PARTITIONING column is exceeded, the landscape redistribution performs a partitioning. The initial number of partitions is stored in this column.
Once a table has been partitioned with the specified initial number of partitions, for performance reasons, the table is only repartitioned by doubling the number of partitions. For example, if the initial number of partitions is three, this would result in six partitions being created during a repartitioning. You maintain the threshold value for the number of records in a partition that triggers a repartioning of this kind in this parameter.
However, as a general principle, the system never creates more partitions than the number of available nodes. For example, if a distributed SAP HANA database only has five nodes, the repartioning described above, from three partitions to six partitions, would not take place. The partitioning rules also apply only to tables for which HASH or ROUNDROBIN partitioning is specified at level 1. Tables without a partitioning specification are also not automatically partitioned when the threshold values are exceeded.
The table placement is controlled using the column LOCATION. Possible values are MASTER, SLAVE, and ALL. You can use these to determine the types of nodes of a distributed SAP HANA database on which the respective tables can be stored.
Example: Customizing SAP BW InfoCubes
SCHEMA_NAME | TABLE_NAME | GROUP_TYPE | SUBTYPE | GROUP_NAME | MIN_ROWS_FOR_
PARTITIONING | INITIAL_
PARTITIONS | REPARTITIONING_
THRESHOLD | LOCATION |
| | | | | 2.000.000.000 | 3 | 2.000.000.000 | slave |
SAPBWP | | | | | 2.000.000.000 | 3 | 2.000.000.000 | master |
SAPBWP | | sap.bw.cube | | | 40.000.000 | 3 | 40.000.000 | slave |
The first entry in the TABLE_PLACEMENT table is the default entry. All specification columns are empty for this entry. This ensures that there is always an applicable rule for creating or distributing tables.
The second entry overwrites the location of the default entry (slave) for all tables of the SAP BW schema.
The third entry defines the size of level 1 partitions and their location for InfoCubes in the SAP BW schema.
This Customizing means that all InfoCube tables are stored on the slave nodes of the distributed SAP HANA system. InfoCube tables with more than 40 million records are divided into three partitions. If the individual partitions have more than 40 million entries on average, these are halved again in accordance with the REPARTITIONING_THRESHOLD threshold value. This results in a table with six partitions. (As described above, the system does not create more partitions for a table than there are nodes available. The dividing of the three initial partitions into six therefore only takes place, if the SAP HANA database has at least six slave nodes. If there are five or fewer nodes, the number of partitions in this Customizing example is limited to three. For performance reasons, there is no automatic repartitioning from three to five partitions, for example. If you want to repartition to five partitions, you need to enter a corresponding Customizing rule in the TABLE_PLACEMENT table.)
There is also no automatic repartitioning when threshold values are exceeded. Instead, this is proposed during the next execution of the landscape redistribution process.
Tables in the SAP BW schema that do not belong to the group type sap.bw.cube are stored on the master node, in accordance with the second entry. These include, for example, the tables of the SAP Basis component, and those of the ABAP runtime environment.
Tables that are not in the SAP BW schemas (for example, tables replicated using SLT) are always stored on a slave node, in accordance with the first entry.
Note: For reasons of clarity, this example only takes account of the InfoCube tables of an SAP BW system. For a complete description of the table placement for SAP BW, see SAP Note 1908073.
Landscape Redistribution Parameters
For a general guide to performing the landscape redistribution, see the SAP HANA Administration Guide. This article therefore only explains a few parameters that influence the behavior of the landscape redistribution in relation to the table classification and placement functionality.
- Global Config (global.ini):
[table_placement] same_num_partitions
If this parameter is set to true (default), all tables with the same group name have the same number of level 1 partitions. The number of partitions is determined by the largest table within the group.
- Service Config (typically 'indexserver.ini'):
[table_redist] all_moves_physical
By default, when the landscape redistribution is performed, tables are only moved to the new node with their working memory part. The persistence part is written to the new node during the next delta merge. If you want to have the persistence part moved immediately during the landscape redistribution, set this parameter to true. Note, however, that this can significantly extend the runtime.
[table_redist] force_partnum_to_splitrule
Setting this parameter to true forces the execution of operations that change the number of level 1 partitions. For example, if a table has two level 1 partitions but should have three according to the Customizing settings, the landscape redistribution process would not, by default, adjust this. Activating this option forces the adjustment.
Additional Important Notes
- All tables in the 'SYS%‘ and ‘_SYS%‘ schemas are explicitly excluded from this functionality.
- The classification of row store tables is currently not taken into account. To ensure that these tables are always created on the master node, or are moved there, you need to set the parameter ‘method‘ in the section [table_placement] of global.ini to "2".
- The Customizing in the TABLE_PLACEMENT table replaces the INI parameters used before SAP HANA SP6 for the column store.
- The landscape redistribution process is performed in two steps:
- Generation of a plan
- Execution of the plan (Currently, the plan generated in step 1 is always completely executed. In the future, it will be possible to manually adjust the plan.)
