“Dual” sourcing functions

OK, sorry in advance for the misleading title. Couldn't resist the pun.

Patrick Wolf has long been on a crusade, educating developers about the dangers of calling functions–especially APEX's v(…) family of functions–in the where clauses of your SQL. See this post from 2006, for instance. The problem is that Oracle calls the function for every row in the potential result set, even if the parameter hasn't changed; for large tables, this can be deadly to your query performance. One way to avoid the issue is to call the function yourself, storing the result in a PL/SQL variable, and then using that in your queries; this works, but only if the function isn't reliant on the values in the rows themselves.

“But, wait!” I hear you cry. If the function does rely on the values in the table, then isn't Oracle's approach correct? The answer is, frankly, no. If a table has 20,000 records in it, and the column who's value matters to the function has four distinct values, the proper approach would be to call the function four times, not 20,000. So we can improve on Oracle's approach, but adding in a loop to call the function and store it for every possible value will just clutter up our code (and all the extra logic will still have a noticeable performance hit).

So, what to do? Well, Andy Tulley points out that we can “convince” Oracle to be a little smarter than it normally is by using inline views of dual (though he doesn't use quite that terminology). Dual, by definition, has a single row, so any function calls with it as the sole source of the query are run once. And Oracle is a lot smarter about caching subquery results than about function calls, so it only calls the subquery when the values referenced in it change–exactly what we want. Patrick can rest slightly easier (though his crusade, I'm afraid, will probably never end).

Of course, if you use this trick, you'll want to document why you're doing it–otherwise, those who come after you may not appreciate the magic, pull out the calls to dual, and bring everything crashing to its knees.