Table Placement

Using table classification and placement you can control the number of level 1 partitions of tables and the distribution of tables to the hosts of a distributed SAP HANA database.

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.

Table classification enables the landscape redistribution process to recognize similar or associated database tables. This allows the landscape redistribution to optimally distribute tables of this kind to different hosts in a distributed SAP HANA database. In this way, you can prevent tables that are regularly joined from being stored on separate hosts, which would mean that the requests first had to be sent across the network when SQL statements are executed. In this case, the tables are placed on the same host 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>; ALTER TABLE … UNSET GROUP (see the SAP HANA SQL and System Views Reference).

The table classification is stored in the TABLE_GROUPS table. 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.

For example, the table classification for the tables of a SAP NetWeaver Business Warehouse (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 SAP 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 SAP BW. This allows landscape redistribution to identify that these tables are associated. This means that all three tables are placed on the same host of the distributed SAP HANA database during the landscape redistribution process.

Note

: If Table Placement is not configured correctly before data is loaded during migration/installation of SAP BW on SAP HANA, the data distribution may lead to individual hosts being overloaded. The data may be

Creating New Tables

The table classification and the table placement rules are taken into account on two occasions: landscape redistribution and during table creation. In the example above, this would mean that an additional table with the group name ZFIGL would be created on the same host as the existing tables.

Number of Level 1 Partitions

Table classification can also be used 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 hosts, 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 hosts.

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:

Column Description

MIN_ROWS_FOR_PARTITIONING This defines the minimum number of records that must exist in a table before level 1 partitioning takes place.

INITIAL_PARTITIONS 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. REPARTITIONING_THRESHOLD Once a table has been partitioned with the specified

Column Description

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.

LOCATION 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.

However, as a general principle, the system never creates more partitions than the number of available hosts. For example, if a distributed SAP HANA database only has five hosts, the repartitioning 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.

Example: Customizing SAP BW InfoCubes

SCHEMA_NAME GROUP_TYPE MIN_ROWS_FO R_

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

Note

Only relevant columns from the TABLE_PLACEMENT table are shown above. Empty columns (that is, TABLE_NAME, SUBTYPE, GROUP_NAME) were left out for clarity.

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 hosts 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

The following parameters influence the behavior of the landscape redistribution in relation to the table classification and placement functionality.

Parameter Description

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.

global.ini:

[table_placement] method 2

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

Parameter Description

there, you need to set the parameter ‘method‘ in the section [table_placement] of global.ini to "2".

Important Notes

● All tables in the 'SYS%‘ and ‘_SYS%‘ schemas are explicitly excluded from this functionality.

● The customizing of the TABLE_PLACEMENT table replaces the INI parameters used before SAP HANA SPS 06 for the column store.

● The landscape redistribution process is performed in two steps: 1. Generation of a plan

2. Execution of the plan

Related Information

SAP HANA SQL and System Views Reference