Table indexes, ESUs and indexes created externally on the DB

[johndanter]

Hi list,

I have a question on indexes, both JDE defined and also externally defined indexes and tables that maybe in an ESU.

We have many bolt on external systems that use PLSQL, java etc and have thus created many new indexes outside of E1 in SQL on the DB, to satisfy queries etc for the external systems to use.

My worry is if we have table A with 10 JDE defined indexes that you me and everyone using E1 also has, yet we here create 3 more to satisfy our external systems also using table A on the DB, what will happen if table A is present in an ESU to now give that table 11 indexes?

Would our 3 custom external indexes be wiped?

I'm thinking yes and am trying to get our other development team to start thinking about the indexes they create.
If they are created outside of E1 we have no record of that in E1 OMW tables and I am worried an ESU could wipe the external indexes.

If the new indexes in the ESU are merged, then that's fine. Yet I still have concerns over the fact I may actually need to create an index in E1. Then I do know the 3 custom indexes would disappear

Any thoughts?

Thanks

John

 

[brandonkirsch]

Unless the ESU does a "generate table" command - which completely drops and recreates your table - your indexes *should* be left in place.

It's a bit risky, but should be easy enough to test in DEV. I would be interested to hear about your results.

I still think there are several good reasons for your development team to document anything they change, especially in the cases like this where they are bypassing OMW. If the indexes are important enough to be created in the first place, they are important enough to be written down or saved in a repository of some sort.

 

[JMR]

Hey John,

I don't know if ESU indexes are merged or not, but like you said, even if you have to add an index via OMW, you will wipe-out any non-E1 indexes. So you really need to be able to identify those non-E1 indices.

You can do this using a SQL tool and the following tables:

F98713
sysobjects
sysindexes
sysindexkeys (if you want to compare any column-level differences between E1 and DB)

You should be able to join those tables in such a way as to list indexes in the database that are not in E1. Sorry, I don't have an example for you right now, but if I find time, I will try to find one that I've used in the past.

 

[Larry_Jones]

Typically an ESU is not going to regenerate (wipe) a table unless its something like a temporary work table, etc.

What you're discussing is more of a concern for upgrades where a schema change forces a table conversion. In that case you will lose your outside indices.

Just make sure that the developers document them and move on ;-)

if the developers won't document them take away their privileges to create them.

 

[peterbruce]

John,

Larry has made an excellent point. Regenerating the table will remove all the data. If there was a need for external access to a table which held enough data to make additional non-JDE indexes worthwhile then it would be highly unlikely that such a table would be dropped and regenerated during the application of an ESU. As Larry said: "What you're discussing is more of a concern for upgrades ..."

This raises some good points for ESU application and upgrades:

1) Keep an external record of all changes to standard JDE objects as they will be lost in an upgrade.
2) Make sure you know what the application of an ESU will do. Tables indexes as discussed here would most likely not be impacted by applying an ESU, but there are other modifications to standard objects that may be impacted.

 

[johndanter]

Thanks for the replies guys.

I guess the ESU would never need to regenerate the table and risk losing data unless the ESU adds a new column.
But indexes can be added and the indexes regenerated at any time without losing the data (as you need the data there for the index anyway )

So it could happen. I'm just curious if anyone has has issues with this and external indexes.

How do you guys do it?
Do you have external systems accessing the E1 database at all? Have you ever made any new external indexes to assist the external systems?

This table F984052 seems to list ESUs that have affected a table.

 

[JMR]

John,

This should give you a pretty good idea of DB indexes that are not in E1. If memory serves, I think there is a chance of false positives. Joining to the correct index is not trivial... on SQL server anyway. I notice you're on an Oracle DB, so I can't speak to the tables they use for the DB metadata (sysobjects, sysindexes, etc). I imagine there are some differences in valid keywords also (CASE, RIGHT, CHARINDEX, etc). Sorry, I don't have an Oracle equivalent.

select j1.*
from (


    select 
        O.name as TableName,
        I.id as ObjectID,
        I.indid as IndexID,
        I.rows, 
        I.name as IndexName, 
        
        /* --------------------------------------------------------------
          Use the Index name to extract the Index ID that corresponds to
          the E1 index ID.  
          ---------------------------------------------------------------*/ 
        CASE RIGHT(I.name, LEN(I.name) - CHARINDEX('_', I.name))
            WHEN 'PK' THEN (
                /* get ID of primary key index */
                select cast(tpinid as char(2))
                from odfdevsql.ps_dv810.dv810.f98712
                where tpobnm = O.name AND tpprmf = '1')
            ELSE RIGHT(I.name, LEN(I.name) - CHARINDEX('_', I.name))
        END as CharIdx,
        RIGHT(I.name, LEN(I.name) - CHARINDEX('_', I.name)) as CharIdx2
    from sysobjects as O
    join sysindexes as I
        on O.id = I.id
    where I.keycnt > 0 and I.rows > 0


) as j1


left outer join ps_dv810.dv810.f98712 as OW
    on j1.TableName = tpobnm and CharIdx = cast(tpinid as char(2)) 


-- get missing indexes from E1
where tpobnm is null


order by TableName

 

[johndanter]

Cheers guys and thanks for the input

A CNC friend of mine says that E1 will NOT drop any external indexes, just the ones in the ESU and then recreate them as the ESU according to the table spec.

So any external or new ones will remain as the table is not dropped in it's entirety.

I'll keep all this info handy and safe

 

[BBritain]

Hi guys,

I think I need to understand this before we write this off as "done" or "answered". I am just seeing this thread for the first time, so I was in "Oh crud!" mode as I am reading through this.

First, I think the only way for an external index to get "blown away" is if the E1 index has exactly the same name and therefore won't (get blown away) with an upgrade or ESU. IS THIS CORRECT?

