Changing SAP Indexes

[peter_strauss]

Hello, another n00b question I'm afraid.

I often see SAP recommending not to change SAP indexes.

I can see that modifying an SAP index would be problem if the index is dramatically changes, or after an upgrade because the change will be lost. But is it always a bad thing?

Say you have an index TABLE~01 with field A only, supplied by SAP.

If you need an index with fields A and B I feel that it would be better to simply add field B to the existing index, rather than creating an new Z index. Any sap code that needed the original TABLE~01 should not be hindered by the addition of field B.

Any advice much appreciated.

Kind regards,

Peter

[Former Member · Accepted solution]

Hello Peter,

here some additional thoughts from my side:

- I recommend changes to SAP indexes during my analysis rather often (mainly adding additional columns at the end of non-unique indexes) because I think that usually it's a good idea to create only as few indexes as possible (less disk space allocation, less buffer pool allocation, quicker DML operations, less confusion for the CBO).

- There is one more potential problem if you add columns at the end of an index: If the already existing indexed columns had only a few distinct values, the clustering factor of the index was quite good (because in case of identical values the index entries will be sorted based on the ROWID). If you now add additional columns, this can significantly impact the clustering factor of the index and the performance of large range scan accesses. Nevertheless I must admit that I have not seen significant problems due to this scenario, yet.

Regards

Martin

[peter_strauss · Accepted solution]

Hello Martin,

Thank you!!!

Another question I have along the same lines: the customer is only tracking modified indexes by searching the database for indexes starting with Z*. I'm sure there is a better way of doing this. I guess this is not database specific so I'm probably asking in the wrong place. Any help is appreciated.

To Stefan,

In the customer system changes in ADRC are not frequent. I am not sure whether this is expected to change after golive.

At present the table is 4GB. Index ADRC_{Z01 is 500MB and ADRC}I03 (still in its shipped form) is 300MB.

MC_STREET is not so important because it is used with "LIKE". Maybe it would be better to leave out altogether.

The main concern with the new index was that during a BI extraction it was used in a nested loop where it shouldn't have been and it caused huge performance issues.

I know it is impossible for you to make any final judgement without knowing more about the scenario (if you are interested I can provide you with a connection - but I don't expect you to do my job for me (although I do often rely on my colleagues!

Kind regards,

Peter

[Former Member · Accepted solution]

Hi Peter,

you are right - determining modified indexes is nothing that can be done on database level. I assume that it is possible based on some SAP DDIC queries, but I have no idea how to do it in the best way.

Kind regards

Martin

[stefan_koehler]

Hello Peter,

if you see only such a simple example, you are almost correct.

But please think about the index structure:

If you add more columns to an index, the column values are stored in the leaf blocks and more space is needed (ignoring the access path in the branch blocks). If you don't want to influence the access path in the index structure you need to append the column after the "original" index column.

For information only:

Have you really seen such an index which needs to be extended or was your question hypothetical?

Your example also only works if the index is non-unique. If it is an unique index with only one column and you add another column you change the "logic".

Regards

Stefan

P.S.: Just to be complete on oracle level.. if you having a primary / unique key constraint on the one column you should think about extending the index.. but until now i only have seen NOT NULL constraints in a SAP environment, so this case should not be hit and constraints are another topic.

[peter_strauss]

Hello Stefan,

I am dealing with indexes on table ADRC (in ECC 6.0)

SAP ships with many single column indexes on this table.

One is ADRC~I03, which has column MC_CITY1 only.

ADRC~Z01 was added with columns

CLIENT

MC_CITY1

HOUSE_NUM1

PERS_ADDR

ADDR_GROUP

Client has a distinct of close to 1 (3 actually but nearly all of 12 million entries are in client 100)

The table has 10 SAP indexes plus the one new one.

My feeling is that it would be better to use the preinstalled index ADRC~I03 as per the following

MC_CITY1 1,564

HOUSE_NUM1 26,014

MC_STREET 39,168 << this is in the relevant select but is a "like" clause"

PERS_ADDR 2

ADDR_GROUP 4

Kind regards,

Peter

[stefan_koehler]

Hello Peter,

i have checked your data with my SAP ERP 2005 system.

You are right that there are many indexes defined by SAP, but not all are active in the database. In my system the following indexes are created on the database:

Unique Index ADRC~0 
CLIENT 
ADDRNUMBER 
DATE_FROM 
NATION 

Index ADRC~I01 
MC_NAME1 

Index ADRC~I03 
MC_CITY1 

Index ADRC~I09 
MC_STREET 

Index ADRC~I13 
SORT1 

In this case you can expand the I03 index, but why not creating a new index? The table has a size of 190 MB and the index I03 a size of 12 MB (in my prod system). So question: Are this 12 MB really so important to modify the standard? I don't think so..

So now let's take a look at your modified index:

If i understand you right, if the column MC_STREET is the only important column: Why creating a concatenated index? Think about the full qualified index path... a unique index access path can only happen, if you also have specify MC_CITY1 and HOUSE_NUM1 in the sql statement.

Maybe i think this is also the cause, why sap has defined so many "little" indexes on this table (to avoid unnecessary i/o with unqualified index access pathes).

Of course you have to think about the overhead by inserts / deletes, but the ADRC table is not the table with the highest DML rate.

Regards

Stefan