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??
- 3 Answers
- 21692 Views
- IBM, iGate, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
How to select valid lines for secondary list?
What is a logical database?
Why do we use events and actions in web dynpro?
What is the significance of delivery class? : abap data dictionary
Define subtype ? : abap hr
Which FM do you use to find out who is reporting to whom
Business scenarios related to your objects( In my case -Rulebook and Pricing work flow)
What are the aggregate objects in data dictionary? : sap abap data dictionary
Explain what is sap script? What is the purpose of sap script?
How did you transfer legacy data in the mid of the financial year (us) to the sap system? : sap abap hr
difference between Valuation type and valuation category?
What is the difference between internal tables and extract datasets? : abap modularization
Can we define our own match code id's for sap matchcodes? : abap data dictionary
Explain the relationship between a functional area, user group, and query when developing queries using the sap query tool?
Explain about sap fico testing and the tools used? : abap bdc
