New Index on Transaction Table

[Alpinist]

Hey JDEList'ers,
What are the ramifications of adding a new index to an existing transaction table? I added a new index to the F03B11 using DCT/VR01, and I realize that there may be a slight performance hit as it has to create the index. What is the impact on performance? Negligible or greater? Also, are there any other issues with doing this to a base E1 table, other than having to re-add the index should JDE make a table change.
I'm on 9.0/8.98 and currently in a DV environment, so would like to know now if this will be causing issues down the road.
Thank you,
K

 

[BOster]

You shouldn't have a problem adding a custom secondary, non-unique index to a pristine table. We have done it quite a bit. However, the ONLY time I recommend doing this is to increase performance. There really isn't any other reason to add a secondary index, so you have to evaluate the write performance hit against the read performance gain (if any). This is where a good DBA can help.

 

[Zoltan_Gyimesi]

Hi Alpinist,

Generally the answers are trivial.

LOSS:

#L1:
Increased execution time on Insert / Delete / Update table actions, because should mainain the new index too. Generally you can omit this, because in the process environment, where these tables are maintained, containes a lot of logic and table IO, so the increment is relativly minor.

#L2:
Increase the required table space to store the new index - in line the backup resource capacity and backup time.

GAIN:

#G1:
Decreased execution time of some (generally customized) process, query, report, etc. Sometimes it can be dramatical, e.g. from hours down to minutes or less. This is generally the goal, why to consider to add new index.

NOTES:

#N1:
Making the index UNIQUE will additionally increase #L1 on Table IOs.
There are several vanilla indicies in JDE Data Base, which are LOGICALLY UNIQUE, though they are not declared as UNIQUE in the table definition. If my memory serves me well, I never had seen any vanilla JDE table, where other than PRIMARY key were defined as UNIQUE. Generally the Business Logic and the "CODE" ensure this feature.

#N2:
Check your Developers manual for performance tips and for restrictions and limitations and special impacts regarding your database. I am not familiar with the documentations of your release, but Development Tools manual containes these chapters, somewhere on the end of the manual, maybe in the Appendicies.

#N3:
Check LOSS against GAIN in a DV environment where the data is near to PD, to see the benefit of the new index.

Regards,

Zoltán

 

[BOster]

[ QUOTE ]

If my memory serves me well, I never had seen any vanilla JDE table, where other than PRIMARY key were defined as UNIQUE. Generally the Business Logic and the "CODE" ensure this feature.


[/ QUOTE ]

F4101 is the only one I can think of.

 

[Zoltan_Gyimesi]

Hi Brian,

[ QUOTE ]
F4101 is the only one I can think of.

[/ QUOTE ]

What is this table?

Ooops, shame on me, how could I forget this.

Thanks & Regards,

Zoltán