1

We have a customer who has created a view in Oracle that performs badly and looking at the SQL for the view reveals that they're using a function call in the WHERE clause of the view definition. This function is fairly intensive and includes DELETEs and INSERTs.

My gut feeling is that this isn't a good idea but I'm not sure how to express exactly why...?

Will the function get executed every time someone queries the view?

Would a materialised view with a daily refresh (for example) make more sense, so the function was only executed once per day? (I guess that would depend on whether the data needed to be bang up to date all the time...?)

Thanks

Improve this question
3
  • 1
    A function called from SQL could only be doing inserts and deletes if it's declared with pragma autonomous transaction, otherwise it would get ORA-14551. Why would they want a view that does a lot of work every time it's queried (which would happen, as a view is just a stored query; possibly once per row depending on how it's referenced)? Commented Jan 13, 2016 at 12:32
  • Thanks @alex-poole. I know, it's crazy...it is already defined as an autonomous transaction but I'm trying to work them round to removing it from the view definition and creating a separate process. D'you know how often the function would get executed as it currently stands? Would it be for every user that accesses the view? Commented Jan 14, 2016 at 10:32
  • It depends a bit on how it's defined and called (is it deterministic, for example?) but if it's in the where clause it's probably being called multiple times by every query against the view by every user. Even if the view is only queried once a day by a single user it's still... unusual... at best and probably still called multiple times. A separate process, maybe called via a job, makes much more sense. Or possibly a procedure that checks if the heavy work has been done for that day, does it if not, and returns the same data as a the view via an OUT sys-refcursor, say, Really depends. Commented Jan 14, 2016 at 10:38

1 Answer 1

Reset to default
4

Calling a function in the WHERE clause of a SELECT statement is Bad News. First and foremost the function will be called for many rows which do not satisfy the conditions of the query because the function is being used to do part of the filtering. I once had a SELECT statement which looked something like

SELECT t.TRANSACTION_ID, t.SOME_OTHER_FIELD
  FROM TRANSACTIONS t
  WHERE MY_PACKAGE.IS_SALES_TRANSACTION(t.TRANSACTION_CODE) = TRUE AND
        X <> Y AND
        Y <> Z

and the MY_PACKAGE.IS_SALES_TRANSACTION function was similar to

FUNCTION IS_SALES_TRANSACTION(pTransaction_code IN NUMBER)
  RETURNS BOOLEAN IS
BEGIN
  IF pTransaction_code IN (10, 11, 12, 13) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END IS_SALES_TRANSACTION;

My little query took about 15 minutes to run, which made no sense to me because the query only returned about 5000 rows out of about 10,000,000 in the table. After some work instrumenting the code I found that the function was being called several MILLION times and was taking up most of that 15 minutes of execution time. Changing the SELECT statement to 

SELECT t.TRANSACTION_ID, t.SOME_OTHER_FIELD
  FROM TRANSACTIONS t
  WHERE t.TRANSACTION_CODE IN (10, 11, 12, 13) AND
        X <> Y AND
        Y <> Z

resulted in the cursor completing in less than a second.

Best of luck.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.