where used list for indexes

[rainer_hbenthal]

Hi,

cause indexes are only used to create a plan how to access db-tables there is no such functionality directly to have a look which index is used in which SQL statement and at least find out the reports where the index is being used. In Addition that can vary all the time due to runstats.

Currently we have 9 Indexes on mseg, 5 from SAP and 4 own. If i'm now changing one of these z-indexes how can i find out which reports are affected? Or is there any other way to find out if one of these indexes is obsolet cause its never used?

Or is it better to create just a new one? But indexes need to be taken into account on each insert, update and delete and are slowing down these processes.

Whats your expreience having a lot of indexes on a table?

[former_member192616]

Hi Rainer,

wheter you change an existing or create a new index doesn't make a change in terms of "risk on reports affected" since

you have a major change for the optimizer in any case. But you are right, that for a new index the burden for DML on the table is higher.

We can find out if indexes are used or not. At least on some database platforms (e.g. in ORACLE and maybe in MSSQL but I'm not sure... as well for the others i don't know... a good DBA should be able to tell you if this is possible or not). However these

options are database dependent and require native access to the database. Furthermore the usefullness of such features are... well... . If we think about it: E.g. on ORACLE we can switch on usage monitoring per index. Then the dbms records whenever such a monitored index is used. But... If you switch it on for a few days and find out that an index is not used... this just means it was not used in these few days of the monitoring. If we think of month end processing, quarterly reports, year end processing... can we really be sure that an index is not required?

