Predicate Evaluation Order – Using Generic Fields

Predicate Evaluation Order – Using Generic Fields

I thought this worth talking about as I’ve been in discussions with a number of people recently around best practices for data types etc. The example in the discussion was the implementation of Descriptive Flex Views in Oracle E-Business Suite.

Background


As a bit of background for those who may not be aware, some standard E-Business Suite tables contains a number of generic fields which a developer can utilize to hold their own custom fields. For example, here is a descriptive flexfield against the documents attachment table FND_ATTACHED_DOCUMENTS.

DFF Definition

You can see I’ve created a new attribute against which I can hold some custom information. When I save and “freeze” that, I get a notification that the flexfield has been generated.

Compiling Success


 

Now if I look, I have an object which the application has compiled for me:

desc fnd_attached_documents1_dfv Name                  Null Type           --------------------- ---- -------------  ROW_ID                     ROWID()        CONTEXT_VALUE              VARCHAR2(30)   DEMO                       VARCHAR2(150)  CONCATENATED_SEGMENTS      VARCHAR2(150)  

The purpose of this is to provide future-proofing, context and data type management, and more simply just a meaningful column name in your query. On the table FND_ATTACHED_DOCUMENTS though the column is actually “ATTRIBUTE1” and the view maps it appropriately.

  SELECT ROWID, ATTRIBUTE_CATEGORY, ATTRIBUTE1,    ATTRIBUTE1   FROM FND_ATTACHED_DOCUMENTS 

The key thing here though is that the DFV manages your data types. So if I create a new segment of type NUMBER then we see something different in the view definition.

"New

SELECT ROWID, ATTRIBUTE_CATEGORY, ATTRIBUTE1, FND_NUMBER.CANONICAL_TO_NUMBER(ATTRIBUTE2),    ATTRIBUTE1 || '.' || ATTRIBUTE2   FROM FND_ATTACHED_DOCUMENTS"

So the discussion ultimately was around whether to do this in your code:

Select  document_id,         Attribute1,          Attribute2    From fnd_attached_documents;

or

Select d.document_id,        dd.demo,        dd.number_demo   From fnd_attached_documents d,        fnd_attached_documents_dfv dd
Where dd.row_id = d.rowid;

Now I have many, many arguments for using descriptive flex views (and very, very few against) however the one I am addressing here is predicate evaluation order.

Predicate Evaluation Order

When the optimizer encounters a statement in which there are a number of predicates, the order in which it applies those predicates under normal conditions is completely indeterminate. For example, given the following

Select *    From my_table  Where field1 = 'Banana'    And field2 = 'Yellow'; 

the optimizer can choose either one of the following strategies.

  • One
    1. Filter the data set where field1 = ‘Banana’
    2. Filter the results of (1) where field2 = ‘Yellow’
  • Two
    1. Filter the data set where field2 = ‘Yellow’
    2. Filter the results of (1) where field1 = ‘Banana’

So why does this matter and how does it relate to Descriptive Flexfields? Well, under normal circumstances it doesn’t matter, however when we are dealing with generic columns it certainly does. For example in the above, Attribute2 is defined as a numeric column however on the database it is a Varchar2 – it is able to hold non-numeric data.
So once we bring things like flexfield contexts into the mix, it’s very easy to end up with a generic data set that looks a bit like this:

IDField_NameField_Value
1 Banana Yellow
2 Apple Green
3 Orange Orange
4 Age 21
5 Height in Feet 6

Let’s create that:

Create Table xxjktest (  id          Number,  field_name  Varchar2(20),  field_value Varchar2(20) );  Insert Into xxjktest(id, field_name, field_value) Values (1,'Banana','Yellow'); Insert Into xxjktest(id, field_name, field_value) Values (2,'Apple','Green'); Insert Into xxjktest(id, field_name, field_value) Values (3,'Orange','Orange'); Insert Into xxjktest(id, field_name, field_value) Values (4,'Age',21); Insert Into xxjktest(id, field_name, field_value) Values (5,'Height in Feet',6);  Commit; 

