SAP Basis Tech Supports

Symptom
As of Version 7.6, SAP MaxDB provides the option to specify the cluster attribute for tables. This note explains the advantages of table clustering in the OLTP environment and where it can be used. This note deals with questions about table clustering in the database. It provides answers and refers you to other information sources.
The note is in no way complete.
1. What does the cluster table attribute mean?2. Are tables in the OLTP environment provided with the cluster attribute by default?3. Does the ABAP Dictionary (transactions SE11 and SE14) support SAP MaxDB clustering in the OLTP environment?4. What advantages does table clustering in the database provide?5. Are all tables provided with the cluster attribute by default as of SAP MaxDB Version 7.8?6. Which system prerequisites must be met to enable table clustering to be used correctly?7. What do the database parameters DATA_IO_BLOCK_COUNT and DATAIOCLUSTERSIZE do?8. How do I provide a table with the cluster attribute?9. Can I also specify a particular cluster key for the ALTER TABLE CLUSTER command?10. Are indexes that were created for a table also clustered automatically if the table has the cluster attribute?11. What happens when I execute the ALTER TABLE CLUSTER statement for a table?12. Which tasks ensure that the data is stored in the table in clusters?13. How can I see that tasks are involved with clustering a table?14. How do I determine the cluster grade of a table?15. Is there a mechanism that automatically determines a poor cluster grade?16. Which side effects may automatically reclustering a table cause?17. How can I determine whether sufficient free segments exist on the data volume?18. What can I do if too few free segments are available on the data volume?19. Where is clustered data stored on the data volume?20. How do I remove the cluster attribute from a table?21. What do the database parameter settings UseLobClustering=YES and CLUSTERED_LOBS=YES do?22. What happens if I remove the cluster attribute from a table?23. Is the cluster attribute automatically removed from the indexes as well?24. When should I activate table clustering in a system?25. Which tables in the OLTP environment are suitable for database clustering?26. Is there any experience of table clustering in the OLTP environment?27. Where can I find additional information about the cluster table attribute?Other terms
Cluster, performance, I/O bottleneck, FAQ, DATAIOCLUSTERSIZE
Reason and Prerequisites
You use SAP MaxDB Version 7.6.04 with patch level 15 or higher or you use SAP MaxDB Version 7.7.04 or higher.
You use SAP MaxDB in the OLTP environment.
You can use SAP MaxDB clustering in the OLTP environment only after consultation with SAP Support.
Solution

