Bill Rehm, ATS Database Lead
You've analyzed the performance of your application or UBE, and after working with the DBA, you've all decided that adding an index to the F0911 is the best way to speed up your slow process. You tell your developer who goes in to Table Design Aid in OMW and adds the new index. They check in the table and the CNC promotes it to Production, after which a package is built and deployed. The only thing left is to generate the new index in Production. The CNC opens OMW, goes to Table Design Aid, clicks the Table Operations tab, and clicks Generate Indexes.
Within a few minutes, the calls start coming in: Everything is running slow. Processes are taking forever. Shipments aren't processing as fast as they should; product is being held up; trucks aren't rolling. What happened? This was supposed to speed things up!
What you probably didn't know is that the moment you click the button to generate indexes in OMW, the first thing it does is drop all the existing indexes on that table. That might not be a big deal on a small table with a few indexes, but it's a huge problem on a large table like the F0911, which ships with 25 indexes. Since that table is used by most operations in E1, the loss of all the indexes causes every application and UBE to slow down. That table is typically so large; it could take 24 hours to regenerate them all. Even worse, there's really no simple way to roll that back. You just have to let it continue until it's done.
Unfortunately, OMW does not allow you to regenerate individual indexes. It's an all-or-nothing proposition. This also makes testing possible indexes a very difficult task. Faced with this, CNC administrators schedule index generation after hours or on the weekend to reduce the performance hit.
There's a better way to get index changes applied to your system. It's significantly faster, it doesn't kill performance, and you only change what's necessary:
Create the indexes directly on the database.
Yes that's right! You open a SQL tool like SQL Developer and make the index changes directly on the database. In fact, you can create your database indexes without defining them in E1 at all. This can help if you want to test your changes before committing to anything.
You might wonder how E1 will know the indexes are there if you don't create them in OMW first. The truth is that E1 itself doesn't actually know or care what indexes are on the database. Behind the scenes E1 simply issues SQL statements to the database without any regard for indexing. Generally, only developers and CNC admins need those specs inside E1 to facilitate their work. The transactions flowing out of E1 to the database don't look at those specs at all. When a transaction arrives, the database decides what index to use (if any) and executes the statement in the way it thinks is best.
That's not to say you shouldn't create indexes within E1 - you certainly should. You're going to need them for ESUs, upgrades, and E1 development, not to mention other CNCs that may not know about your new secret. By creating indexes manually on the database, you not only save huge amounts of time but you can test indexes quite easily. If you create an index and it doesn't do what you thought, you can easily remove it and try another one without having to go back through the development cycle.
Remember this new, faster way to get index changes into your system:
Determine what index you need.
Add it directly to the database.
If it works the way you want, add it inside E1.
Don't generate indexes with OMW!
For assistance with JD Edward, please email us at inquiries@GetGSI.com.