JDE Index optimization

Hi!
I have following problem with growing table F41021 in Oracle DB:
data - 3.5 Gb
indexes - 11Gb
So, I want to optimize indexes organization - delete odd indexes.
F41021 has couple (for example, because there are another indexes exist) indexes:
[ QUOTE ]
PRODDTA.F41021_2 on PRODDTA.F41021 (LIITM, LIMCU, LILOTN, LILOCN)

[/ QUOTE ] and
[ QUOTE ]
PRODDTA.F41021_6 on PRODDTA.F41021 (LIITM, LIMCU, LILOTN, LILOCN, LIPBIN)

[/ QUOTE ]
The difference is in one additional field in second index LIPBIN. For Oracle optimizer it will be the same for query's processing, if there will be only last index - it's satisfying for conditions with
[ QUOTE ]

"where LIITM=... AND LIMCU=... AND LILOTN=... AND LILOCN=..."


[/ QUOTE ] AND
[ QUOTE ]

"where LIITM=... AND LIMCU=... AND LILOTN=... AND LILOCN=... AND LIPBIN=..."


[/ QUOTE ] If I will drop first index in DB(not in JDE), will be some problems with work in JDE?
I've tested such variant on small test application and it seems to work fine, but I'm not sure about possible problems in JDE.
Can somebody help me in this situation?
Thanks.

oleg_d,

As far as I understand this, the indexes inside JDE are only for selection criteria on table I/O. Our DBAs (Database Administrators) have told me a number of times that the oracle database will decide on its own what index it uses. I believe that there is a way to create a SQL that "forces" the use of an index, but the SQLs produced by JDE do not do this.

Having said that, let me say this: it should be OK to drop the index in the DB and not in JDE. JDE can be "tricked" into "thinking" a database view is a table (search JDEList with "virtual table"). So the ties to the database are not that strong.

However, I would advise discussing this with your DBA first, before you do anything.

That's my AUD 0.02 worth, I hope it is of some assistance.

Hi!
I'm DBA and CNC admin
So, I know (according to my knowledge that Oracle will use longer index(with extra fields, but with same fields order as in shorted index), if shorted will be deleted.
But I wasn't sure about JDE behavior in this case.
Thanks for help, I will find about virtual tables.

Thanks.