monline

I've found something interesting about the Oracle optimizer and its use of PL/SQL functions in query conditions (in the WHERE clause). It seems that if you refer to a column of a view in a function call, then Oracle tends to "merge" this call into the view and runs it on a lot bigger set of values, then you would think based on the views results. A NO_MERGE hint is of no use in this case.

Here's an example:

Let's suppose that this query returns 10 rows in 0.01s, however inside the view the table that contains the "some_column" column has several thousand rows in it.

Now let's see this one:

Let's suppose that the function call contains quite some logic and has significant execution time ("n"). In this case the query will not execute in n*10 time, but a lot slower, because the function call will not run only on the 10 rows that the view would otherwise return. Adding an /*+ NO_MERGE(v1) */ hint doesn't help either. You've to join another table to it and run the function call on a column of that one. Even in this case you'll need either a NO_MERGE or an ORDERED hint to make Oracle calculate the result of the view first and run the function only on the few records of the view.

Eg.

I find it rather interesting that a NO_MERGE on a view alone does not help. I think that the following should really do the job, but unfortunately it does not:

I'm sure that this is working as it is for a reason, but from a user's perspective it is annoying and not intuitive.

The same applies if you embed your query into another one ... so the following will not help you either. If you put the function call into the selected expression list, then it'll be run only for the records from the view.

So far so good. However if you try to use this and "cheat" on the optimizer, it won't work. In the following the function call is again "merged" into the view.