Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 23342 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
What is the syntax for ‘call transaction’?
What is meant by write lock? : sap abap data dictionary
Can you show multiple alvs on a single screen?
Describe data classes? : abap hr
How to creat transactions? : abap data dictionary
What are the aggregate objects in the data dictionary? : abap data dictionary
What is the difference between internal tables and extract datasets? : abap modularization
when are objects are passed from task to the change request?
Does the external program run in the same sap luw as the caller, or in a separate one?
What is the difference between dialog program and a report?
What are the objects of the abap dictionary or what types of objects can be created in the abap dictionary? : abap data dictionary
Write syntax for message error (report)?
pls if any one have idea ,reply as soon as possible 1.Allied solution group inc. is a genuine company or not ? 2.what is update module. 3.Transaction: scc3 what it is?
What are the data classes in abap?
Which transaction code is used executing a report (type 1 program)? : abap data dictionary