Which reports are affected: Theroretically all reports that access the table in question. A good start is regular SQL cache analysis with a filter on the table. Here you can see all regular or recently executed SQL statements (since the database is up don't forget the displacement) and their execution plans. But there might be reports that don't show up in the cache and might be affected too.

There is no silver bullet for your questions, sorry.

Kind regards,

Hermann

[rainer_hbenthal]

Hi Hermann,

thanks for your thoughts about that. I'm aware that indexes can be used only once a year, we have the same problem while investigating in unsed reports. We do a research of the workload every night for over a year now to find unused reports and unsed transactions. At least, the workload clearly states if a report did run or not.

There is no such thinf for indexes, in your words no silver bullet. But if an index is used only at years end calculation i would drop that and reinstall it at years end calculation.

Maybe someone else has some ideas of getting rid not used indexes. By the way we are running at oracle.

The other thing i noticed that a lot of indexes have the client as first field, due to the low selectivity of this field i would like to drop that or put the field at the end of the index to move the plan from tablescan with rowid to index only access. I saw that even SAP advises to put the client in front in of the index, to be honest i can not follow that. What are the reasons for this approach. Did i miss something that makes the client mandantory in secondary indexes?

[former_member192616]

Hi Rainer,

regarding the monitoring:

well on ORACLE i would use the monitoring usage approach:

switch usage monitoring on:

ALTER INDEX "MSEG~Z01" MONITORING USAGE;

switch usage monitoring off:

ALTER INDEX "MSEG~Z01" NOMONITORING USAGE;

Check the results in the V$OBJECT_USAGE view.

SELECT index_name,

table_name,

monitoring,

used,

start_monitoring,

end_monitoring

FROM v$object_usage

WHERE index_name = 'MY_INDEX_I'

ORDER BY index_name;

You must query this view as object owner (schema owner SAP<SID>, SAPR3, ...).

Note that the information in V$OBJECT_USAGE is reset when you shutdown and start up so you have to

query it and store the information elsewhere.

Kind regards,

Hermann

[former_member192616]

Hi Rainer,

for your second part:

> The other thing i noticed that a lot of indexes have the client as first field, due to the low selectivity

> of this field i would like to drop that or put the field at the end of the index to move the plan from tablescan

> with rowid to index only access.

What do you mean with that? Move from "index (range/unique) scan + table access by index rowid" (index + table access) to

"index (range/unique) scan" (index only access)? Could you give a concrete example? For any select that has the client field (which are most of the selects in SAP) removing the client from the index will lead to an table access (to verify the client which is generated in the where clause...) I'm not sure if i understood correct what you mean...

> I saw that even SAP advises to put the client in front in of the index,

and i saw the opposite recomendation from SAP ... (e.g. note 825653 nr. 22) it probably depends on who you ask... (kidding... it depends on the concrete case)

> to be honest i can not follow that.

> What are the reasons for this approach. Did i miss something that makes the client mandantory in

> secondary indexes?

There are reasons for putting the client in the index and there are reasons for putting it in the first place. And there are

reasons for not putting the client in the index.

There are several topics and discussions:

some topics:

- several clients and locality of the data

- uniqunes of the primary key (sometimes only WITH client possible)

- space consumtion (on disk, backups, ... and more important in the database cache)

- note 176754 (nr. 18)

- avoid table access for the client only....

- index compression (columns with lower distinct keys in the beginning lead to better compression)

- other topics...

Kind regards,

Hermann

[Former Member]

Hi Rainer,

Check out OSS note [825653 - Oracle: Common misconceptions|https://service.sap.com/sap/support/notes/825653] and look point 22. As explained adding the client to your index adds in many cases no selectivity at all (especially if you're having only one productive client). You can encounter problems in certain situations though with Oracle not picking up the right index, as explained in OSS note [176754 - Problems with CBO and RBO|https://service.sap.com/sap/support/notes/176754]. By adding client as the first field, you basically could ensure that the first index field could always be specified with an equals condition, thus circumventing the problems described if this field was missing.

If you're concerned about index space consumption (your description about dropping and creating an index sounds unusual), you might want to take a look at OSS note [1109743 - Use of Index Key Compression for Oracle Databases|https://service.sap.com/sap/support/notes/1109743] (sorry, if this is old news for you).

Cheers, harald

Edited by: Harald Boeing on Mar 30, 2010 2:49 AM: Sorry, after posting my message I saw that it's actually obsolete, because Hermann and others had already given excellent long explanations...

[rainer_hbenthal]

>

> If you're concerned about index space consumption (your description about dropping and creating an index sounds unusual), > Edited by: Harald Boeing on Mar 30, 2010 2:49 AM: Sorry, after posting my message I saw that it's actually obsolete, because Hermann and others had already given excellent long explanations...

Maybe its unusual, but for years end calculation i'm creating a very complex index having 20% of all table fields. This speeds up the report from round about 8 hours to 30 minutes cause this index is very special for exactly this huge select statement. Creating the index, running the report and dropping the index takes less of three hours, so i'm gaining more then 50% runtime. During the year, i dont want to have this index maintained by DML.

Okidoki, will read the OSS Notes i got from you digging a little bit deeper into the science of the optimizer.

[Former Member]

Maybe its unusual, but for years end calculation i'm creating a very complex index having 20% of all table fields. This speeds up the report from round about 8 hours to 30 minutes cause this index is very special for exactly this huge select statement.

Ah, ok, now I suspect I understand this better. Sounds basically like you're defining an index that probably even contains all the required fields thus limiting I/O access to index only...

The unusual comment was a poor choice of words and should have read more like interesting. I.e. I was a bit surprised to see such activities for year-end-processing, wondering who is responsible for doing that. But probably you have it all fully automated and run it as job steps...

[former_member192616]

Hi Rainer,

> I saw that even SAP advises to put the client in front in of the index, to be honest i can not follow that. What are the reasons for this approach. Did i miss something that makes the client mandantory in secondary indexes?

another comment:

i just checked the system i'm currently working on and found 1646 indexes in the SAP namespace on client dependent tables

that do not have the client (as a first field, or not at all) in the secondary index... (take USR01 as an example...)

sometimes it is worth comparing what one does with what one says and think about it...

No, honestly: I know the above recomendation. And it is not wrong as a recomendation in general. It doesn't harm, it can make sense and in some examples you can do it in another way...

Kind regards,

Hermann

[Former Member]

I saw that even SAP advises to put the client in front in of the index

Rainer/Hermann, do you have a (recent) documentation link where that's stated? I only know this from way back, but haven't come across this recently. In the past this might have been appropriate considering the databases SAP was supporting and their capabilities. However, nowadays it seemed that there's the more nuanced recommendations like in OSS note [825653 - Oracle: Common misconceptions|https://service.sap.com/sap/support/notes/825653] SAP under 22:

It is not necessary that all SAP indexes start with the client column. In most cases, including the client in the index is more of a disadvantage, since it makes the index larger but - due to the small number of clients in the system - it does not improve the selectivity. Including the client at the beginning of the index is useful only in specific situations.

[former_member192616]

Hi Harald,

i have heard this in trainings (BC490, USCP50, ...) several years ago but i'm not sure if it was stated on slides or only told verbally.

I think it is as you said. This recommendation used to be much more important than it is today. And if you take a look and

check recent systems (e.g. CRM or others) you can find many (SAP delivered) indexes on client dependent tables without the client. From my experience this changed as well over time in the past it was not that much.

Kind regards,

Hermann

[Former Member]

Thanks for the update. As with any of those odd statements stuck in my brain I sometimes have difficulties identifying the original source. This leaves me wondering if I'm just replicating a myth/rumor that some seemingly reliable person had stated with great authority...

[former_member194613]

there was a discussion about the client field some years ago. But now it is generally agreed that the client should be the first index field, if the table is client dependent.

The client is not selective but who cares, because of the automatic client handling of the DB interface it usually always there with an equal condition. It does not help and does not harm with respect to selectivity. It helps for transports, clients copies, partitioning and some of the problems with some databases had, like the transitivity problem with joins of client-dependent tables (db did not understand that the client is for all tables given) are fixed.

Siegfried

[rainer_hbenthal]

Just to make sure, i'm not talking about cleint in primary key und in the resulting unique index(which you cant change in SAP). I'm only talking about sencondary indexes.

And for me it still makes no sense to have e.g. client. matnr on mseg as secondary index. amtnr is much more selective and having only a couple of clients this is wasting ressources. And something which is not in present does not need to be compressed. Sigfried, i can not see any of your argumentfulfilled and if it takes the time to create a index with/without i would always omit the client. the mandt is always given by default, you're right, but thats no reason to put it into the index. The result is always filtered again the complete where condition.

Hermann: guess you have the following select statement

select matnr, bwart from mseg where matnr = .... and bwart = ...

If there is an index inb mseg with matnr, bwart you will get a result drom the index, but still this needs to be filtered against the where condition which implicit says mandt = sy-mandt. So you will get a table scan by rowid just to read the client. If this select is executed very very often (maybe with in clause istead of equal) it makes sense to out the client as last column to the index: matnr, bwart, client

With this index all information is present and the system does not need to read the table, so you will get an index only access for the above sql.

From my understang of searching in b-trees the sequence of fields is important, even if the gain is small. Why should i throw away this gain if it is the same work when creating the index?

[former_member192616]

Hi Rainer,

your example given here:

> guess you have the following select statement

>

>

select matnr, bwart from mseg where matnr = .... and bwart = ...

>

> If there is an index inb mseg with matnr, bwart you will get a result drom the index, but still this needs to be filtered against the where condition which implicit says mandt = sy-mandt. So you will get a table scan by rowid just to read the client. If this select is executed very very often (maybe with in clause istead of equal) it makes sense to out the client as last column to the index: matnr, bwart, client

>

> With this index all information is present and the system does not need to read the table, so you will get an index only access for the above sql.

- absolutely right: it makes sense to have the client in the index in order to avoid the "table acess by row id" as you said.

> From my understang of searching in b-trees the sequence of fields is important, even if the gain is small. Why should i throw away this gain if it is the same work when creating the index?

- for this example the order of the fields (if the client is the 1st or 3rd field) is not important (see note 825653 nr. 10)

Kind regards,

Hermann

[ThomasZloch]

Look here what I asked three years ago:

Unfortunately there were no experts around then. My clumsy solution involved reading V$SQL and V$SQL_PLAN, but I was never sure if this catches all index usages.

Thomas

[former_member192616]

Hi Thomas,

interesting... .

Monitoring index usage is exactly what you wanted i think.... hope you can still use it in future...

Kind regards,

Hermann

[former_member194613]

Updated:

The table and therefore the primary key should always have the client as a first field, if there is a client.

For the secondary keys it makes sense, to differeniate between secondary indexes which are active in

the SAP standard, i.e. all customers and all databases.

There I think the recommendation is currently to have the client as first field.

For additional secondary indexes only active in a specific customer system, it can be decided based on

much more information:

+ like the used database

> However, nowadays it seemed that there's the more nuanced recommendations like in OSS

> note 825653 - Oracle: Common misconceptions SAP under 22:

That is an Oracle Note no general SAP statement ....

+ the size of the table

> it makes the index larger

weak.

+ the knowledge how many clients exist and are expected

> The client column is selective because the table is filled with

> data in several clients.

Counting of the existing indexes in the system does not help, since indexes are not changed

only because a recommendation changes. Some years ago it was not recommended to have the

client as a first field.

Siegfried

Edited by: Siegfried Boes on Mar 31, 2010 3:44 PM

[former_member192616]

Hi,

in note 525673 i found the following:

"Follow the recommendations below for index instances:

Client field in the index:

If you are using a multi-client environment, SAP generally recommends

that you use Client as the first field in the index.

Most indexes provided for address tables do not contain the Client field

as the definition was already delivered before SAP issued the currently

valid recommendation.

Regardless of the recommendation above, SAP cannot change the

definitions of already delivered indexes in the standard system.

Such a change would require a very time-consuming conversion of the

database in each customer installation.

The recommendation, however, applies to new indexes and additional

customer-specific indexes. The Client field in the index is not required

if you only work with one client in the SAP system (except for the 000

and 066 clients) as then, the Client field is not selective."

For sure in standard software (general cases) the design has to be defensive and asume

multiple clients. Specific cases however allow specific decisions i would say.

If a secondary customer index should be created one can take into account

the things mentioned (database, size, clients, ...). (MaxDB and MSSQL store

the client column in secondary indexes on client dependent tables in any case

whether it is specified or not.) At least in my area (support) most of the systems

we see have only one client. That's probably why most of the colleagues here tend

to ommit the client when this is possible.

Kind regards,

Hermann