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.
- http://orasql.org/2013/02/10/deterministic-function-vs-scalar-subquery-caching-part-1/
- http://orasql.org/2013/02/11/deterministic-function-vs-scalar-subquery-caching-part-2/
- http://orasql.org/2013/03/13/deterministic-function-vs-scalar-subquery-caching-part-3/
Footnote:
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.
Oracle Scratchpad 
Hi ,
All the examples of this I’ve found are where functions are used in the select list.
SELECT 1
2,
(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)
Etc?
Thanks
Comment by John — May 13, 2015 @ 11:32 am BST May 13,2015 |
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 |
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 |
[…] 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 |
[…] 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 |