Indexes

Jamie_Yates

Active Member
Hi

Quick question, if you specify to use an index on a select/Fetch Next at design time, does the database always use this index at runtime? And what happens if you select using a view where you cannot select an index?

We have recently seen an error "Failed to find Index 35 for table F4801" this is due to the index not being in F98713. That index is on that table so should run the query OK. I didn't know JDE was so tightly connected to the indexes.

Any info would be useful.

Regards
Jamie
 
Jamie,

JDE isn't tied very tightly to "database" indexes. The indices are basically used to identify columns needed for unique keys and to construct the SQL Order By clauses.
However, when your JDE code specifies an Index to use in a Select / Fetch, and the Index is not defined in the Table definition, then it gets upset.

This can happen when:
1. Oracle adds a table index and writes code utilizing that index but the ESU/Update fails to include the Table Definition change.
2. You (the customer) do the same but fail to promote the table and build/deploy a package containing same.
 
E1 won't always use the index that you've suggested in Event Rules no, but it's great starting point for it to decide.

Sounds like you need to get CNC and a DBA to check and then to generate all the indexes on F4801.
As if an index is there for you to chose in event rules, then it should be there on the database. Whether the DB uses it or not, isn't really under your control in event rules.

List the E1 TDA out for a DBA (I have 32 here) and get them to check they match the indexes on the DB. I know my site have many more created on the Oracle DB that E1 can't see.

Most sites mix and match E1 TDA indexes and Oracle DB indexes. I hate this approach as you lose track of what's where.
Most of the DB created indexes are usually the result of tuning exercises that suggest to build a certain index for performance. Whether you then come back full circle and build these in E1 is down to you.
I prefer it.
 
Last edited:
There can be a much longer discussion on how defining "Indexes" work in JDE.

Quick Notes:
- If you do a Select, using a specific index, you MUST use the same Index on the Fetch-Next(s) - or bad things can happen
- When you create a custom index over a JDE Table - ALWAYS make sure you create/delete at least 10 Dummy Indexes (or JDE will overwrite your custom indexes in a future ESU/Release - AND BAD THING WILL HAPPEN
- The Definition of an Index is used by the Select/Fetch to define the 'Requested (but not required)' columns and the Sequence
- "Failed to Find Index ##" is, often, a result of not promoting the table - after a new index is created
*Why the heck do you have 35 Indexes????
- You CAN Generate a single index (Research and UNDERSTAND P95150, before you try)
- The Database "Optimizer" will select the most appropriate Index, on the Database Side

And - others can append a whole bunch more....

(db)
 
There can be a much longer discussion on how defining "Indexes" work in JDE.

Quick Notes:
- If you do a Select, using a specific index, you MUST use the same Index on the Fetch-Next(s) - or bad things can happen
- When you create a custom index over a JDE Table - ALWAYS make sure you create/delete at least 10 Dummy Indexes (or JDE will overwrite your custom indexes in a future ESU/Release - AND BAD THING WILL HAPPEN
- The Definition of an Index is used by the Select/Fetch to define the 'Requested (but not required)' columns and the Sequence
- "Failed to Find Index ##" is, often, a result of not promoting the table - after a new index is created
*Why the heck do you have 35 Indexes????
- You CAN Generate a single index (Research and UNDERSTAND P95150, before you try)
- The Database "Optimizer" will select the most appropriate Index, on the Database Side

And - others can append a whole bunch more....

(db)
it may be late but curios to know this ? if we are done with simplified upgrade then obviously our custom index get pushed to last rite ? but still it may create a problem what is that dummy index you mentioned please elaborate to follow the same.
 
Back
Top