So we are holding numeric data in a text field. So let’s find all age attributes with a value of 21.

Select *   From xxjktest  Where field_name='Age'     And field_value=21; 

On my system (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production), that works perfectly – I get the following result:

        ID FIELD_NAME           FIELD_VALUE         ---------- -------------------- --------------------          4 Age                  21       

What might not be clear though here is that there is a bug in the above code! It is only by sheer luck that this has worked as we wanted. Remember at the beginning of this section when I said that predicate evaluation order was non determinate? I’m not sure actually whether there is some “best guess” going on which will always try and “help” out the developer – however please don’t rely on it. This old post from Tom Kyte explains it a little, however things might have evolved since then.
Anyway, the optimizer in our case had two choices again.

  • One
    1. Filter the data set where field_name = ‘Age’
    2. Filter the results of (1) where field_value = 21
  • Two
    1. Filter the data set where field_value = 21
    2. Filter the results of (1) where field_name = ‘Age’

In strategy one, the first predicate restricts our data set down so that for all remaining rows the field_value must be numeric. There is then an implicit data conversion of that column to a number to apply the second predicate (remember in previous blog posts I’ve said how bad they are)… and of course this might not be a hard-coded predicate; it might be a join. In which case, applying functions to convert it can cause undesired effects on the plan anyway.
In the second strategy, the optimizer first applies the predicate to select all rows where the field_value is 21. See the problem? This data-set contains non-numeric data! And we can’t compare a numeric data type to a non-numeric data type. This can easily be demonstrated by forcing the order in which predicates are applied.

Select /*+ordered_predicates*/ *   From xxjktest  Where field_name='Age'     And field_value=21;                ID FIELD_NAME           FIELD_VALUE         ---------- -------------------- --------------------          4 Age                  21                                 Select /*+ordered_predicates*/ *   From xxjktest  Where field_value=21    And field_name='Age';     Error starting at line : 6 in command - Select /*+ordered_predicates*/ *   From xxjktest  Where field_value=21    And field_name='Age'      Error report - SQL Error: ORA-01722: invalid number 01722. 00000 -  "invalid number" *Cause:    The specified number was invalid. *Action:   Specify a valid number. 

Ooooops! As I said above, this isn’t unexpected. In fact the optimizer could use the first strategy for days, weeks, months or even years. Then one day could pick a completely different strategy (due to a change in stats or similar). I can assure you that debugging that will be nothing short of a nightmare!

So how does the use of DFV’s help us? Well, the views manage the data types for us when using contexts which gives rise to this situation of shared datatypes. We can guarantee that we will never get a non-numeric data type coming through a numeric column. This doesn’t of course only relate to E-Business Suite code though, any time you are using a column to hold something of a different data type you need to be very aware of potential bugs. I say potential bugs, because as you can see in the demo above, it doesn’t always manifest itself during development – it’s a very easy mistake to make, and quite a common one.
If I did want to continue using the data model in the example above, I would either need to treat my 21 as a string (easy for numbers, not so easy for dates and other complex data types), wrap it in a view which deals with the correct data type according to the value of field_name (which is the purpose of the descriptive flex view) or re-write something like this:

With x As (   Select /*+materialize*/ *     From xxjktest    Where field_name='Age') Select * From x Where field_value=21; 

We need to provide the materialize hint because the query can get re-written otherwise.

Error starting at line : 1 in command - With x As (   Select /*+ordered_predicates*/ *     From xxjktest    Where field_name='Age') Select * From x Where field_value=21 Error report - SQL Error: ORA-01722: invalid number 01722. 00000 -  "invalid number" *Cause:    The specified number was invalid. *Action:   Specify a valid number. 

So the moral of the story is please don’t mix data types, and if you really have to then ensure you are using the appropriate infrastructure around those columns to deal with it.

Leave a Reply

Your email address will not be published. Required fields are marked *

4 × four =

This site uses Akismet to reduce spam. Learn how your comment data is processed.