topic Re: Table Index in Application Development and Automation Discussions

Table Index

Former Member — Tue, 02 May 2006 14:34:27 GMT

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.

Re: Table Index

Former Member — Tue, 02 May 2006 14:37:08 GMT

While doing select DB will confuse which one to use.

Regards

vijay

Re: Table Index

Former Member — Tue, 02 May 2006 14:45:28 GMT

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.

Re: Table Index

Former Member — Tue, 02 May 2006 14:50:14 GMT

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

Re: Table Index

former_member181966 — Tue, 02 May 2006 15:06:21 GMT

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 it’s 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 you’re wasting Db resources as well as creating hurdle for performance.

Hope this’ll give you idea .pl..award the points

Thanks

Saquib khan

Re: Table Index

Former Member — Tue, 02 May 2006 15:16:25 GMT

Is this a Z table or an SAP table?

Rob

Re: Table Index

Former Member — Tue, 02 May 2006 16:15:17 GMT

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.

Re: Table Index

Former Member — Tue, 24 Apr 2007 15:53:22 GMT

hi all.i would like to know more about creating indexes for tables.

this is my select statement below.

START OF CODING----

SELECT n_{tplnr n}equnr n_{matnr n}qmart

n_{qmnum n}priok n_{revnr n}qmgrp

y_{sernr y}bdmng y_{pspnr y}plnnr y~plnal

dd_{doknr dd}dokar dd_{dokvr dd}doktl dd~dokob

INTO CORRESPONDING FIELDS OF TABLE gt_itab

FROM viqmel AS n JOIN yepse_tech_obj AS y

ON n_{equnr = y}equnr

AND n_{proid = y}pspnr

LEFT JOIN drad AS dd

ON dd_objky=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.

Re: Table Index

Former Member — Tue, 24 Apr 2007 16:09:05 GMT

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.

Re: Table Index

Former Member — Wed, 25 Apr 2007 15:35:36 GMT

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.