Advice on Deletion of unused primary indexes using std SAP program

[Former Member]

Hi,

This is with refrence to the subject matter and we would like to have a sincere advice fro the same.

Is it advisable to delete the unused primary indexes using the SAP standard program.

PROGRAM NAME:RDDCRIND ,as these primary indexes are not being used by any of

the programs.

What are the key points that needs to be considered before deletion of the indexes using the

above mentioned program.

What would be the affect of same on the system and its performance.

Regards,

Neeraj.

[Former Member]

Hi All,

well i am sure that the information shared by you guys is of great help to me but as you suggested me to go for the dropping of z_{table rather than index dropping, so may i please know if there is any way possible through which i can check weather the programs that are present in the WHERE-USED-LIST of these tables are actually not referenced any more or is there any std FM or Report program using which we can find out the list of programs present in the system that are not been used since last 3-4 months .so that we can further strengthen our argument of deleting tables rather than index itself and adding to it, i would also like to know in case we delete the z}tables ,would it show up under their respective Transport requests.

Kind Regards,

NEERAJ.

(SAP NW'04 Consultant)

[Former Member]

Dear Lars AND Mark thankyou very much for your valuable inputs but lemme explain this senarion to you in brief, lemme tell you how i reached to the conclusion of deleting these primary indexes on z-tables.well this is an innitiative taken to reduce DB size i.,e as we keep on adding the data to the DB server the size of DB would keep on incresing irrespective of the kind of data present in it, but based on our internal analysis and assistance of our DB team(ORACLE DB),we managed to get a list of primary indexes on z~tables which were not used or accessed,and hence forth we did a further analysis by individually taking each tables WHERE -USED- LIST and check in all the programs that are making use of of this index or not.and all the tables that fit our criteria we further analised there NO. OF TABLE ENTRIES which were '0' , then moving further to strengthen our argument we checked their date of creation their creator and weather those table were actually used in our programs anymore or they are no more of any use to us and finally all those tables containing those indexes who met all these criteria were sorted and collected in a different list which we prepare for storing those tables marked for deletion.

Based on this kindly provide your valuable feedback,it would be really appreciated.

Regards,

NEERAJ.

[lbreddemann]

Hello Neeraj,

by looking for tables without rows in oder to find out wether a primary key index is used or not you do fall for a fallacy of thought.

Don't think of primary indexes as data structures that 'make things fast'. Rather think of them as part of your data model.

Would you start to drop the columns of the tables to save space?

Would you drop the tables alltogether to save space?

If the answer to the last question is YES, then don't just drop the index. In that case get rid of the table.

Otherwise leave the index (especially the primary/unique) indexes as they are, since they are part of your data design.

By the way: if the table is empty than you would only be able to save the initial extent of the index segment.

So either the initial extent would be rather large or this whole discussion already costed more than the storage space saving would save you

regards,

Lars

[stefan_koehler]

Hey,

> So either the initial extent would be rather large or....

... wait for Oracle 11gR2 .. then you have "deferred segment creation" 😛

Regards

Stefan

[Former Member]

Hello Neeraj,

Thank you for your extensive reply. However I still have quite a few concerns:

we managed to get a list of primary indexes on z~tables which were not used or accessed

How did you determine that the indexes were not being used? To be frank, if you created a Z-table and then wrote programs around it that do not even once issue a SELECT with a WHERE clause that needs the primary key, then those programs would truly have a design issue. And even if for some reason the primary index was never needed for selections, it would still be necessary to preserve integrity (uniqueness).

we further analised there NO. OF TABLE ENTRIES which were '0'

If today the table is empty, does that mean the table will always remain empty? If no, you still need the table and the primary index. If yes, then drop the table.

weather those table were actually used in our programs anymore or they are no more of any use to us

Same remark: if no longer of any use, then drop the tables

assistance of our DB team(ORACLE DB)

This is the most worrying thing of all. SAP is an application built on top of a relational data model, and expects certain rules to be respected to keep this model in good working order. Always be very cautious with advice coming from DB specialists who are totally unfamiliar with SAP. Their advice might be valid in a custom-built database application, but totally invalid in the SAP context.

Hope this helps,

Mark

[Former Member]

Hi Neeraj,

Unique primary key indexes are an absolutely essential element of the SAP data model and you should never ever delete them. If you do, the result will be massive performance and data integrity problems. Was this question the result of external advice given to you?

Regards,

Mark

[lbreddemann]

Hi there,

how do you come to the conclusion that the unique indexes used for primary key constraint implementation are not used?

They are used.

These indexes are necessary to make the constraint checking for the primary key possible at all.

Without them there is no check for uniqueness anymore.

Your data could become invalid or at least ambiguous without these indexes.

That's the reason why the DBCHECK of brconnect always prints out warnings about missing primary key indexes.

Therefore: don't drop these indexes!

regards,

Lars