SAP Note 912620 Note Language: English
FAQ: Or Oracle in indexes Version: 11 Validity:
Valid Since 18.12.2008
Summary Symptom 1.
What are indexes?
2.
What hat ar are in indexe dexes s use used d fo for?
3.
What What is the the tec techn hnic ical al stru struct ctur ure e of inde indexe xes? s?
4.
How How is is dat data a acc acces esse sed d usi using ng an inde index? x?
5.
How can I creat reate e an inde index? x?
6.
What What di disad sadvan vantag tages es are are asso associa ciated ted with with ad addit dition ional al inde indexes xes? ?
7.
What What inde index x acc acces esse ses s are are ther there? e?
8.
What What type types s o of f i ind ndex exes es are are t the here re? ?
9.
What What mu must st I take take into into co consi nsider derati ation on with with regard regard to inde index x de desig sign? n?
10. 10.
Can I deac deactiv tivate ate inde indexes xes temp tempora oraril rily? y?
11. 11.
Is the the use of of index index comp compres ressio sion n permit permitted ted? ?
12.
How can fragment fragmented ed indexes indexes be defragme defragmented nted? ?
13. 13.
How can can I deter determin mine e index index fragm fragment entati ation? on?
14.
How can can I determ determine ine whethe whether r an index index is is actuall actually y being being used? used?
15.
What factors factors deter determine mine the costs costs for an an index index access access? ?
16.
Should Should i index ndexes es a alway lways s begin begin with the client client c colum olumn? n?
17.
Where Where do I find find f furth urther er i infor nformati mation on a about bout indexes? indexes?
Other terms FAQ
Reason and Prerequisites Solution 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.
08.11.2010
P ag e 1 o f
9
SAP Note 912620 -
2.
FAQ: Oracle indexes
What are indexes used for? 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: o
Root block: 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.
o
Branch blocks: 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.
o
Leaf blocks: 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 analyze 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. See also Notes 806554 and 334224 regarding this topic.
08.11.2010
Page 2 of
9
SAP Note 912620 -
6.
FAQ: Oracle indexes
What disadvantages are associated with additional indexes? Before you decide to create a new index, you should be aware of the following consequences: o
Space required on the hard disk Each index requires space and therefore adds to the growth of the database.
o
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.
o
DML performance Each additional index increases the runtime of INSERT, UPDATED and DELETE operations.
o
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: o
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.
o
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.
o
Index Full Scan
08.11.2010
Page 3 of
9
SAP Note 912620 -
FAQ: Oracle indexes
All the leaf blocks of the index are scanned in the logical tree structure. o
Index Fast Full Scan All the blocks of the index are scanned in their physical sequence.
o
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: o
B*Tree indexes The SAP environment mainly uses B*Tree indexes, in which the values of the indexed columns are stored in the leaf blocks.
o
Bitmap indexes Bitmap indexes are also used, above all in the BW environment (for example, on the characteristic columns of InfoCubes). 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.
o
Reverse key indexes In exceptional cases, it may make sense to use reverse key indexes, in which all column values are stored in reverse. For more information, see Note 915242.
o
Unique indexes 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").
o
Non-unique indexes Non-unique indexes are indexes that can contain the same combination of values several times.
o
Function-based indexes
08.11.2010
Page 4 of
9
SAP Note 912620 -
FAQ: Oracle indexes
Indexes that do not index column values themselves but rather functions of column values (for example, HEXTORAW()) are known as function-based indexes. o
Index organized tables Index organized tables are tables that are constructed in the form of an index. For more information, see Note 641435.
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: o
Create as few indexes as possible.
o
Check whether an index that already exists can be used effectively by changing the application design.
o
If possible, do not create an index that is very similar to an index that already exists.
o
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 selectivities. 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: o
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 behavior 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.
o
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.
o
If an index field is not specified with "=" or "IN", the subsequent fields can be analyzed 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
08.11.2010
Page 5 of
9
SAP Note 912620 -
FAQ: Oracle indexes
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 analyzed 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.
10.
o
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.
o
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).
o
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.
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). For more information, see Note 766349.
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, see Note 1109743.
12.
How can fragmented indexes be defragmented? For more information, see Note 771929.
13.
How can I determine index fragmentation? For more information, see Note 771929.
14.
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: o
Index-monitoring As of Oracle 9i, you can use the statement ALTER INDEX MONITORING USAGE;
08.11.2010
Page 6 of
9
SAP Note 912620 -
FAQ: Oracle indexes
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 NOMONITORING USAGE; o
Segment statistics 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: SELECT SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME, SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME, VALUE "NUMBER" FROM V$SEGMENT_STATISTICS WHERE OBJECT_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.
o
Information from V$SQL_PLAN 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: SELECT SUBSTR(OBJECT_OWNER, 1, 10) OWNER, SUBSTR(OBJECT_NAME, 1, 25) OBJECT, SUBSTR(OPERATION, 1, 15) OPERATION, SUBSTR(OPTIONS, 1, 15) OPTIONS, COUNT(*) "NUMBER" FROM V$SQL_PLAN WHERE OBJECT_NAME = '' GROUP BY OBJECT_OWNER, OBJECT_NAME, OPERATION, OPTIONS;
15.
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 (Note 832343) of the index is decisive in determining the number of table blocks to be read in an index range scan.
08.11.2010
Page 7 of
9
SAP Note 912620 -
FAQ: Oracle indexes
See also Note 750631, which contains information about calculating the costs of index accesses. 16.
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: o
There are selections that can be completely covered by the index if the index includes the client, which makes a processing-intensive table search unnecessary.
o
The client is used in a unique index to guarantee application consistency.
o
A short-term work-around is required for the CBO (Cost-Based Optimizer) problem described in Note 176754 (18).
o
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. 17.
Where do I find further information about indexes? Detailed information about indexes is contained in the Oracle documentation under Oracle Database Concepts -> Schema Objects -> Indexes
Header Data Release Status: Released on: Master Language: Priority: Category: Primary Component:
Released for Customer 18.12.2008 09:41:41 German Recommendations/additional info FAQ BC-DB-ORA Oracle
The Note is release-independent
08.11.2010
Page 8 of
9
SAP Note 912620 -
FAQ: Oracle indexes
Related Notes Number
Short Text
1109743
Use of Index Key Compression for Oracle Databases
915242
FAQ: Reverse key indexes
832343
FAQ: Clustering factor
806554
FAQ: I/O-intensive database operations
771929
FAQ: Index fragmentation
766349
FAQ: Oracle SQL optimization
750631
Approximations for cost calculation of the CBO
641435
FAQ: Oracle Index Organized Tables (IOTs)
334224
Important notes for creating indexes
105047
Support for Oracle functions in the SAP environment
08.11.2010
Page 9 of
9
