1/8/2016 11:21:48 AM
1. What are indexes?
Indexes are Oracle segments that are organized in a logical tree structure. They contain column values of a table in a sorted sequence. Each table entry is also taken into account in the index - unless all the indexed columns of a table entry have the value NULL. In this case, the entry is not included in the index.
The most important reason for using indexes is to allow quicker access to table data. Instead of having to read the complete table, the system can use an appropriate index to quickly find the table entries that correspond to the conditions.
In addition to this, you can create indexes to avoid duplicate key combinations. To do this, indexes must be defined as UNIQUE. The SAP primary indexes are a typical example.
3. What is the technical structure of indexes?
Indexes consist of the following three block types:
The root block forms the access to the index. There is just one root block. It contains navigation information and can have various successive blocks at the next index level.
Below the root block, there may be one or more levels containing branch blocks, depending on the size of the index. The branch blocks also contain navigation information and can have various successive blocks at the next index levels.
The blocks at the last index level are known as leaf blocks. These contain the actual index data and are linked to one another by a pointer. They also contain a pointer to the relevant table entry in each case in the form of the ROWID.
4. How is data accessed using an index?
Data is always accessed using the root block. Depending on the navigation information and the selection conditions, the system then reads a branch block at each subsequent level until it finally reaches the first leaf block that is required to analyse the selection conditions. If the system finds suitable data records, it uses the ROWID reference to read the (non-indexed) remainder of the data record from the table as required. If necessary, it navigates using the leaf block pointer to the next leaf blocks in order to read additional relevant data records in the same way.
5. How can I create an index?
Indexes should generally be created using SAP tools (in other words, transactions SE11 and SE14, transports) in the SAP environment.
If required, you can also create an index at Oracle level using the command CREATE INDEX. Here, additional options such as PARALLEL, NOLOGGING or ONLINE are possible.
6. What disadvantages are associated with additional indexes?
Before you decide to create a new index, you should be aware of the following consequences:
Space required on the hard disk
Each index requires space and therefore adds to the growth of the database.
Space required in the buffer pool
Each index occupies blocks in the buffer pool when it is used. As a result, blocks of other segments are supplanted quicker and the performance of the global database may suffer.
Each additional index increases the runtime of INSERT, UPDATED and DELETE operations.
Mistakes in CBO
Each additional index increases the risk of mistakes in the CBO, which can in turn cause lengthy SQL statements. You should therefore avoid creating many similar indexes.
In spite of these restrictions, creating an index to tune an expensive SQL statement is in many cases a logical (temporary or permanent) technical solution in which the advantages clearly outweigh the disadvantages.
Wherever possible, you should check the consequences of a new index in a test system before you create the index in a productive environment.
If a new index causes problems, you can quickly delete it using DROP INDEX.
7. What index accesses are there?
The following index accesses are used:
Index Unique Scan
An index unique scan always returns a maximum of one data record. It is used if all the columns of a unique index are specified with "=" in the selection conditions.
Index Range Scan
In an index range scan, the system scans a subset of the leaf blocks and can return as many data records as required. Almost all primary key accesses that have not been completely qualified are processed in an index range scan.
All the leaf blocks of the index are scanned in the logical tree structure.
Index Fast Full Scan
All the blocks of the index are scanned in their physical sequence.
Index Skip Scan
An index skip scan may be useful if indexed columns are not specified or if they are only specified with a range condition. In this case, the CBO may opt for an index skip scan, which is technically comparable with several index range scans.
8. What types of indexes are there?
The most important index types are:
The SAP environment mainly uses B*Tree indexes, in which the values of the indexed columns are stored in the leaf blocks.
Bitmap indexes are also used, above all in the BW environment (for example, on the characteristic columns of Info Cubes). Instead of the column values, bitmaps that specify which column values are located under which ROWIDs are stored in the leaf blocks.
The advantages of bitmap indexes are that fast bitmap operations are possible when the table data is accessed (in STAR transformations, for instance) and that the indexes are often smaller. Since a bitmap is created for each column value, bitmap indexes are particularly suitable for columns that have a low cardinality.
In exceptional cases, it may make sense to use reverse key indexes, in which all column values are stored in reverse.
Unique indexes are indexes in which each combination of values may only occur once. If you attempt to add a combination of values that already exists, Oracle returns the error ORA-00001 ("unique constraint violated").
Non-unique indexes are indexes that can contain the same combination of values several times.
Indexes that do not index column values themselves but rather functions of column values (for example, HEXTORAW(<column>)) are known as function-based indexes.
Index organized tables are tables that are constructed in the form of an index
9. What must be considered in terms of the index design?
Each additional index means an additional time-effort when you make changes to the table, additional demands on hard disk and memory resources and potential mistakes in the CBO. Therefore, take account of the following general rules:
Create as few indexes as possible.
Check whether an index that already exists can be used effectively by changing the application design.
If possible, do not create an index that is very similar to an index that already exists.
Only change the standard indexes after consultation with, or by request from, SAP.
In order to determine the optimal index for an actual statement, the typical values must be known in the WHERE condition and its selectivity.
You can determine how suitable an index is for a request by comparing it with a typical telephone directory, whose entries are sorted according to locality, last name and first name. In a manner of speaking, a telephone directory is an index on the columns for place, surname and first name. Note the following rules:
Although unselective fields (such as MANDT) at the beginning of an index increase the index, they do not influence the performance significantly. This is exactly the same behaviour in the telephone directory: If the telephone directory only contains one (or a small number of) locations, you will find a specific person in a location just as quickly as if the telephone directory contained lots of locations.
If a field that is not specified in the conditions appears in the index, all the subsequent index fields are virtually worthless. You can compare this with a telephone directory where you are looking for a last name without knowing the locality. This effort would only be justifiable if there were only a small number of localities, for which you want to search. This would amount to an INDEX SKIP SCAN, which should not normally be used.
If an index field is not specified with "=" or "IN", the subsequent fields can be analysed only in a limited way. You can easily reproduce this, for example, by looking for the telephone number of Peter M. in a telephone directory (last name like 'M%' and first name is 'Peter'). In this case, Oracle always reads the entire index area belonging to the condition that was not specified with "=". No other conditions are analysed as part of this INDEX RANGE SCAN. It is therefore important to include range fields as near to the end of an index as possible.
If several columns to be indexed are specified with "=" in the selection conditions, the sequence does not affect the performance of the access. In this case, you should place at the beginning the fields that you expect will be used for access in other places.
The question of how selective a column should be before you include it in an index cannot be answered in general terms. A column with a comparatively low selectivity of 50% is advisable if it reduces the dataset from 10,000 to 5,000 data records (thus saving 5,000 data records). On the other hand, a column with a selectivity of 10% only offers a slight advantage in the index if it reduces the dataset from 10 to 1 (saving 9 data records).
All in all, a new index should therefore firstly contain sufficiently selective columns with "=" and IN" conditions, and then sufficiently selective columns with range conditions.
10. Can I deactivate indexes temporarily?
It is not technically possible to deactivate indexes temporarily. If you want to avoid using an index, you must drop it or implement another technical solution (using a hint, adjusting the query, adjusting the statistics).
11. Is the use of index compression permitted?
If index compression is activated, each index key must be stored only once - regardless of how often it actually occurs. This can significantly reduce the size of the index in the case of indexed columns that have a small number of occurrences. For more information.
12. How can I determine whether an index is actually being used?
Up to Oracle 8i (inclusive) it was difficult to find information about the use of indexes. It was possible to implement a manual analysis of SQL statements in the Oracle Shared Cursor Cache. However, as of Oracle 9i, more elegant methods are available:
As of Oracle 9i, you can use the statement
ALTER INDEX <index_name> MONITORING USAGE;
To activate index usage monitoring. In the USED column of the V$OBJECT_USAGE view, you can check whether the index has recently been used (USED = 'YES'). Note that access to V$OBJECT_USAGE only returns information for objects of the user who has just logged on. The data is retained after a system restart. To reset the monitoring for an index (USED = 'NO'), execute ALTER INDEX MONITORING USAGE again. Use the following command to deactivate the monitoring process:
ALTER INDEX <index_name> NOMONITORING USAGE;
As of Oracle 9i, statistical data is available in the view V$SEGMENT_STATISTICS, describing how many "Disk Reads" and "Buffer Gets" were needed for accessing individual segments since the last database start. The following query returns the respective number of block accesses for an actual index:
SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME,
SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,
OBJECT_NAME = '<index_name>' AND
STATISTIC_NAME IN ('logical reads', 'physical reads');
In general: the smaller the number of block accesses, the less the index is required. However, consider that block accesses occur on each index belonging to a table because of INSERT, UPDATE and DELETE operations, even if the index is not required for actual data access.
You can use the following query in Oracle 9i to determine whether and how many accesses (and of which type) have been carried out using a specific index since the last database start:
SUBSTR(OBJECT_NAME, 1, 25) OBJECT,
SUBSTR(OPERATION, 1, 15) OPERATION,
SUBSTR(OPTIONS, 1, 15) OPTIONS,
WHERE OBJECT_NAME = '<index_name>'
GROUP BY OBJECT_OWNER, OBJECT_NAME, OPERATION, OPTIONS;
13. What factors determine the costs for an index access?
The costs for an index access basically correspond to the number of leaf blocks to be read and the number of table blocks to be read using the ROWID pointer. The clustering factor of the index is decisive in determining the number of table blocks to be read in an index range scan.
14. Should indexes always begin with the client column?
For historical reasons, many SAP indexes begin with the client column (MANDT, CLIENT, MANDANT, ...). In many cases, this column only increases overhead by increasing the size of the index. The client column is only useful or advantageous in a limited number of situations:
The client is used in a unique index to guarantee application consistency.
The table contains several filled clients. Therefore, the client condition is selective in this context.
In most cases, it is not necessary to include the client in the index.
If you like this blog, please share (Facebook/LinkedIn/Google+) to click below links so it will reach to others.