05-02-2006 3:34 PM
I want to know what are disadvantages of having more number of Indexes on a table with million of records. I have a idea updates to the tables gets slow down. Any body have fair and clear picture how this affetcs.
05-02-2006 5:15 PM
hi,
<b>We should not create more secondary indexes as Indexes have to be updated each time the database table is changed.</b>
Few points to be remembered while creating secondary indexes:
So, its better not to create multiple secondary indexes.
As a rule, secondary indexes should not contain more than four fields, and you should not have more than five indexes for a single database table. If a table has more than five indexes, you run the risk of the optimizer choosing the wrong one for a particular operation.
For this reason, you should avoid indexes with overlapping contents. Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column's selectivity.
Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.
Regards,
Sailaja.
05-02-2006 3:37 PM
05-02-2006 3:45 PM
hi ,
one should use the indexes basically to retreive the data faster.
If the no. of indexes are high, then it might affect the performance .
You can have a detailed analysis of this usin SQL Trace . Transaction Code for this is ST05.
You can also do the runtime analysis to check the performance of your code using transaction code SE30.
So based on the results , you can get the clear idea of the table indexes.
Regards,
Kunal.
05-02-2006 3:50 PM
Hi Durgesh,
The only use of index is faster access of data base tables.
Always use all the fields for which the index has been created in <b>where clause in Select statements</b>.
Regards,
Amit Mishra
05-02-2006 4:06 PM
Having too much index on the DB will make it slow the reason being , you know you have DB chunks and as well as Db spaces . When you execute the select statement on the table that have lot of indexes its confuse compiler which combination to be used and if your index have the common fields it try to gather all and then take the results in to buffer. In this way youre wasting Db resources as well as creating hurdle for performance.
Hope thisll give you idea .pl..award the points
Thanks
Saquib khan
05-02-2006 4:16 PM
05-02-2006 5:15 PM
hi,
<b>We should not create more secondary indexes as Indexes have to be updated each time the database table is changed.</b>
Few points to be remembered while creating secondary indexes:
So, its better not to create multiple secondary indexes.
As a rule, secondary indexes should not contain more than four fields, and you should not have more than five indexes for a single database table. If a table has more than five indexes, you run the risk of the optimizer choosing the wrong one for a particular operation.
For this reason, you should avoid indexes with overlapping contents. Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column's selectivity.
Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.
Regards,
Sailaja.
04-24-2007 4:53 PM
hi all.i would like to know more about creating indexes for tables.
this is my select statement below.
-
START OF CODING----
SELECT ntplnr nequnr nmatnr nqmart
nqmnum npriok nrevnr nqmgrp
ysernr ybdmng ypspnr yplnnr y~plnal
dddoknr dddokar dddokvr dddoktl dd~dokob
INTO CORRESPONDING FIELDS OF TABLE gt_itab
FROM viqmel AS n JOIN yepse_tech_obj AS y
ON nequnr = yequnr
AND nproid = ypspnr
LEFT JOIN drad AS dd
ON ddobjky=ypspnr
FOR ALL ENTRIES IN lt_drad
WHERE y~equnr IS NOT NULL
AND y~pspnr = lt_drad-pspnr
AND n~tplnr IN tplnr
AND n~equnr IN equnr
AND n~qmart IN so_qmart
AND n~iwerk IN so_iwerk
AND n~swerk IN so_swerk.
-
END OF CODING----
currently i am trying to do some program enhancement to it as it takes a very long time for the system to run when the 3 tables are joined above.
if the method of table index is faster, how do i go about implementing it to the above code?
thanks.
04-24-2007 5:09 PM
hi,
Just an index does not reduce the execution time.
In the select which you have written, there are few more corrections to be made.
1) corresponding fields.. It deproves performance.
2) do not use inner join and as well for all entries in the same select. It also deproves performance.
Regards
Sailaja.
04-25-2007 4:35 PM
hi sailaja,
from the 2 points that you mentioned, corresponding fields and inner join that will slow down the processing.
is there any other better alternatives?
Thanks.