topic Re: Index in Application Development and Automation Discussions

Index

Former Member — Thu, 26 Apr 2007 08:15:11 GMT

Hi ,

How many Primary ,Secondary index can be there for a table ?

Re: Index

Former Member — Thu, 26 Apr 2007 08:16:14 GMT

Hi kumar,

1. Primary can only be one.

2. Secondary can be many, depending upon the database limits.

regards,

amit m.

Re: Index

Former Member — Thu, 26 Apr 2007 08:17:53 GMT

The Number of primary key columns per table is 512.

Check this link for more info:

http://www.sapdb.org/htmhelp/32/60a014540911d5a9fd00a0c9449261/frameset.htm

Regards,

Ravi

Re: Index

Former Member — Thu, 26 Apr 2007 08:20:28 GMT

Hi Kumar,

Go through this info.

Disadvantages:

Basically index are provided to improver performance. i.e with index select on db tables retrieves data much faster.

If you are writting select statement on the table where in where clause you have non-primary keys then it takes longer to retriev data from DB table.

If you have select in dbtable on non-primary key you can create Secondary index with keys as per your where clause which result in faster DB table access.

But remember not to create too many secondary indexes on same table which could result in slow / degrage performance..

you can check out the link below for more inforamation on indexes

http://www.idig.za.net/mysqlindexes/2006/11/09/

Advantages :

Secondary indexes are used to enhancement performance when retrieving data. For example, say you have a table AFKO where the key to the table is AUFNR. You can select data from this table using AUFNR, the performance will be great because you are using the key. Say you need to hit this table with GLTRP(finish date), it is not part of the key, so the performance would be poor. If you created a secondard index using this field GLTRP, the performance would be improved. Make sense?

https://forums.sdn.sap.com/click.jspa?searchID=1052972&messageID=1633228

http://help.sap.com/saphelp_470/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm

sap.ittoolbox.com/documents/popular-q-and-a/specifying-the-index-to-be-used-2462

http://www.sap-img.com/abap/quick-note-on-design-of-secondary-database-indexes-and-logical-databases.htm

Primary can only be one.

Secondary can be many, depending upon the database limits.

Rgds,

P.Naganjana Reddy

Re: Index

Former Member — Thu, 26 Apr 2007 08:20:49 GMT

Primary is one .

Secondary can be many , but creating many will cause performance issues and may also cause wrong picking of the index for a select

Re: Index

Former Member — Thu, 26 Apr 2007 08:21:08 GMT

Hi Kumar,

There can be only one primary index but can have more than one secondary index for a table.

Reward points if it solves ur query or answer is helpful

Thanks

Chinmay

Re: Index

Former Member — Thu, 26 Apr 2007 08:21:50 GMT

hi Kumar,

Check out these threads

Regards,

Santosh

Re: Index

Former Member — Thu, 26 Apr 2007 08:25:20 GMT

Hi kumar

You can create your index via SE11, enter the table name, click change, choose Go To, Indexes. Here create your index with the key fields that you want. To use the index, your select statement WHERE clause, you must have the key fields of the index in the order that they appear in the index. The "optimizer" will choose the index depending on your fields of the WHERE clause.

One thing to remember is that when you create indexes for tables, the update or insert of these tables may have a slower response then before, I for one have never seen a big problem as of yet.

this Document may help u on primary and secondary indexes :

http://jdc.joy.com/helpdata/EN/cf/21eb20446011d189700000e8322d00/frameset.htm

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/content.htm

There is no limit specified by SAP. But when we create more number of secondary indices, the system takes more times in choosing the proper index while executing the SELECT statement.

As per the SAP help..

Creating an additional index could also have side effects on the performance. This is because an index that was used successfully for selection might not be used any longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the newly created index is more selective.

Check the below sap link for more details.

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb2d446011d189700000e8322d00/content.htm

https://forums.sdn.sap.com/click.jspa?searchID=728985&messageID=1884447

https://forums.sdn.sap.com/click.jspa?searchID=728985&messageID=1633228

http://www.sap-img.com/abap/important-abap-faq.htm

http://help.sap.com/saphelp_nw04/helpdata/en/80/1a6473e07211d2acb80000e829fbfe/content.htm

Regards,

Re: Index

Former Member — Fri, 19 Mar 2010 07:33:51 GMT

correct