Within the last week, Shannon Moir has had 2 great posts about EnterpriseOne indexes that have motivated me to evaluate our database:
- JDE slow, missing indexes? find it fast… fix it fast!
- Forget missing indexes, have you thought about UNUSABLE indexes
Both are very good and extremely useful. I had to do a little bit of adjusting and add a where clause or two for our instance but they worked great.
This is what I my modified code looks like:
select TRIM(TPOBNM), JDEINDEX from (select tpobnm, trim(tpobnm) || '_' || tpinid as jdeindex from PD900.f98712 where tpuniq <> 1 and not exists (select 1 from all_indexes where owner = 'PRODDTA' and trim(tpobnm) || '_' || tpinid = index_name) and exists (select 1 from all_Tables where owner = 'PRODDTA' and table_name = trim(tpobnm)) union select tpobnm, trim(tpobnm) || '_PK' as jdeindex from PD900.f98712 where tpuniq = 1 and not exists (select 1 from all_constraints where owner = 'PRODDTA' and trim(tpobnm) || '_PK' = constraint_name) and exists (select 1 from all_Tables where owner = 'PRODDTA' and table_name = trim(tpobnm)) ) order by 2 asc; select * from all_indexes where status <> 'VALID';
- Running the SQLs above to get a list of tables that need to be examined.
- Verify the specs in OMW.
- Check the number of records in the table – You don’t want to impact user experience trying to make things better.
- If there weren’t too many records, I generated the indexes using OMW.
Yes, I know Shannon did things a little cleaner using all SQL, but my SQL-Ninja qualifications are not where his are.
R9698713 – EnterpriseOne Index Compare and Regeneration
After getting halfway through my process, I remembered a handy, dandy UBE that does some of this for you: R9698713.
The R9698713 has the following Processing Options:
- Environment Name
- or Data Source Name
- Enter ‘0’ for Proof mode, ‘1’ to recreate ALL the indexes on the selected tables if there are ANY missing or mismatch indexes or ‘2’ to create only the missing and mismatch indexes on selected tables.
- NOTE: your best options here are either ‘0’ or ‘2’. ‘1’ is very dangerous!
- Summary report or list all tables
Be sure to run it in proof mode to evaluate what it will be doing. Also, be sure to check the record counts in the tables that it plans to write the indexes over.
P95150 – EnterpriseOne Index Selection Tool
The P95150 application allows you to drop single or multiple EnterpriseOne indexes at a time. However, it only allows you to drop indexes that are not unique or primary keys.
The following doc explains how to drop and regenerate EnterpriseOne indexes using the P95150: http://docs.oracle.com/cd/E14688_01/e1tools898pbr1/eng/psbooks/1tsa/chapter.htm?File=1tsa/htm/1tsa09.htm
Any tips or tricks when it comes to EnterpriseOne Indexes?