With the use of secondary index we can make a program run faster but we are adv

With the use of secondary index we can make a program run
faster but we are advised not to use secondary index why is
that so??

Question Posted / shankar

3 Answers
21641 Views
IBM, iGate, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

With the use of secondary index we can make a program run faster but we are advised not to use seco..

Answer / saifur rahaman

INDEX

An Index is a copy of database table having few numbers of
fields. This copy is always in sorted form. As we know,
Sorted data would always have a speed access from a
database table. Hence, we use an index for the table while
reading the database records. Index also contains a pointer
pointing towards actual database table so that it can
access fields that are not contained in the Index. There
are two kinds of index for a database table:
Primary Index
Secondary Index
Primary Index:

The columns in a table have an 'X' under "KEY" in the
dictionary table/structure (SE11) for a primary index. They
always appear in the initial columns of a table. Or we can
say that primary index consists of a primary key field of a
database table.

Secondary Index:
Secondary Index is the one which we create
separately for the speed access of a database table. Go to
the Transaction SE11- enter the table name (EX: MARA)
and click on Indexes button in the top right corner.

Tables that allow Indexing:

As mentioned earlier in the abstract, not all the
tables allow you to create an Index.
They are allowed only for transparent tables. All
business data and application data are stored in
transparent tables. Ex: VBPA
(Sales Document: Partner), VLCVEHICLE (VELO: Vehicle),
MARA (General Material Data), etc..
Secondary indexes are not allowed for the below
tables but gives a message saying 'Index maintenance only
possible for transparent tables '.
-> Cluster tables. Ex: BSEG
(Accounting Document Segment), KONV (Conditions
(Transaction Data) ), etc..
-> Pooled tables. Ex:
Configuration tables, etc..
Before creating an index:

These are the points to be remembered before
creating an Index.

Create Secondary Indexes for the tables that you mainly
read. Because every time we update a database table, it
would update indexes also. Let's say there is a database
table where we create (or update) 100s of entries in a
single day. Avoid using Indexes in such cases.
We should take care that an index shouldn't have more than
4 fields and also the number of indexes should not exceed 5
for a database table. Or else, it would result in choosing
a wrong one for particular selection by an optimizer.
Place the most selective fields at the beginning of an
Index.
Avoid creating an Index for a field that is not always
filled i.e., if it's value is initial (null) for most
entries in a table.

Options:
Unique Index: If the fields chosen in an index are very
unique i.e., for each combination of fields only one entry
exists, then we chose unique index.
Non-Unique Index: If the above is not true, we choose this
option. We have 3 radio buttons under this called Index on
all database systems, For selected database systems, and No
database index. We can chose any one of them depending on
our requirement.
For client Dependent tables, we always include the
field "MANDT' under all the indexes as you can see in the
previous slide's screenshot. Or else, Optimizer might not
use the Index.
After creating an Index

These are the points to be remembered while coding
in ABAP programs for effective use of Indexes i.e., to
avoid the full table scan.

In the select statement, always put the condition fields in
the same order as you mentioned in the INDEX. Sequence is
very important here.
If possible, try to use positive conditions such as EQ and
LIKE instead of NOT and IN which are negative conditions.
Optimizer might stop working if you use OR condition. Try
to use IN operator instead of that.
The IS NULL operator can cause a problem for the Index as
some of the database systems do not store null values in
the Index structure.

Is This Answer Correct ?

22 Yes

1 No

With the use of secondary index we can make a program run faster but we are advised not to use seco..

Answer / anju

B'coz secondary index is just like a copy of data base
table reduced to specific fields.So,insertion and updation
in database become slow after creation of secondary index
where as read from table become fast.
This is b'coz additional indexes can also place a load on
the system since they must be adjusted each time the table
contents change. Each additional index therefore slows down
the insertion of records in the table

Is This Answer Correct ?

16 Yes

5 No

With the use of secondary index we can make a program run faster but we are advised not to use seco..

Answer / 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)

Is This Answer Correct ?

1 Yes

0 No

Post New Answer