Oracle Scratchpad

February 8, 2015

Functions & Subqueries

Filed under: Oracle,Performance,Subquery Factoring,Tuning — Jonathan Lewis @ 4:12 am GMT Feb 8,2015

I think the “mini-series” is a really nice blogging concept – it can pull together a number of short articles to offer a much better learning experience for the reader than they could get from the random collection of sound-bites that so often typifies an internet search; so here’s my recommendation for this week’s mini-series: a set of articles by Sayan Malakshinov a couple of years ago comparing the behaviour of Deterministic Functions and Scalar Subquery Caching.

Although I’ve labelled it as “this week’s” series, I wouldn’t want you to assume that I’ll be trying to find a new mini-series every week.

Footnote 2:
I had obviously expected to publish this note a long time ago – but must have forgotten about it. I was prompted to search my blog for “deterministic” very recently thanks to a recent note on the OTN database forum and discovered both this note and an incomplete note about improving the speed of creating function-based indexes by tweaking hidden parameters – which I might yet publish, although if you read all of Sayan’s articles you’ll find the solution anyway.



  1. Hi ,
    All the examples of this I’ve found are where functions are used in the select list.

    SELECT 1
    (Select f(n) From Dual) n
    From Table;

    rather than

    SELECT 1,2,f(n) n From Table;

    However; does
    this equally apply when a function is used in the where clause?

    Select …
    From tables
    Where tab.column = function(tab2.column2)



    Comment by John — May 13, 2015 @ 11:32 am BST May 13,2015 | Reply

    • John,

      The “trick” of putting using “(select functioncall() from dual)” is just as valid for the where clause as it is for the select list.

      Comment by Jonathan Lewis — May 13, 2015 @ 11:36 am BST May 13,2015 | Reply

  2. Thanks for the response, that’s very useful – I use E-Business Suite a lot and I see a massive number of statements (both standard and custom) like this:

    SELECT some_columns
    FROM some_tables
    WHERE x.y = y.x
    AND a.b = b.a
    AND d.e = fnd_profile.value(‘A_PROFILE_VALUE’)

    So there are obviously some good gains to be made from wrapping that in a select clause.

    Surely given that any function in a SQL statement can be wrapped in a “select from dual”, the optimizer team would just put in a rewrite to do this, perhaps with a hint if you wanted to disable it for some reason. I’m sure there’s many reasons above my knowledge why that’s not a good idea. :)

    Thanks again

    Comment by John — May 13, 2015 @ 3:47 pm BST May 13,2015 | Reply

  3. […] posted a note a few days ago about read consistency, the Cross Session PL/SQL Function Result Cache, deterministic functions, and scalar subqueries. The intent of the article was to make clear the point that while you might think that declaring a […]

    Pingback by Read Consistency | Oracle Scratchpad — October 12, 2015 @ 7:44 pm BST Oct 12,2015 | Reply

  4. […] As you can see, my execution plan now has a View operation telling us that the Sort operation completed inside a non-mergable view; and we can see that the Buffers and the Used-Mem statistics for the Sort operation has stayed at the 948(+1 again) buffers and 2440K of the simpler query. Of course, the need for the data (i.e. sorted rowsource) to pass up the plan through the view operation means a little extra CPU at that point before we start executing the subquery. By a lucky fluke the hash collisions on the scalar subquery cache have dropped slightly because the data department ids are arriving in a different order – this can be a good thing but, as you will have seen in the linked article above on filter subqueries, the change of order could have been a disaster. (More articles on scalar subquery caching – and deterministic functions – linked to from this URL) […]

    Pingback by Best Practice | Oracle Scratchpad — December 1, 2021 @ 7:08 pm GMT Dec 1,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: