OneWorld Indexes

[MelissaS]

Oracle 8.1.6, Solaris, Xe

As our F0911 grows, out DBA is concerned with the number of indexes on this table (more than 20). She wants to identify which ones we have never used and delete them. I was wondering what the repercussion of deleting these unused indexes would be. If we can delete them - should we delete them in oracle or through the JDE tool.

Thanks!
Melissa

 

[brother_of_karamazov]

Has your dBA identified these indexes as unused or is
she or he just having a knee-jerk reaction to seeing a
lot of indexes? Has the dBA shown these to be a
performance problem?

JD Edwards indentifies indexes based on standard
business practices, make sure you dBA isn't
overreating.


--- MelissaS <[email protected]> wrote:
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OW&Number=26117


__________________________________________________

 

[scottw]

Technically, the indexes are used in the ER for file IO functions. Start
deleting the indexes and not the programs using the file IO, i.e., "fetch
single" no longer works. No you have big problems every where the index is
used (mainly in the ER) of JDE programs. There could be possibly hundreds
of places a specific is used. I not sure there is a easy way to tell where
indexes are used. It is a crapshoot at best; good luck.

 

[yangdayong53]

Hi,
Yes.F0911 has too much index .Maybe jde need them all.
One way on as400 is you must regenernate it if you want better
performace.
regards



----- Original Message -----
From: "scottw" <[email protected]>
To: <[email protected]>
Sent: Tuesday, January 08, 2002 4:48 AM
Subject: RE: OneWorld Indexes


is
where
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=0&Board=OW&Number=
26129

 

[Carl_Fisher]

Except for a Disk Space overhead, why is your DBA so worried? How will she
know which ones are never used?

F0911 is a massive and very important table in JDE, after the Address Book
it is the most central part of OneWorld, break that and you break the Whole
System.

Indexes are called directly by JDE ERs and so need to be kept intact. Most
other people ask if they can add indexes to improve UBE performance or
QBEs!!

As far as I understand Oracle, as long as you run regular Optimisation
scripts, these indexes should not pose a performance problem, so is saving
the disk space so important?

You bought OneWorld as a package, if the indexes are a problem get JDE to
change them in a future rewrite. If you wanted to write and tune databases
you would have written your own application.

Yes OneWorld is a compromise, one size fits all solution, but it works
moderately well on all platforms from AS400 DB2 to Sun Unix Oracle via NT4
SQLServer, better that than works well on One Platform only, no choice (if
you want that buy World, tuned for AS400 only).

Oracle has lots of nice tools for tuning databases, but OneWorld will
continue to ignore them, to remain Platform Independent!

You will see from the indexes that some are of the form key A,B,C then a
second index key A,B, C, D, E, your DBA will know that the second index
makes the first redundant in Oracle terms, but not in other Database
systems, and both indexes are coded into OneWorld, the table definitions are
used by business functions and ER, a query is formatted in JDE then sent out
via the middleware, this ensures the same result on any platform.

I would suggest changing Indexes is a place you do not want to go. If you do
go there you must do it from within JDE, otherwise the Database will not
look like the model held in OneWorld, and chaos will ensue.



OW733.3 Xe SP 14.2
Enterprise Server - Intel NT + Oracle 8.0.6
Client - Citrix TSE + 4 NT PC's for development

 

[kf]

I had thought that all the OW applications referred to was the OW index
definitions as opposed to pointing directly to an index at the database
level. Again my assumption was the respective query optimizer chose the
index (or table scan) most suitable to the query, and this was not
selected by OW on any platform. As technical people we are restricted
to the tuning that we can make to specific queries passed by OW via the
middleware, so we are forced to tune the database to accommodate these
queries
We have had a similar request from one of our clients, with an
exceptionally large F0911 and we suggested that they selectively delete
and create indexes to match their specific queries as they were only
using a small component of the financial module. The only issue
occurred was during an upgrade whereby all the missing indexes were
regenerated as expected.
Having huge numbers of indexes on a file may increase performance of
queries, but hamper insert performance, which can become an issue if you
are doing high volume posts.
OneWorld is designed to be all things to all people, thereby the indexes
are defined to account for all possible OW queries to the table in
question. But these are not set in stone, and there is no black art in
changing them. The index definitions are held in OW (you can also
script them in a DB level) and can be regenerated if the results aren't
favourable. But obviously proceed with care, and ideally do a bit of
testing in PY before making the changes to PD
Maybe a better request for JDE would be to adapt their middleware to
better utilise the functionality of the underlying database, like
datatypes commit/rollback etc.
I would agree that unless there is an imperative to change the structure
of your database you should leave it alone, as it will be easier other
people to get up to speed on your system. But systems are never static,
and as long as you have a functional backup, I wouldn't be unduly
worried about making those changes.
Let Armageddon ensue...

Regards
Kieran Fitzgerald

 

[Larry_Jones]

Re: RE: OneWorld Indexes

Well stated Kieran.

Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials