Skip to Content
author's profile photo Former Member
Former Member

sencondary index

What is the purpose of creating secondary index? I heard that this wil increase the select performance...but i dint get the real concept? Pls explain with an eg?

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2007 at 04:10 PM

    If you do a select on a field in a table without using an index, the database must read all entries in the table to see if the field matches the value in the where clause. If the field is an index field and all previous index fields are also specified, the database can go directly to the record(s) that match the WHERE clause.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2007 at 04:33 PM

    HI,

    Procedure to create the sencondary index

    In the maintenance screen of the table, choose Indexes.

    If indexes already exist on the table, a list of these indexes is displayed. Choose Create .

    In the next dialog box, enter the index ID and choose ok

    The maintenance screen for indexes appears.

    Enter an explanatory text in the field Short text.

    You can then use the short text to find the index at a later time, for example with the R/3 Repository Information System.

    Select the table fields to be included in the index using the input help for the Field name column.

    The order of the fields in the index is very important.

    If the values in the index fields already uniquely identify each record of the table, select Unique index.

    A unique index is always created in the database at activation because it also has a functional meaning (prevents double entries of the index fields).

    If it is not a unique index, leave Non-unique index selected.

    In this case you can use the radio buttons to define whether the index should be created for all database systems, for selected database systems or not at all in the database.

    Select for selected database systems if the index should only be created for selected database systems.

    Click on the arrow behind the radio buttons. A dialog box appears in which you can define up to 4 database systems with the input help. Select Selection list if the index should only be created on the given database systems. Select Exclusion list if the index should not be created on the given database systems. Choose ok

    Choose Activate

    Aee the threads

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

    how-to-utilize-index-in-selection-statement

    Regards

    Sudheer

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2007 at 04:52 PM

    An index is sorted and will be searched with a binary search, so the time for finding an entry will much shorter. The average search time for a random full table scan will n/2 with an unique index it will be ln(n). It is like searching a special page in a book, turning page by page and open the book in the middle, ok to less, open the book in the middle between middle and end and so on. Google for B-trees.

    HTH

    Matthias

    Add a comment
    10|10000 characters needed characters exceeded

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

    Balaji,

    Some times, requirement will be, you have to read the records not based on your primary keys.That time system will take lot of time to read all the records.So that time you willl go for secondary index creation,which means that when you try to extract data based on your secondary index it will take required records in buffer and extract the data from buffer for further select..So performance wise it will good.

    Pls. mark if helpful.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 10, 2007 at 11:00 AM

    Secondary index would improve performance in select query but it can have negative impact on performance in other areas.

    -any inserts to the table, since we also have to update the index

    -any query that no 'accidentally' also uses this index, even though another one would be better

    -the statistics should be updated on the table after the index is created (if not, the index would not have stats, and oracle will pick it for just about any query that touches the table)

    Hope the above clarifies.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2007 at 11:14 AM

    Hi Balaji,

    Secondary Indexes will be created to increase the performance, say sometimes we are going to read no-unique key fields in WHERE clause. So obviously it will give performance.

    If it helps, close the thread.

    Regards

    Eswar

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2007 at 02:13 PM

    Hi Balaji,

    Secondary index is created on the database with the fields you give as key .

    What happens is similar to that of the telephone directory.

    As in case of tel. directory, the nos are stored against names which are inturn written in the direstories in their alphabetical pages .

    When you search for a particular persons name you go to the alphabet with which the persons name starts and then start searching the name therein.

    Similarly in this case the database table sorts the data in that table according to the keys you have provided and then it can use binary search to effeciently find out its data

    Regards,

    Sunmit.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.