If fields in the WHERE clause are specified with operators NOT or <>, these WHERE conditions cannot be used for a search over a database index. You should therefore formulate SQL statements positively wherever possible.
If a positive formulation cannot be used, for example because the IN list would be too long, you should still specify the WHERE condition with NOT, in order to reduce the amount of data to be transferred. An index search will not be performed, but the amount of data returned will be smaller.
On ORACLE databases, a WHERE condition with BETWEEN is evaluated together with the costs for reading 5 % of all index blocks and table blocks, regardless of the size of the BETWEEN interval. If the BETWEEN interval is small enough, you can replace SELECT field list WHERE field BETWEEN value1 AND value5 by SELECT field list WHERE field IN (value1, value2, value3, value4, value5) or by SELECT field list WHERE field IN ranges table.
On ORACLE databases, a WHERE condition with LIKE, > or < field =" ‘999’.">
A field specified with LIKE can narrow the selection of database indexes and be used for a search over a database index only if it does not begin with the wildcard character ‘_’ or ‘%’. In ABAP, the wildcard ‘+’ is used for any character, and ‘*’ is used for any character string. For database accesses, however, the characters ‘_’ and ‘%’ are used.
WHERE conditions with critical operators that are linked to the WHERE clause with OR can be particularly problematic. Since BETWEEN is calculated as costing 5% and LIKE is calculated as costing 10%, the cost-based database optimizer opts for a full table scan after a certain number of corresponding WHERE conditions, instead of using an index.
If you want the result of an SQL statement to be returned in a sorted order, use an ORDER BY clause on the database, or use the ABAP statement SORT itab in the program.
Since the database is a central resource and is therefore not scalable, sort in ABAP wherever this is possible. However, if the same database index can be used for sorting and selecting data records (according to the WHERE clause), you can also sort on the database.
If you establish that an expensive SQL statement is triggered by an SAP program, first search for R/3 Notes in SAPNet (in a full text search, specify the name of the table or view, as well as the word 'performance'). If you cannot find an applicable R/3 Note, open a problem message in SAPNet.
In your own programs, if you find expensive SQL statements on SAP standard tables with transaction data, avoid creating secondary indexes wherever possible. Transaction data tables usually grow linearly with their indexes. Therefore, a search using a secondary index becomes less effective as time goes on. You should look for alternative methods of access, for example, using index tables or matchcode tables.
Before you create a secondary index to optimize an SQL statement, check whether you can adapt the SQL statement to use a secondary index that already exists. Before you change an index, you must perform a selectivity and interference analysis.
In some cases, deleting a secondary index makes sense. For example, if a secondary index is not used, or if a secondary index is a subset of another index, updating it causes unnecessary work for the database. Before you delete an index, you must perform an interference analysis.
RELATED POST
No comments:
Post a Comment