Second, John, you state that you if you create a custom index that "Then I do know the 3 custom indexes would disappear". I don't think that even this is correct, again unless the index name conflicts with one of the 3 custom indices. IS THIS CORRECT?

My understanding is that many db optimizers will create "many" external indices in order to speed up processing and the db is responsible for which index get used anyway (and not E1). (the way I heard it, is that E1 can suggest an index, but the db may or may not use an exact match or even have a better index) Is this correct?

Ben again,

 

[Larry_Jones]

Ben,
I believe that during upgrades a table conversion creates a new table (such as F4211_UPG) following the JDE table specs. Data is copied/written to the table until completed. At which point original table is dropped and new table is renamed (F4211_UPG to F4211). This has the effect of "blowing away" any externally defined indices.

Correct me if I'm wrong.

 

[JMR]

I think one of the issues here is with adding a new E1 index to a table that has external indexes defined at the database level.

In OMW, when you do a 'Generate Indexes', what is this actually doing on the DB side? Is it dropping all the indexes and regenerating them? That would be my guess, but I am not sure.
Best case scenario: it drops only the indexes that are defined in E1 and regenerates those. I guess this is what it should do and then there is no problem for this scenario.

 

[johndanter]

I may fire this question off into the CNC forum to see what they have to say?

One of my CNC mates said E1 will only go through all the indexes in the ESU, it should not touch any new customs ones.
Thankfully here we name them TABLENAME_GTSN N being an incremented number.

Sorry to open a can of worms

 

[craig_welton]

Hi

Generating Indexes in OMW will drop all JDE "known" indexes and re-create them, so external ones are left alone. As a side note, check out B9800200

Craig

 

[DBohner-(db)]

Hi John,

The following Oracle Document does a good job explaining some issue with creating Custom Index within JDE:
- E1: TDA: Custom Indexes on EnterpriseOne Table and Index Changes in Upgrades or Software Updates (ESUs) (Doc ID 648417.1)

Basically, the idea is to create, then delete faux indexes - until you have a buffer of at least 10. JDE names the indexes Incrementally. So, when an ESU or Update hits the system, any new JDE Indexes could overwrite custom indexes, if the procedure is not fully followed. When this occurs, your fetches that used the original custom index will no-longer work as expected (the BC AddressNumber = TC AddressNumber might, now, looks like "BC AddressNumber <= TC AddressNumber".

P95150 allows you to look at E1 Indexes and Externally Defined Indexes. Additionally, it allows you to create a single index, so you don't have to rebuild all indexes over your F0911.
Look for Oracle Document, titled: Dropping indexes using P95150.

I, strongly, recommend my customer match the externally defined indexes into the JDE File Spec, even if they don't create them there. At some point and time - they will want to use them internally (or the table will be regenerated).

So - my two pennies in with the rest of the change.

(db)

 

[JohnDanter2]

Wow
Well this has come around full circle
We've just upgraded from 9.0 to 9.2 and part of our retrofit was to add all externally defined SQL indexes to the E1 tables themselves.

We've done our upgrade by migrating over 9.0 DB to the 9.2 system and it appears we didn't run the R96 jobs to create E1 indexes or extra columns from E1 metadata specs. our 9.2 can be slow and I'm wondering if this is a cause

 

[peterbruce]

Wow
Well this has come around full circle
We've just upgraded from 9.0 to 9.2 and part of our retrofit was to add all externally defined SQL indexes to the E1 tables themselves.

We've done our upgrade by migrating over 9.0 DB to the 9.2 system and it appears we didn't run the R96 jobs to create E1 indexes or extra columns from E1 metadata specs. our 9.2 can be slow and I'm wondering if this is a cause

Hi John,

Missing indexes is a possible cause of your 9.2 running slow. There are other possibilities too. Your upgrade partner, if you have one, should be able to point you in the right direction. You or your upgrade partner will probably need to look at the following, as well as other things:

- A comparison of indexes and table structures between the 9.0 and 9.2 databases (I used to have SQLs that did some of this work).
- Check the 9.2 database server specs (minimum specs, particularly RAM)
- Check the 9.2 enterprise server specs (minimum specs, particularly RAM)

 

[TFZ]

Just a heads up, if you're on MS SQL for back end, we're finding issues with the optimizer not using indexes on certain tables (F0902 and F986110 were the big ones so far.) You'll see the long running statements in activity monitor. We've had some luck enabling legacy cardinality estimation, but we've also just generated the suggest indexes in some cases. If you really think about it, that index over GLCO, GLFY, and GLCNTRY on your 100 million row F0902 used by countless reports.... Or the BIP polling if you have multiple ent boxes running 5 BIP kernels constantly querying, it sort of starts to make sense why modern software just says, meh - may as well do a full table scan!

Also helpful -
You can flip the switch for long running queries to be logged in server manager for the ent box as well (it will log both UBE and kernel processes that take over X seconds, you define the threshold and I haven't noticed overhead.) On your ent server, Database -> Advanced -> Query Execution Time Threshold.

I don't always blindly regenerate all indexes - really depends on the customer, I'll run the check between the specs and the database and make decisions based on that as a starter, and go from there. Especially for large upgrades, it can be hard to gauge just what was custom developed sometimes (I've seen some real crazy stuff over the years, and all of it's "very important and executives could not live without it.")

 

[Stank1964]

I think that we can say that adding indexes using the JDE tools should be considered best practice.

 

[BOster]

I think that we can say that adding indexes using the JDE tools should be considered best practice.

Probably is best practice. However, just to avoid any confusion, JDE doesn't give any SQL index hints. So whether the index is created via JDE tools or whether it is created in the DB directly it will have zero effect on the DB's query execution plan.