Performing Table Placements

Table classification and table placement configuration, enhanced by partitioning, build the foundation for controlling the data distribution in an SAP HANA scale-out environment.

Associated tables can be classified by a common table group.

The SQL interface of SAP HANA provides three possible kinds of classifications: group name, group type, and subtype. Tables that have been classified with group information are included in the SYS.TABLE_GROUPS table, and you can review classification details in the monitoring view SYS.TABLE_GROUPS (see details following). Tables with the same group name are kept on the same host, or, in the case of partitioned tables that are distributed over several hosts, corresponding first-level partitions are distributed for all tables the same way.

One table in the group is defined as the leading table and its table placement settings are applied to all other tables in the group. This can be, for example, the location, or in the case of partitioned tables (if SAME_PARTITION_COUNT is set in SYS.TABLE_PLACEMENT, see below), the number of first-level partitions.

For native applications, the application developer can define a grouping manually, for example, by grouping tables together that are often joined. The following statements show examples of setting table group attributes using CREATE and ALTER statements:

1234567
CREATE COLUMN TABLE "HA201_DEMO"."HA201_TABLE"(
  HAY INT,
  GEORG INT,
  HAKAN INT,
  PRIMARY KEY (HAY, GEORG))
 GROUP NAME DEMO GROUP TYPE EXAMPLE GROUP SUBTYPE TEST GROUP LEAD;
 

This create table statement creates a table named HA201_TABLE, sets the group name to DEMO, the group type to EXAMPLE, the group subtype to TEST, and makes this the lead table in the group DEMO.

123456
ALTER TABLE "HA201_DEMO"."HA201_TABLE" SET
  GROUP NAME DEMO
  GROUP TYPE EXAMPLE
   GROUP SUBTYPE TEST
  GROUP LEAD;
 

This alter table statement sets the group name to DEMO, the group type to EXAMPLE, the group subtype to TEST, and makes this the lead table in the group DEMO, for an existing table.

This can also be performed dynamically based on the information available in the SQL Plan Cache, with the Join Path Analysis tool within the Data Distribution Optimizer, or with the ABAP grouping report (SHDBSO_TABLE_GROUPING) for SAP S/4HANA scale-out. See SAP Note: 2447004 - "Table Grouping Report for S/4 HANA in scale-out systems".

The SAP HANA Cockpit Table Redistribution tools also include an optional preparation step to integrate the Group Advisor tool into the plan generation process to create table groups dynamically.

Application data is usually stored in a multitude of database tables, and data from several of these tables is combined using SQL operations, such as join or union, when it is queried. As these relations between different tables are defined in the application code, this information is not available in SAP HANA. The table classification feature provides a possibility to push down this semantic information in the database by allowing administrators to define groups of tables. This information can be used, for example, when determining the number of partitions to be created, or, in the case of a scale-out landscape, the node on which to locate the tables or partitions.

The classification is performed by providing each table with a group name, group type, and subtype. Based on combinations of these elements, as well as the table names and schema names, a set of configuration values can be defined as table placement rules. These rules are used to control, for example, the placement of partitions or the number of partitions during operations like table creation or redistribution. By doing this, associated or strongly-related tables are placed in such a way that the required cross-node communication is minimized for SQL operations on tables within the group.

Table placement rules are applied during system migration or table creation, but it may also be necessary to adjust the location or the number of partitions on an ongoing basis for handling data growth. Therefore, table redistribution can also be run on demand to optimize the landscape as the system evolves. Repartitioning is always necessary, for example, for any table or partition in the database that reaches the maximum count of 2 billion rows.

The following tools are available to perform table repartitioning and redistribution. These tools evaluate the current landscape and determine an optimized distribution:

• SAP HANA Table Redistribution

• Data Distribution Optimizer (part of SAP HANA Data Warehousing Foundation)

Balancing an SAP HANA scale-out landscape with these tools is done in two stages:

1. Generation of a plan based on table placement rules (described in detail in a later section). After generating the plan, you can review it and adjust the definition of the rules if required.
2. Execution of the plan that implements the partitioning and distribution changes.

Because split table and move table are operations that require table locks, the execution of the plan should not be performed during a period where there is heavy load on the database.

Table placement rules are defined in the table SYS.TABLE_PLACEMENT. The system privilege TABLE ADMIN is required to maintain these settings. Placement rules basically address the following areas:

• Classification, that is, related tables that must be located together are organized in groups

• Configuration settings to manage partitioning (number of initial partitions, split threshold, and so on)

• Physical distribution or location of tables or partitions in the server landscape

When creating a table, if the defined rules match with a table or a table group, SAP HANA considers them while creating the table. Keep in mind that partition specifications must still be defined by the application.

The TABLE_PLACEMENT table provides a customizing interface that can be used for the dynamic management of partitions and locations.

