Oracle not using E1 Index from Table I/O Select Statement

[richslaney]

Hi all,

Just wondered if any of you out there had any suggestions for me. I'm working for a client who heavily uses the JDE Contract Tables (ie F1721) for Service / Warranty Contracts for their products. Their E8.12 system is sitting on Oracle and is rather heavily moded.

In there 'customised' E1 system they store a product 'Activation Code' in the Lot Master (F4108) table which is then used as the only link back to the Contract Detail Table F1721 against the VR01 field. They have created an Index in F1721 over the VR01 column.

Due to the nature of the activation code (lots of F1721 records don't have a value until registered) and the volume of F1721 records (35million + ) the performance is awful.

I did a basic test with the Oracle DBA here and Oracle is doing what I thought it was. Instead of using the Index created over the VR01 field in F1721, Oracle is making up it's own mind, ignoring the JDE Middleware and doing a full table scan.... A full Table scan over F1721 which has 35 million plus records and 132 columns per record.

Does anyone have any ideas / tricks to get Oracle to use their index.

Before anyone says, I know the design is bad and that I could create an intermediate table by activation code and containing the F1721 keys to provide a primary key link, but this would mean additional mods to existing live objects (which maintain the Activation code)and therefore the extra cost and additional testing (therefore risk).

Any idea's would be appreciated.

Best Regards,
Rich Slaney
JDE E8.12 Technical Development Consultant.

 

[james_thurman]

Oracle uses a Cost Based Optimizer to select an execution plan for your query. The choice of which index to use or even if any index will be used is completely up to the optimizer. When we write a SQL statement, we can help the optimizer by using HINTS in the query but as you know JDE creates the query for you and we are not able to use that feature. So if we look at the data that you described, 35 million records with an index on a column that is mostly blank, there are some things to note. The optimizer will favor an index if the column has a high degree of cardinality. What that means is that it wants there to be a high percentage of unique values in the column. So a column that has all unique item number for example. Every row in the table has a different value = high degree of cardinality. A bad example would be a column like gender, where every column is either 'M' or 'F'. Not very many unique values! Or even worse, like your case where 99% are blanks and a very small percentage of populated values. In Oracle, you need to ensure you have good statistics and a histogram on the skewed column to help aid the optimizer in making a decision on how to select. You should also have your Oracle DBA look at a bitmap index for that column as they are better suited for heavily skewed columns - but the number of distinct values has to be fairly low (usually) or the clustering of the data in the table has to be well known. If the index was created in OMW using the JDE toolset, you don't have the fine degree of control needed to make specific indexes, so you may need to drop and recreate the index using SQLPlus... You will need to test all of this... a lot...

Another thing to note is that JDE makes use of bind variables when passing the SQL statement. So Oracle will look at the value the first time the statement is parsed and choose a plan based on that value. That same plan will be used over and over when the statement is executed until the statement is reparsed. So even with the statistics on the Histogram, you may not get the expected results every time.

 

[Adrian_Chimirel]

Rich,
Did you try to call a custom section (based on a business view over F1721) instead of using Table I/O Select?

 

[Alex_Pastuhov]

Please post the part from the logs with the actual SQL it uses and the Oracle plan...