SAP MaxDB Indexes FAQ
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.
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 for each database user or scheme, but not for the same table.
What is the technical structure of indexes?
Indexes, like tables, are implemented as B* trees in SAP 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 database system does not use physical items to identify these references, but instead these are saved in the primary key of the data records. The data record is identified by the primary key (the physical item of this data record is determined via 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.
Is the primary key for SAP 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).
How is data accessed using an index?
If the SQL 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.
How can I display the structure of an index?
In the database assistant (transaction DB50), you can choose Problem analysis -> Indexes to display the CREATE INDEX command with which the index was created.
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 = <owner>
AND schemaname = <schema>
AND tablename = <table_name>
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.
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 = <owner>
AND schemaname = <schema>
AND tablename = <table_name>
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 <index_name> ON <table_name> INIT USAGE
In the database assistant (transaction DB50), you can choose Problem analysis -> Indexes to display the information.
Comment: Even if SAP standard indexes were not previously used, they must 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.
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 SQL optimizer for access, but are still maintained by the database system, this means that 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 statement:
ALTER INDEX <index_name> ON < owner>.<table_name> DISABLE
To activate the index again, use the following SQL statement:
ALTER INDEX <index_name> ON < owner>.<table_name> ENABLE
In SAP WebAS, you can use the database assistant (transaction DB50) to activate or deactivate indexes. To find this function, choose Problem analysis -> Indexes. The buttons there are called "Allow index access" and "Forbid index access".
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.
Are indexes permitted to be created on database system tables?
No. You are not permitted to create indexes on database system tables. The system tables are mostly main memory structures; additional indexes cannot speed up their accesses.
In particular, this refers to the fact that you are not permitted to create ANY indexes in the system tables of the command monitor. Background information PTS: 1240564)
What has to be taken into account with the index design?
Indexes 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 database 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 system table <DATABASE_SYSTEM_ADMINISTRATOR>.OPTIMIZERSTATISTICS, as well as in the database assistant (transaction DB50) -> Problem analysis ->Tables/View/Synonyms -> Optimizer statistics.
How can I create an index?
In SAP WebAS, indexes are initially created in the ABAP Dictionary (transaction SE80 or SE11), activated, and thereby created in the database (transaction SE14).
You can use the SQL studio (for example) to create an index 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 statement for this is:
CREATE [UNIQUE] INDEX <index_name>
ON <owner/schema>.<table_name> (<col1>, <co2>, ...)
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 that are created via database means in the SAP environment (for example, Database Studio, SQL Studio) must then also be created in the SAP ABAP Dictionary (transaction SE11).
What search strategies are there with indexes?
You will find a description of the strategies used by the SQL optimizer under 'Strategy' in the glossary of the SAP MaxDB documentation (Note 767598).
Can indexes fragment in SAP MaxDB?
No. SAP 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.
Can I check individual indexes for consistency?
As of Version 7. 8, individual indexes can be checked for consistency. For more information about the statement CHECK INDEX, see FAQ Note 940420 Database structure check (VERIFY).
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.
Can I create an index on a view?
No. View tables are views on tables. The tables that are involved in view tables are called base tables.
These views on base tables are implemented as SELECT statements on the base tables. Technically, views tables can be compared to saved SELECT statements.
Therefore, no indexes can be created on view tables, but they can be created on the base tables of view tables.
Can the table or indexes become smaller after a data loading process with R3load?
Yes. 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 = '<table_name>'
(Comment: Execute this SELECT statement only 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 load
What are BAD indexes, how do they come about, and how can I eliminate this situation?
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. The database assistant (transaction DB50) and the alert monitor (transaction RZ20) report BAD indexes in the SAP system.
In SAP 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. For this purpose, proceed as follows:
Database Manager GUI -> Recovery -> Index
Database Studio -> Administration
The database studio displays the number of indexes that are set to BAD. The database system administrator (for example, SUPERDBA) can double-click "Indices" to display the affected indexes and they can right-click each of these to recreate them (Recreate Index).
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 started the next time, UNIQUE indexes that are set to BAD are automatically recreated.
As of Version 7. 8, the DBM command auto_recreate_bad_index is available. You can use this command to activate the automatic recreation of indexes (including UNIQUE indexes). For more information, see the SAP MaxDB documentation in Note 767598.
What is a parallel index build?
The data for the index is read in parallel by several server tasks to perform 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.
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 SAP 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
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 statement, the cancel indicator is set for the user task. However, before the task can be re-released, it must clear all index lists that were created before by the server tasks for the index build. This ensures that all of the structures that are no longer required are deleted from the system after you cancel a CREATE INDEX statement. This may take some time depending on the number and size of the index lists.
Can I create several indexes simultaneously?
You can create several indexes simultaneously. However, since only one index build can be carried out in parallel (by several server tasks), we recommend (to speed up the creation of indexes) that you ensure that indexes on large tables are started only 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 statement is already active.
Are locks set during CREATE INDEX?
Yes. Up to and including SAP MaxDB Version 7.6, a lock is set on the relevant tables during the index creation.
As of SAP MaxDB Version 7. 7, the system sets a lock for the entire duration of the index creation only if the following conditions apply:
- if it is a UNIQUE index
- if the transaction that executes the CREATE INDEX statement has already set other locks.
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 statement 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.
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.