FAQ: SAP MaxDB indexes
Symptom
This note consists of questions and answers about indexes in MaxDB/liveCache /content server/SAP DB databases.
General and frequent questions about indexes are answered, although this note does not claim to be exhaustive.
1. What is an index?2. What are indexes used for?3. What is the technical structure of indexes?4. Is the primary key for MaxDB also stored in a separate index-B* tree?5. How is data accessed using an index?6. How can I display the structure of an index?7. How can I view information about the state of indexes?8. Can I deactivate indexes?9. What do I need to consider when I create new indexes?10. What must I take into consideration with regard to index design?11. How can I create an index?12. What search strategies are there with indexes?13. Can indexes fragment in the MaxDB?14. Can I check individual indexes for consistency?15. Can an index become larger than the table to which it belongs?16. Can I create an index on a view?17. Can the table or indexes become smaller after a data loading process with R3load?18. What are BAD indexes, how do they come about, and how can I eliminate them?19. What happens if a UNIQUE index is set to BAD?20. What is the parallel index creation?21. Which phases occur during the index creation?22. Why may it take a long time until a task is available again after you cancel the user task to cancel the creation of an index?23. Can I create several indexes simultaneously?24. Will locks be set with “Create Index”?25. How can I speed up the index build?26. Must I carry out an update statistics for indexes?27. Where can I find more information on indexes?Other terms
FAQ, MaxDB, indexes, index, secondary key, primary key
Reason and Prerequisites
You use the SAP MaxDB database system. 23. Can I create several indexes simultaneously?
Further FAQ notes about MaxDB/liveCache are available on SDN (SAP Developer Network):
https://wiki.sdn.sap.com/wiki/x/GkM
Solution
1. What is an index?
You can create an index (also known as secondary key) to speed up the search for database records in a table. An index is a database object that can be defined for a single column or a sequence of columns of a database table.
In technical terms, indexes are data structures (consisting of one or more inverting lists), which store parts of the data of a table in a separate B* tree structure. This storage sorts the data according to the inverting key fields that were used. Due to this type of storage, the table data can be accessed faster using the indexed columns than without the relevant index.
Indexes, unlike tables, do not include any independent business data, and therefore can always be created again by the table. For example, this is relevant if corruption occurs on the index due to hardware problems.2. What are indexes used for?
Indexes enable faster access to the rows of a table.
You can build indexes for a single column or for a sequence of columns.
The definition of indexes determines whether the column value of different rows in the indexed columns must be unique or not (UNIQUE or NON-UNIQUE index).
An assigned index name and the table names must be unique.
Therefore, there can be several indexes with the same name, but not for the same table, for each database user or scheme.3. What is the technical structure of indexes?
Indexes, like tables, are implemented as B* trees in the MaxDB. These consist of a root node that forms a tree with the subsequent nodes.
The complete index key and the references to the table data are in the lowest level of the index tree, otherwise known as the leaf level. The MaxDB does not use physical items to identify these references, but instead these are saved in the primary key of the data records. You can identify the data record using the primary key. (The physical position of this data record is determined by the converter.)
Since access to the data does not follow the sequence Primary key -> Data, but rather Data -> Primary key, it is also known as an inversion.
The idea behind this is that the relational table design provides for the fact that all data is dependent on a unique primary key.
While the access Primary key -> Data always collects one or no rows, the access Data -> Primary key collects no, one or multiple rows.4. Is the primary key for MaxDB also stored in a separate index-B* tree?
Each database table has a primary key (primary index). The primary key is either defined by the user or generated by the system. A user-defined primary key can consist of one or more columns. The primary key must have a unique value for each row of the table.
The primary key is implemented directly on the data tree, which means there is no separate primary key tree. There is no ROWID or anything similar. The unique identification of a record is the primary key (for multiple keys, this is the combination of fields that are defined as the primary key).5. How is data accessed using an index?
If the MaxDB optimizer evaluates access using the index as the best solution, the primary keys of the table that match the index key fields used are determined in the table tree.
The corresponding rows are read from the table using this list of primary keys.6. How can I display the structure of an index?
You can display the CREATE INDEX command, with which the index is created, using Problem analysis -> Indexes in transaction DB50.
The indexes of a table can be determined using the system table ‘indexcolumns’.
SELECT owner, tablename, indexname, type, columnname,
sort, columnno, datatype, len, createdate
FROM domain.indexcolumns
WHERE owner =
AND schemaname =
AND tablename =
ORDER BY owner, tablename, indexname, columnno
The index structure is relevant for the later analysis of the SQL statement. Using the indexed columns (columnname), you can determine whether an SQL statement can be processed with this index, or if a new index with another index structure should be created.7. How can I view information about the state of indexes?
This information is contained in the system table INDEXES:
SELECT tablename, indexname, index_used, indexusedresetdate, indexusedresettime, filestate , disabled
FROM domain.indexes
WHERE owner =
AND schemaname =
AND tablename =
ORDER BY tablename, indexname
If structural inconsistencies are detected when you access an index, access to this index is locked, and the index is set to BAD (filestate column):
If the index is deactivated, the column DISABLED is set to YES.
If the column INDEX_USED has a value less than 0, the index has already been used to access a table. As of Release 7.6.00 build 19, the columns INDEXUSEDRESETDATE and INDEXUSEDRESETTIME can also be output in the system table INDEXES. They specify when the counter for INDEX_USED was last initialized.
This counter can be initialized with the following SQL statement: ALTER INDEX
In transaction DB50, the information can be displayed using Problem analysis -> Indexes.
Comment: Even if SAP standard indexes were not previously used, they may not be deleted, since inconsistencies between the SAP data dictionary and the database catalog would arise and could lead to problems during an SAP upgrade.
8. Can I deactivate indexes?
Yes. Indexes can be deactivated implicitly by the database system or also explicitly by the administrator. Indexes that are deactivated are no longer used by the optimizer for access, but are still maintained by the database system, that means, each change to the table data is also maintained in the index.
Indexes can be deactivated to exclude indexes (for test purposes) from the optimization of the data access using the following SQL command:
ALTER INDEX
To activate the index again, use the command:
ALTER INDEX
In SAP WebAS, indexes can be deactivated or activated using transaction DB50. To find this function, use transaction DB50 -> Problem analysis -> Indexes. The buttons there are called “Allow index access” and “Forbid index access”.9. What do I need to consider when I create new indexes?
Indexes are additional data structures that must be maintained with each change to the table data. Therefore, the effort involved in a data change (insert, update, delete) in the database increases with the number of indexes in a table. You should therefore ensure that the indexes you create in the customer namespace are actually used by the application also.10. What must I take into consideration with regard to index design?
Indexes of the MaxDB always contain the primary key information.
Therefore, it is normally pointless to include the field MANDT in an index in an SAP system.
In addition, you should also ensure that fields that are not always part of a selection are added to the end of index fields, since subsequent fields can no longer be used for a search.
Example:
SELECT * FROM SFLIGHT WHERE MANDT=’100′ AND SEATSOCC >0;
If an INDEX SFLIGHT~Z01 in the form (MANDT, SEATSOCC, SEATSMAX) now exists in the table SFLIGHT, you can use it.
However, if the sequence is (MANDT, SEATSMAX, SEATSOCC), for example, then the MaxDB cannot skip the information about SEATSMAX, so that only MANDT can be used here for the index access. This results here in a table (range) scan, since MANDT belongs to the primary key.
Furthermore, fields that enable a strict restriction of data volume should be primarily included in indexes.
The average size for this purpose can be the ratio of different values in a field (NUM DISTINCT) to the total number of table rows (NUM ROWS). You can find this data in the view
In SAP WebAS, indexes are initially created in the ABAP/4 dictionary (SE80/SE11), activated and thereby created in the database (SE14).
An index can be created in the SQL studio for test purposes. For this purpose, you must call the point “New” -> “Index Definition” using the context menu for a table in the object tree of the “Catalog Manager”. You can then enter the index names in a dialog box, and select the columns to be indexed from the fields of the table.
The index is created in the database using F8 or by choosing “Execute” (the button with the [!] icon).
The SQL command for this is:
CREATE [UNIQUE] INDEX
If the values to be indexed must be unique, you must specify the option UNIQUE. If the option is omitted, the index then accepts duplicate entries also.
Indexes, which are created by SQL studio in the SAP environment, must then be created in the SAP data dictionary (SE11) also.12. What search strategies are there with indexes?
You will find a description of the strategies used by the MaxDB optimizer under ‘Strategy’ in the glossary of the MaxDB documentation (Note 767598).13. Can indexes fragment in the MaxDB?
No. The MaxDB does not have an index fragmentation problem like that of Oracle (Note 771929). Here, indexes are kept optimal permanently, and storage space is immediately allocated to the freespace again.14. Can I check individual indexes for consistency?
As of Version 7.8, individual indexes can be checked for consistency. For more information about the command CHECK INDEX, see the FAQ Note 940420 Database structure check (VERIFY).
15. Can an index become larger than the table to which it belongs?
Yes. This is possible, for example, if the index is created on a little used field (that is, a field filled with too little data), but the primary key requires too much memory.
The key information is stored in a reduced form at index level in the B* tree of the table, while the secondary index must store each complete primary key.16. Can I create an index on a view?
No. Views are views on tables, and the tables involved together with the views are known as base tables.
These views on base tables are implemented as SELECT statements on the base tables. Technically, views can be compared to saved SELECT statements.
Therefore, no indexes can be created on views, but they can be created on the base tables of views.17. Can the table or indexes become smaller after a data loading process with R3load?
This is possible. For example, if data is inserted in ascending order using R3load, less index or leaf pages must be used than for unsorted data loads. The pages of the index are then filled more.
You can check this as follows:
Select * from tablestatistics where tablename = ‘
(Comment: Only carry out this SELECT for small tables, since the entire B* tree will be analyzed and locked as a result.)
You will find the following values:
‘Rows’ number of data records
‘Leaf pages’ number of data pages used for them
‘Space used in leaf pages (%)’ average load
‘Space used in leaf pages (%) min’ minimum load
‘Space used in leaf pages (%) max’ maximum load18. What are BAD indexes, how do they come about, and how can I eliminate them?
If structural inconsistencies are detected when you access an index, the access to this index will then be locked. The index tree will be set to BAD. The index is no longer maintained by the database system and can no longer be used for the access. This may cause performance problems.
If an index is set to BAD, then a corresponding entry occurs in the files knldiag or knlmsg and knldiag.err or knlmsgarchive. Transactions DB50 and RZ20 report BAD indexes in the SAP system.
In MaxDB Version 7.5, the system table INFO_BAD_INDEXES includes an entry for each BAD index. If a table has no entries, then it has no BAD indexes.
As of MaxDB Version 7.6, the column FILESTATE is also maintained in the system table INDEXES.
Indexes that are set to BAD should be created again when you have minimal work loads to avoid performance problems. This can be done using “DBMGUI -> Recovery -> Index” or “DBStudio -> Administration”.
See also Note: 26837 – MaxDB: Data corruption.19. What happens if a UNIQUE index is set to BAD?
If a UNIQUE index is set to BAD, the corresponding table is set to READ ONLY. This lock is necessary since the UNIQUE option of the index can no longer be ensured for each additional write operation that concerns this index in the table. At the latest when the database is restarted, UNIQUE indexes that are set to BAD are automatically recreated.
As of Version 7.8, the DBMServer command auto_recreate_bad_index is available. You can use this command to activate the automatic recreation of UNIQUE indexes. For more information, see the MaxDB documentation in Note 767598.20. What is the parallel index build?
The data for the index is read in parallel by several server tasks to create the index build as quickly as possible. Only one parallel index build can be carried out at a time – if several CREATE INDEX statements are executed at the same time, these other indexes are then processed by only one server task. This is noticeably slower than a parallel index build. Therefore, you should always ensure that indexes are only created successively.21. Which phases occur during the index creation?
A user task starts the create index. This task activates server tasks that read the data in the tables and create several index lists.
Once these lists are created, the server tasks transfer processing back to the user task. The user task begins to merge the index lists. This is CPU-intensive and may take some time depending on the size and number of lists.
A savepoint completes the creation of an index.
The following entries exist in the knldiag, for example, for MaxDB 7.6:
Create index parallel – final index statistics
+ Perm leaf pages 348 – record count 11411
Create index parallel – start final merge step
Create index parallel – temp pages 42 – temp files 2
Create index parallel – stop final merge step [count 1]
Savepoint (CreateIndex) started by T77
SVP(1) Start Write Data
SVP(1) Stop Data IO, Pages: 2304 IO: 643 643
SVP(2) Wait for last task: 453 453
SVP(2) Stop Wait for last task, Pages: 0 IO: 0
B20PREPARE_SVP: 16116
SVP(3) Start Write Data
SVP(3) Stop Data IO, Pages: 11 IO: 10
SVP(3) Start Write Converter
SVP(3) Stop Converter IO, Pages: 1582 IO: 1582
B20SVP_COMPLETED: 16116
22. Why may it take a long time until a task is available again after you cancel the user task to cancel the creation of an index?
If you cancel a create index command, the cancel indicator is set for the user task. However, before the tasks can be re-released, the task must clear all index lists that were created before by the server tasks for index creation. This ensures that there are no unrequired objects left in the system. This may take some time depending on the number and size of the lists.
You can create several indexes simultaneously. However, since only one index build can be carried out by several server tasks, we recommend (to speed up the creation of indexes) that you ensure that indexes on large tables are only started if no other CREATE INDEX is active, when you are creating several indexes simultaneously. You can create indexes on small tables, even if a CREATE INDEX is active.24. Will locks be set with “Create Index”?
Yes. Up to and including MaxDB Version 7.6, a lock is set on the relevant tables during the index creation.
As of MaxDB Version 7.7, the system only sets a lock for the entire duration of the index creation if the following conditions apply:
- if it is a UNIQUE index
- if the transaction that executes the create index has already set other locks.25. How can I speed up the index build?
You can speed up the index build on large tables by ensuring that only one CREATE INDEX is active, and therefore several server tasks are carrying out the index build.
The data cache should be configured sufficiently large, so that preferably all data for the index build can be loaded in the cache.
26. Must I carry out an update statistics for indexes?
No. You are not required to explicitly create the statistics for the indexes by carrying out an update statistics.
For more information about update statistics, refer to Note 927882.27. Where can I find more information on indexes?
For more information on the B* tree and indexes, see the MaxDB documentation (see Note 767598):
You can access this information using the following keywords in the glossary:
B* tree, Index, Indexed Column, Inversion list and Primary key.
Alternatively, you can use the SDN to access the MaxDB documentation.
https://www.sdn.sap.com/irj/sdn/maxdb
![[del.icio.us]](http://images.del.icio.us/static/img/delicious.small.gif){kind=small}
Leave a Comment