Answer Posted / babu

An index helps to speed up selection from the database. An
index is a sorted copy of selected database table fields.

Primary index:

When you activate the table (in Oracle, Informix, and DB2)
in the database, an

index that is formed from all the key fields is created in
addition to the table itself.

This index is called the primary index. It is unique by
definition.

If the primary index cannot be used to determine selection
result, (for example, the WHERE condition does not contain
any primary index fields), the system searches the whole
table. To prevent this, and determine the selection result
by searching through a restricted number of database
records, you can create a secondary index.

In addition to the primary index, you can define one or more
secondary indexes for a table in the ABAP Dictionary and
save them in the database. Secondary indexes can be

either unique or non-unique.

When to create an index

It is worth creating a secondary index when:

You want to select table entries based on fields that
are not contained in an index, and the response times are
very slow.
The EXPLAIN function in the SQL trace shows which index
the system is using. You can generate a list of the database
queries involved in an action by entering Transaction ST05
and choosing Trace on → Execute action → Trace off → List
trace. If you execute the EXPLAIN SQL function on a EXEC,
REEXEC, OPEN, REOPEN or PREPARE statement, the system
returns a list containing the index used in the database query.
The field or fields of the new secondary index are so
selective that each index entry corresponds to at most 5% of
the total number of table entries. Otherwise, it is not
worth creating the index.
The database table is accessed mainly for reading entries.

The Database Optimizer

The database optimizer is a database function that analyzes
SQL statements and

defines an access strategy. It is the database optimizer
that determines whether

one of the existing indexes will be used, and if so, which one.



A rule-based optimizer analyzes the structure of an SQL
statement (mainly the

SELECT and WHERE conditions without values), and the index
of the table(s)

involved. It then uses a set of analysis rules to decide the
most appropriate

procedure for executing the statement.

A cost-based optimizer in addition analyzes some of the
values in the WHERE

condition and the table statistics. The statistics contain
low and high values for the

fields, or, in some cases, a histogram of the distribution
of the data in the table.

ORACLE databases up to and including Release 7.1 use a
rule-based

optimizer in SAP systems with R/3 Release 3.1 and earlier.
From Oracle release

7.2 (R/3 Release 4.0), they use a cost-based optimizer. All
other database systems

use a cost-based optimizer.





The index search string:

The index search string is formed from the WHERE condition.
To do so, the

desired values of the fields contained in the index are
concatenated. To minimize

the number of index blocks that have to be checked, the
index search string should

be specified from the left without placeholders (._. or .%.)
wherever possible.

Because the index is saved sorted by the index fields, a
contiguous range of index

records can be checked, and only a few index blocks have to
be read.



Problematic Statements



1. Negations in the WHERE Condition

Avoid use of NOT. Use positive formulations instead.

Negations in the WHERE condition (NOT operator) will usually
prevent the

optimizer from considering the field in question when
searching for a suitable

index. If this makes it impossible to find a suitable search
range, determining the

corresponding hit-list can become very processing-intensive,
resulting in long

runtimes.

Therefore, you should avoid negations of potential index
fields whenever

possible.

In contrast, negations involving fields that are not
contained in the index do not

pose a problem. They are only used to reduce the number of
hits anyway.



2. Intervals in the WHERE Condition (using operators)

The quality of operators used:

=

IN

BETWEEN

LIKE

NOT



3. SELECT-OPTIONS and RANGES Tables

These two statements implicitly declare an internal table
with the following fields: SIGN, OPTION, LOW, and HIGH.

RANGES tables that are declared with SELECT-OPTIONS are
usually filled

dynamically on screen by the end user, while RANGES tables
that are declared

with RANGES are filled by the program. The database
interface translates the

individual rows in a RANGES table into a format that the
DBMS can interpret

and joins them with OR. The created SQL statement is then
passed on to the

database. The RANGES table can contain rows with complex
expressions (BT

= BETWEEN, CP = LIKE, and so on). Accordingly, the SQL
statement that

results from a ranges table can be extremely complex and
difficult for the DBMS

to process. If you have a program fill the RANGES tables,
you have to make

sure that the number of items is limited. If the RANGES
table is too large, the

analysis of the OR or IN list will result in a complex
database statement that

will be extremely expensive for the database to process.



4. Inner ORs

Avoid inner ORs. Formulate with IN instead

You should replace .inner. OR conditions with IN conditions
whenever possible.

In general, .outer. OR conditions . those outside the
parenthesis of the WHERE

condition . are better than complex clauses with inner ORs.



5. Access with the ORDER BY Addition

If you want an SQL statement to return a sorted result, you
can either use an

ORDER BY clause to do so in the database, or use the ABAP
SORT statement

for an internal table.

SORT statement or ORDER BY PRIMARY KEY are preferred.





Rules for creating indexes

· Few indexes per table (4-7)

· Fewer short fields in the index

· Selective fields in the front

· Disjunctive indexes

· No indexes of transaction data

· Change existing indexes before you build new ones

· Under certain circumstances, it can even be
advantageous to delete indexes

· Do not change any SAP indexes or SAP tables
(unless instructed to do so by SAP, refer the SAP notes)





Forcing an index:

SAP chooses whether to use a particular index or not (based
on the performance)

We can create an index and see if SAP is choosing it while
executing the select statement.

However, if SAP does not choose to use the index, we can
force the program to use the index by using the statement:

%_HINTS ORACLE 'INDEX("BSAK" "BSAK~1")'

(For Example if the name of the index is 1 and name of
the table is BSAK)

What will you code in start-of-selection & end-of-selection ?

586

---

Did you use buffering?

590

---

In BDC, data transfer is done success in foreground mode but if we done the same in background mode it shows errors. What is the reason?

976

---

foreign key enforcement?

1620

---

What is a modification assistant?

583

---

---

what next in SAP-ABAP/4 is it going to be change?

1646

---

Which FM do you use to find out who is reporting to whom

5167

---

Tell me a scenario where did you use secondary index?

625

---

What is the difference between a substructure and an append structure? : abap data dictionary

642

---

hi,how to diable the popup window which is displayed afetr executing the SAP script program?

1571

---

What does hide statement do?

563

---

Lock objects and what are parameters of ENQUEUE FM?

1240

---

What is transparent table?

594

---

Explain the difference between function group and function module?

560

---

Explain get pernr concept when we use logical data base? : abap hr

744

---