The partitioning parameters are used to define how a table or a group of tables is partitioned if the table has a first-level partitioning specification of hash or round-robin. Range partitioning is not handled in this way.

If the number of rows is lower than MIN_ROWS_FOR_PARTITIONING, the table consists of only one partition. If this minimum row limit is exceeded, the table is partitioned in as many parts as fulfills the following constraints:

• The number of partitions is larger or equal to the value of (row count of the table) / REPARTITIONING_THRESHOLD.

• The number of partitions is a multiple of INITIAL_PARTITIONS.

• The number of partitions is smaller or equal to the number of hosts if the parameter max_partitions_limited_by_locations is not set to false and the number of partitions is less than the value of the parameter max_partitions (see details below).

Therefore, if the table has more than one partition, there are at least INITIAL_PARTITIONS partitions, and each partition has less than REPARTITIONING_THRESHOLD records. In this context, partitions refer to first-level partitions (of type HASH or ROUNDROBIN).

Note that when a partitioned table is created without an estimated row count (default behavior), a partitioned table is created with INITIAL_PARTITIONS first-level partitions. Whereas in a redistribution, it is targeted to have a single first-level partition (assuming MIN_ROWS_FOR_PARTITIONING > 0). In specific applications, creation is performed with an estimated row count, for example, BW with 1 million, and therefore it is created with only one first level-partition (assuming MIN_ROWS_FOR_PARTITIONING > 1,000,000).

There is no automatic repartitioning when threshold values are exceeded. Instead, this is proposed the next time the redistribution process is executed.

The values entered for partitioning must be consistent with the physical landscape, especially the number of server nodes available:

• If repartitioning is necessary, tables are only repartitioned by doubling the number of existing (initial) partitions. This is done for performance reasons. The maximum number of (first-level) partitions reached by that process is defined by parameter global.ini > [table_placement] > max_partitions (default: 12).
• By default, the system does not create more partitions than the number of available hosts (or more specifically possible locations). For example, if INITIAL_PARTITIONS is set to 3, but the distributed SAP HANA database has five possible locations, repartitioning from three to six partitions would not take place. A table can have more than one partition per host if the parameter global.ini > [table_placement] > max_partitions_limited_by_locations is set to false (default: true). This rule is disregarded if a higher number of first level partitions is required to partition groups with more than 2 billion records (global.ini > [table_placement] > max_rows_per_partition, default: 2,000,000,000).

There are the following predefined values for possible locations:

• Coordinator: represents the primary node

• Worker (or workers): represents all worker nodes that belong to the worker group ‘default’

• All: represents all nodes that belong to the worker group ‘default’, that is, the coordinator node and the worker nodes

The worker group assignment can be found in the WORKER_ACTUAL_GROUPS entry of the M_LANDSCAPE_HOST_CONFIGURATION view, and can be accessed by executing the following procedure:

1
call SYS.UPDATE_LANDSCAPE_CONFIGURATION('GET WORKERGROUPS','<hostname>')

In addition, it is also possible to create custom location definitions by using the following procedure to assign worker groups to a host:

1
call SYS.UPDATE_LANDSCAPE_CONFIGURATION('SET WORKERGROUPS','<hostname>','<name1> <name2> <name3>')

The TABLE_PLACEMENT table is read in such a way that a more specific rule supersedes a more generic one. A complete matrix of priorities is available in SAP Note: 1908082 — "Table Placement Priorities".

For example, an entry with only one schema applies to all tables of that schema; additional entries for that schema and specific group types overrule the more general rule.

You can see the actual table placement settings per table by querying the M_EFFECTIVE_TABLE_PLACEMENT system view. You can see the valid location(s) according to the configuration, and for each partitioning parameter the actual values, and in the corresponding _MATCH columns the reason (matching rule) for those.

The information how a table is classified, can be reviewed in the SYS.TABLE_GROUPS monitoring view.

In a distributed SAP HANA system, tables and table partitions are assigned to an index server on a particular host at their time of creation, but this assignment can be changed. In certain situations, it is even necessary. You can use the SAP HANA cockpit or the SQL Editor to execute automatic redistribution operations.

There are several occasions when tables or partitions of a table need to be moved to other servers. For example, if you plan to remove a host from your system, then you first need to move all the data on that host first to the other hosts in the system. Redistributing tables may also be useful if you suspect that the current distribution is no longer optimal.

Redistribution operations are available to support the following situations:

• You are planning to remove a host from your system.

• You have added a new host to your system.

• You want to optimize current table distribution.

• You want to optimize table partitioning.

Although it is possible to move tables and table partitions manually from one host to another, this is neither practical nor feasible for a large-scale redistribution of data.

