Skip to Content
author's profile photo
Former Member

seconday-index why

hello guru

why r u creating secondary indexes only 5, we have a facility to create 15 indexes, but why we take only 5 secondary indexes?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • author's profile photo
    Former Member
    Posted on Sep 28, 2007 at 09:03 AM

    Hi

    Its always good to create few secondary indexes.

    Secondary index : Its created as and when required,

    based upon other field(s) of the table,

    on which search criteria is used in sqls.

    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.

    CREATION

    go to SE11

    enter table name

    press display

    from menu GOTO-> indexes

    press create button

    enter the fields for which you need the index

    save and activate

    use those fields in the Where condition of the select statement and see

    Regards

    Anji

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Sep 28, 2007 at 09:07 AM

    Hi

    <b>Primary and secondary indexes</b>

    Index: Technical key of a database table.

    <b>Primary index</b>: The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

    <b>Secondary index</b>: Additional indexes could be created considering the most frequently accessed dimensions of the table.

    The database optimizer decides which index on the table should be used by the database to access data records.

    You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

    The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

    If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

    When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated

    <b>Reward if useful</b>

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 28, 2007 at 09:09 AM

    hi

    secondary indexes

    are to be used for speed up data retrieving

    withou secondary index it is not possible to fetch data from data base when ur using non primary key fields in where condition of aselect statement

    u can create as many as u want

    but performance wise it is better to restrict for 5 indexes

    here when assigining secondary indexes user has nothing to do

    optimizer will take care of these things

    to avoid unnecessary in consistences it is better to restrict secondary indexes upto 5

    about secondary index disadvantages

    go through these links

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

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

    reawrd for use ful answers

    regards

    Nagesh.Paruchuri

    Add comment
    10|10000 characters needed characters exceeded