Solution: A function in the where condition

Of course, Martin Berger is correct once more.

  1. The function is obsolete and should be removed
  2. The function is not defined as deterministic, though it is maximal deterministic. As a consequence, one can not create a function based index, create no virtual column and no Extended Statistics.
  3. of course, it would make sense to use the result cache. In the function definition for this keyword is missing. We can not add it without changing the code.
  4. As a final comprehensive measure we could assign statistics to the function. The how to is described here Table Expressions, Cardinality, SYS_OP_ATG and KOKBF and here
    setting cardinality for pipelined table functions and code. Unfortunately this is not possible because an error in the where condition enforces an implicit conversion. Statistics assigned to function effective are not used if to_number (effective ..) which is actually called. Statistics associated with the original effective function are therefore ineffective.

Thus, the developer has destroyed all possibilities for an elegant solution due impressive incompetence and we need to fix the statement using hints. This was achieved through a sql patch

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s