Skip to Content
avatar image
Former Member

Creation of indexes

SAP 4.7 6.20 Oracle 10g WINDOWS SERVER 2008

Our SAP system was converted from SQL to Oracle database.

I'm a Basis guy and I'm trying to adjust myself to this change.

For the first time with this new oracle database, I see that it's necessary to create a new index for a table.

So I just used SE11 to create it like I always did when I had an SQL database.

My questions are about the different needs which I could have to deal with, and that's why I'm asking for your help.

1) After creating an index in SE11 and activate it, do I need to do anything else for it to work ?

2) About maintenance. In SQL I have to rebuild the indexes sometimes for it not to lose performance. In Oracle it seems that there is a tablespace exclusive for indexes. Do I have to do any frequent maintenance for Oracle indexes to continue working properly ?

3) What's the best way for me to check if the Oracle index is being used by a program ?

Thanks in advance,

Eduardo

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Dec 01, 2010 at 12:58 PM

    1) After creating an index in SE11 and activate it, do I need to do anything else for it to work ?

    Yes, you need to update the statistics for this index, otherwise it will not be used..

    2) About maintenance. In SQL I have to rebuild the indexes sometimes for it not to lose performance. In Oracle it seems that there is a tablespace exclusive for indexes. Do I have to do any frequent maintenance for Oracle indexes to continue working properly ?

    Yes, in case indexes get unbalanced, they need to be rebuild. This will be mentioned in your database check or statistics log in db13.

    3) What's the best way for me to check if the Oracle index is being used by a program ?

    You can see this in transaction st04 -> detailed analysis -> oracle session

    Select the statement that you want to analyze. In the explain plan you can see if it is using the index.

    Kind regards,

    Mark

    Add comment
    10|10000 characters needed characters exceeded

    • Execute program "RSANAORA" in tx cose se38.

      This is used to perform statistics on table, index. also this program is also used to rebuild the index too.

      It is not advisable to perform the command at Database level for security resons.

      For oracle10g and above, using Analyze command for pefforming database statistics is onsolete and is not effective.

      DBMS_STATS is more effective in performing database statistics.

      Thanks and Regards

      Apr