In SAP HANA cockpit, search for the Table Distribution card. From this card, select View Current Table Distribution. The Table Distribution application outlines partitioning and distribution information of tables in a distributed system. You can reduce the number of displayed tables, by using the filter function.

Furthermore you can choose additional actions in the Table Distribution application.

There are the following available operations:

• View table distribution

• Generate table redistribution plan

• Save current table distribution

• Restore saved tabled distribution plan

• Rerun table distribution plan

SAP HANA supports several redistribution operations that use complex algorithms, as well as configurable table placement rules and redistribution parameters, to evaluate the current distribution and determine a better distribution depending on the situation. Administrators can use the table redistribution feature in the SAP HANA cockpit to create a plan for redistributing and repartitioning tables. The administrator can review the plan and execute it.

Balance table distribution

The load on a scale-out system changes over time with the usage of the system. This option generates a plan to move tables and partitions to their proper hosts if they are currently on invalid hosts according to the rules specified in the TABLE_PLACEMENT table. The plan checks whether a split or merge is necessary and calculates optimal positions for the parts and tables. All types of tables and parts can be moved. However, only the tables that you have permission to view as catalog objects are affected.

Check the number of partitions

In a scale-out system, partitioned tables are distributed across different index servers. The location of the different partitions can be specified manually or determined by the database when the table is initially partitioned. Over time, this initial partitioning may no longer be optimal, for example, if a partition has grown significantly.

This option evaluates whether or not partitioned tables need to be repartitioned. The plan specifies how partitioned tables are repartitioned (split or merged) and how newly-created partitions are distributed. Note that this is only relevant for column-store tables. System tables, temporary tables, and row-store tables are not considered.

Redistribute tables after adding host(s)

After adding one or more worker hosts to a scale-out system, you may need to redistribute the tables across the active index servers. This option checks whether new partitions can be created and generates a plan to move the tables and table partitions as necessary.

Check the correct location of tables and partitions

This option generates a plan to move tables and partitions to their proper hosts if they are on invalid hosts according to the rules specified in the TABLE_PLACEMENT table. Only the tables that you have permission to view as catalog objects are affected.

Housekeeping

Some regular operations need to be done from time to time. This option allows you to perform various operations in the system, such as, optimize compressions, defrag, load table, and merge delta. Only the tables that you have permission to view as catalog objects are affected. Also, you must have the appropriate privileges to perform specific housekeeping operations, such as delta merge.

Table redistribution can also be performed using the SQL commands. You can create an SQL script that executes all the required steps, or you can use the SQL Editor provided by the SAP HANA Database Explorer.

Table redistribution is based on the table placement rules defined in the TABLE_PLACEMENT table. These rules determine, for example, table sizes, partitioning threshold values, and preferred partition locations. Redistribution is a two-stage process: firstly, to generate the plan and secondly, to execute the plan. Separate commands are used in each stage:

1. The plan generation command is a multi-purpose tool that requires an algorithm number as a parameter to determine which actions are executed. Depending on the algorithm selected, additional optional parameter values may also be available to give more control over the execution.
2. The plan execution command takes a single parameter which is the numeric plan ID value. You can retrieve this value (REORG_ID) from the REORG_OVERVIEW system view (see System Views following).

For these commands, there is the following syntax:

• CALL REORG_GENERATE(<algorithm integer>, <optional parameter string>);

• CALL REORG_EXECUTE(<plan_id>);

Resource admin privilege is required to call REORG_GENERATE(). The command only operates on tables and partitions that the executing user is allowed to see as catalog objects.

For specific applications, SAP provides recommendations regarding partitioning and table distribution configurations.

Starting with SAP S/4HANA 1610 FPS1, scale-out is supported and can be applied in special scenarios. Application data tables are grouped together according to application area and can be placed as a table group on a specific server. In this way, it is possible to use scale-out in SAP S/4HANA.

For details of scale-out options for SAP S/4HANA, refer to SAP Note: 2408419 — "SAP S/4HANA - Multi-Node Support". This note includes scripts and configuration settings, as well as detailed documentation about table groups and migration.

With the mixed workload on an S/4HANA system, it still makes sense to first scale-up as far as possible, before going into scale-out. So if the first node is bigger than 6 TB and 8 CPU sockets, then scale-out is allowed. See the previous figure for an example.

All the required steps and recommended settings for SAP BW/4 on HANA 2 are described in SAP Note: 2334091 — "BW/4HANA: Table Placement and Landscape Redistribution". This includes a zip file with documentation and SQL code to configure various scenarios covering a range of TABLE_PLACEMENT settings depending on the node size (TB per node) and the number of coordinator and worker nodes.

SAP provides recommendations regarding table distribution configurations. For these scenarios, SQL implementation scripts and detailed documentation is provided in SAP Notes.