1. What does the cluster table attribute mean?
Tables can be provided with the cluster table attribute. This attribute causes the system to no longer distribute the table contents in individual blocks of 8 KB pages at the same time to all volumes; instead, if possible, the system stores them on the volumes in larger blocks in succession.2. Are tables in the OLTP environment provided with the cluster attribute by default?
No. Up to now, no tables in the OLTP environment were provided with the cluster table attribute by default; as a result, no clustering of the tables was used on the database.
Up to now, the tables were clustered by default only in the BI environment. For more information about table clustering in the BI environment, see Note 1040431.3. Does the ABAP Dictionary (transactions SE11 and SE14) support SAP MaxDB clustering in the OLTP environment?
No. Up to now, there was no connection between the ABAP Dictionary and SAP MaxDB clustering in the OLTP environment. Therefore, table changes that result in a conversion of the table may cause the cluster attribute to be lost.
For this reason, you can activate SAP MaxDB clustering in the OLTP environment only after consultation with SAP Support.4. What advantages does table clustering in the database provide?
The records of a clustered table are sorted in succession on the volume and saved in segments; during reading, they can also be read in succession.
You can use an I/O operation to read blocks that are larger than 8 KB. Experience up to now has demonstrated that I/O times can be greatly improved during sequential reading if you use this option.5. Are all tables provided with the cluster attribute by default as of SAP MaxDB Version 7.8?
No. Even in SAP MaxDB Version 7.8, all tables are not clustered by default when you install or upgrade to Version 7.8.6. Which system prerequisites must be met to enable table clustering to be used correctly?
You must use SAP MaxDB Version 7.6. 05 or higher.
To ensure that the database table data of a clustered table can be stored in succession, you must ensure that enough free space exists in the database to enable a good cluster grade to be obtained.7. What do the database parameters DATA_IO_BLOCK_COUNT and DATAIOCLUSTERSIZE do?
The clustered tables are grouped and saved in segments. The parameters DATA_IO_BLOCK_COUNT and DataIOClusterSize determine this size of the segments (this is typically 64 blocks of 8 KB).
Before you cluster a table, you must check the setting of the database parameter. The parameter should be set to at least 64.8. How do I provide a table with the cluster attribute?
Since there is currently no connection to the ABAP Dictionary, you can set this table attribute only using an ALTER TABLE statement in the SAP MaxDB SQL interface:
ALTER TABLE <table_name> CLUSTER
If a table has LOBs, the cluster attribute affects these LOBs only if the parameters UseLobClustering and CLUSTERED_LOBS (lower than Version 7.7.03) are set to “YES”.9. Can I also specify a particular cluster key for the ALTER TABLE CLUSTER command?
Yes, if a number of column names are specified after the keyword Cluster, then you can replace the previous primary key with the new cluster key.
If no columns are specified after the key word CLUSTER, you cannot change the previous primary key.
ALTER TABLE <table_name> CLUSTER (<column>,…., <column>)
If you change the cluster key, you must delete all of the indexes defined in the table BEFORE you execute the ALTER TABLE command.
The deleted indexes must be recreated after the ALTER TABLE command is complete.
Important:
If you set the cluster key differently than the primary key, the original primary key must then be created as a UNIQUE index in the table.
10. Are indexes that were created for a table also clustered automatically if the table has the cluster attribute?
If a table receives the cluster attribute, only new indexes on the table also receive the cluster attribute.
Existing indexes do not receive the cluster attribute automatically. You must delete and recreate the affected indexes to ensure that index data is also stored in clusters.11. What happens when I execute the ALTER TABLE CLUSTER statement for a table?
In the database catalog (system table FILES), the relevant table receives the cluster table attribute. At this point, the system reads all of the table data into the cache and stores it in segments on the data volumes at the next save point.
Remark: The runtime of the alter table cluster command depends on the size of the table. As long as the alter table command is active, the table is locked for all accesses from the application in the available MaxDB versions (PTS:1174872 and PTS:1174879).
Comment: If you execute the ALTER TABLE CLUSTER statement again, the system starts to read the data to the cache again.12. Which tasks ensure that the data is stored in the table in clusters?
At a save point, the system determines whether data is to be stored in clusters. As a result, data from a clustered table that is blocked by the pager tasks is written to the data volume at the save point.13. How can I see that tasks are involved with clustering a table?
Since writing the data takes place as part of a ‘normal’ save point, you cannot actually determine whether data is currently stored in segments.14. How do I determine the cluster grade of a table?
You can use the database assistant (transaction DB50) to determine the quality of the clustering. The database assistant is available as of the following Basis Support Packages:SAP 7.10 Support Package 2SAP 7.00 Support Package 13SAP 6.40 Support Package 21SAP 6.20 Support Package 63SAP 4.6C Support Package 55
In the database assistant, choose “Problem analysis -> Tables/View/Synonyms -> Data Storage”.
See the “FACTOR” column. The closer the factor is to the value of DATA_IO_BLOCK_COUNT, the better the clustering is. A value of one indicates that the table is not stored in clusters.
You can also use SELECT statements on the system tables TABLESTORAGEDETAILS and INDEXSTORAGEDETAILS to evaluate the cluster grade. For more information, see Note 1040431.15. Is there a mechanism that automatically determines a poor cluster grade?
When the system reads a clustered table, it checks the cluster grade of the area that it read. If it determines that the cluster grade is poor, it marks the pages as changed and writes them in segments to the data volumes at the next save point (reclustering).16. Which side effects may automatically reclustering a table cause?
A reclustering can take place successfully only if sufficient free segments exist on the data volume. Even if this is not the case, the system still marks the data as changed; this causes an unnecessary write load.17. How can I determine whether sufficient free segments exist on the data volume?
You can use the following SQL statement to determine the number of free segments for a segment size of 64 blocks (database parameters DATA_IO_BLOCK_COUNT and DATAIOCLUSTERSIZE):
SAP MaxDB Version 7.6:
SELECT sum(sections) FROM “SYSDD”.”FREEBLOCKMANAGERSECTIONS”
WHERE freeblockcount = 64
SAP MaxDB Version 7.7:
SELECT count(*) FROM “SYSINFO”.”FREEBLOCKMANAGERSECTIONS”
WHERE freeblockcount = 6418. What can I do if too few free segments are available on the data volume?
After each save point, the system checks to ensure that the fill level of a segment remains below a specified threshold value.
You can use the database parameter CLUSTERCOMPRESSIONFILLTHRESHOLD to configure this threshold value. The default value is 10%.
If the threshold value is exceeded, the system loads the used data blocks into the cache and moves them to a different location at the next save point. The system uses this mechanism to attempt to provide additional free segments.
If you increase the threshold value, the system adds additional segments to this mechanism. This increases the I/O load of the system.
If sufficient free segments are available, you must reduce the threshold value again. Since the database parameter can be changed in the operational system (operational state: ONLINE), you do not have to schedule any database downtime for this.
However, if the fill level of the data volume is too high, this mechanism cannot provide any additional free segments. In this situation, the only option is to add new data volumes.19. Where is clustered data stored on the data volume?
On the data volumes, there are no places reserved for storing clustered data. Segments are distributed between all data volumes and are stored along with data that is not clustered.20. How do I remove the cluster attribute from a table?
Since there is currently no connection to the ABAP Dictionary, you can remove this table attribute only using an ALTER TABLE statement in the SAP MaxDB SQL interface:
ALTER TABLE <table_name> NOT CLUSTER
If a table has LOBs, the system stores these in clusters only if the database parameter UseLobClustering is set to “YES” (default value).21. What do the database parameter settings UseLobClustering=YES and CLUSTERED_LOBS=YES do?
If UseLobClustering is set to “YES”, this affects all LOBs in the database, regardless of whether the basis table of the LOB has the cluster attribute.
In Version 7. 6, the default value for this database parameter is “NO”.
As of Version 7. 7, you must not change the default value of “YES” for UseLobClustering because LOBs are always large, database-internal files with few change operations.22. What happens if I remove the cluster attribute from a table?
If you use ALTER TABLE NOT CLUSTER to remove the cluster attribute, the physical storage in the B* trees remains as before. However, the system no longer reads clusters; instead, it reads only individual records (8 KB).23. Is the cluster attribute automatically removed from the indexes as well?
When you set or remove the cluster attributes of tables, the system does not pass this on to the existing indexes; as a result, you must delete and recreate the index to remove the cluster attribute from the index.24. When should I activate table clustering in a system?
In the OLTP environment, you should activate table clustering only if SAP Support recommends this for certain tables.25. Which tables in the OLTP environment are suitable for database clustering?
Large tables are suitable for clustering if the system mainly uses ranges to access the table and if the tables are changed infrequently.
The following tables are NOT suitable for clustering:Small tablesTables that are updated frequently.26. Is there any experience of table clustering in the OLTP environment?
Table clustering is used in the BW environment by default for fact tables.
In the OLTP environment, only limited experience exists in the production system up to now.
However, table clustering has demonstrated a significant improvement in the I/O scan performance.27. Where can I find additional information about the cluster table attribute?
SAP employees can find additional information about SAP MaxDB clustering at:
https://wiki.wdf.sap.corp/x/MIKtEw.