Creation of indexes

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hello,

When my SAP database was SQL I used to use SQL commands in order to rebuild indexes and update statistics.

Am I able to do these same tasks(rebuild and update statistics) in DB13 just for one index ?

Eduardo

[Former Member · Accepted solution]

Hi Eduardo,

You can do the same with Oracle.

From sqlplus you enter the command: analyze index "<schema>"."<index name>" estimate statistics;

For rebuild: alter index "<schema>"."<index name>" rebuild online parallel X nologging;

Creating statistics can also be done by using brconnect, rebuilding of tables/indexes can be done with brspace.

See help.sap.com for documentation regarding these tools.

Kind regards,

Mark

[former_member227281 · Accepted solution]

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