Extending secondary index...consequences

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Extending secondary index...consequences

Post by Dr Sidewalk » Mon Apr 14, 2008 8:32 am

We use Oracle, and have a secondary index (Z01) against table LIKP which currently consists of fields MANDT and ERDAT. We Have identified a number of badly performing programs that access this table using fields MANDT/ERDAT/VKORG/LFART. My question: is there likely to be any far reaching consequences if we extend this existing secondary index and add the two extra fields. There are obviously other programs that utilise this current secondary index based on MANDT/ERDAT and I assume that having extended this index these programs/SQL will perform using a partial index, which should be equally as fast. Is that last statement fair/correct?.

We no longer have a volume environment other than production, hence my hesitation. Normally I could just try this in a QAS and watch the effect on other programs as well.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

ssii
Posts: 222
Joined: Sat Nov 11, 2006 9:57 am
Location: France

Re: Extending secondary index...consequences

Post by ssii » Mon Apr 14, 2008 9:18 am

Hi Doc,

I beleive you are right... Extending this index will not affecte much other programs...

If some doubts exist, you should create a new index and then the system will select the good index depending on the select...

SSII
SSII is back

christmaslights
Posts: 857
Joined: Wed Jun 16, 2004 5:13 am
Location: Hampshire, England

Re: Extending secondary index...consequences

Post by christmaslights » Mon Apr 14, 2008 9:23 am

I should be very surprised if extending a custom index in this way caused you any problems. The index will, of course, be larger because of the two extra columns and I've got a vague memory that using the full index is supposed to be a bit more efficient than using only the first two columns, but I would have thought that any difference in access times caused by either change would be so small as to be unnoticeable.
Using SAP R/3 version 4.7 BASIS 6.20 under SunOS on an Oracle 10.2.0.2.0 database

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Extending secondary index...consequences

Post by Dr Sidewalk » Mon Apr 14, 2008 9:36 am

Yeah, I had already suggested creating a new index altogether but our Basis team will not buy that one. My suggestion was that it's technically tricky to extend an index (especially if it's used 24/7) whereas creating a new one should be easier and have less knock-on effects, plus, any problems and this new secondary index could be just removed/deleted. However, it looks like our basis team are going for the extension. Their call. :)


Thanks all.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Extending secondary index...consequences

Post by Dr Sidewalk » Mon Apr 14, 2008 9:39 am

Apologies everyone. I've just noticed where I've posted this. It was destined for the BASIS forum. I guess I went to the ABAP forum out of habit. Doh!

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

Post Reply