A Function used in a Where Condition

The function is defined as:

FUNCTION effective (p_id IN VARCHAR2)
-- IF   p_id  = '0'
--    OR p_id = current_id
--    OR current_id = '0'
--   RETURN('0');
-- END IF;

and it is used like that:
Where effective(id) = 1 ...

How could you improve this code fragments?  Name all short comings.



  1. There are several dimensions to attack this code.

    Probably the most obvious is: by commenting most parts of the function, it always returns ‘1’.
    this will be tested in the WHERE part to be equal 1 – which is true.
    As we know it is always TRUE, we just can skip this test from WHERE and avoid the work totally.

    But that’s only part of the whole picture: Without special care the Cost Based Optimizer will estimate a cardinality of 10% for an unknown function. But as showed above, we will get 100%. This can lead to bad plans as well. Some background and ways to mitigate such a Problem here: http://www.oracle-developer.net/display.php?id=426

    If we can not change the SQL for whatever reason, maybe it’s possible to pre-calculate the return value for this function into a virtual column. Unfortunately this requires the function to be defined DETERMINISTIC – let’s hope we can change the function for that, when even the SQL can not be changed?
    If we can not create a virtual column – still RESTULT CACHE will help.

    Another strategy might to create a sql profile / stored outline / SQL plan management to
    tell the optimizer the correct cardinality AND order the joins & filters so that the set, which must be tested against effective(id) is as small as possible. – I’d like to reduce the number of context-switches between SQL and PL/SQL engine to a minimum.

    At the end the test effective(id) = 1 leads to a implicit type conversion, as effective returns VARCHAR2. It would be better to do effective(id) = ‘1’

    Liked by 1 person

    1. Hi Martin, great comment! Very much on target again. I will give the solution next week as always. Unfortunately we were talking Standard Edition here. Thus the profile did not work out.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s