Oracle SQL interesting feature

I bring to your attention a translation of an interesting post in my opinion about an unobvious feature of Oracle.


Create a table FRUITS.

CREATE TABLE fruits (fruit_name varchar2(30));

We fill the table with data: 5 bananas, 7 apples, 3 blueberries.

INSERT INTO fruits VALUES ( 'banana' );<br> INSERT INTO fruits VALUES ( 'banana' );<br> INSERT INTO fruits VALUES ( 'banana' );<br> INSERT INTO fruits VALUES ( 'banana' );<br> INSERT INTO fruits VALUES ( 'banana' );<br> INSERT INTO fruits VALUES ( 'apple' );<br> INSERT INTO fruits VALUES ( 'apple' );<br> INSERT INTO fruits VALUES ( 'apple' );<br> INSERT INTO fruits VALUES ( 'apple' );<br> INSERT INTO fruits VALUES ( 'apple' );<br> INSERT INTO fruits VALUES ( 'apple' );<br> INSERT INTO fruits VALUES ( 'apple' );<br> INSERT INTO fruits VALUES ( 'blueberry' );<br> INSERT INTO fruits VALUES ( 'blueberry' );<br> INSERT INTO fruits VALUES ( 'blueberry' );


In order to know how many times our function has been launched, we create a sequence.

CREATE SEQUENCE seq START WITH 1;


We write a function that returns the color of the fruit (input parameter) and increments the sequence as an indicator of its work.

CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)<br> RETURN varchar2<br> IS <br>l_num number;<br> BEGIN <br> SELECT seq.nextval INTO l_num FROM dual;<br><br> CASE p_fruit_name<br> WHEN 'banana' THEN RETURN 'yellow' ;<br> WHEN 'apple' THEN RETURN 'green' ;<br> WHEN 'blueberry' THEN RETURN 'blue' ;<br> END CASE ;<br> END get_colour;<br>/

We recognize the color of each fruit in our table.

SELECT get_colour(fruit_name) FROM fruits;


Question: What will return this request?

SELECT seq.nextval FROM dual;


So, in table 15 entries, then the function will be called 15 times. And since we are running seq.nextval , we can expect the result to be 16. Let's reset the sequence for another experiment.

DROP SEQUENCE seq;<br>
CREATE SEQUENCE seq START WITH 1;<br>

And again we use our function to get the color of the fruit in the table, but this time we wrap it with the SELECT FROM dual expression.

SELECT ( SELECT get_colour(fruit_name) FROM dual)<br> FROM fruits;


Question: What will return the request this time?

SELECT seq.nextval FROM dual;


It can be assumed that, like the previous time, the function will be executed 15 times and the request will again return 16. However, this is not the case.
We find that the number 4 is returned, which means that the function was called only 3 times.
What happened?
Why is the function performed only 3 times, although we give it every record in the table, and these are 15 fruits, and at the same time, in general, the query returns valid data?
The answer lies in the mechanism of caching the results of subqueries - Scalar Subquery Caching .
The result of the SELECT query some_function (x) FROM dual will be saved for each value of the parameter x .
Thus, in fact, the function will be performed only for different input parameters, and since we have only three different fruits (banana, apple, blueberry),
then the function will be executed only three times.
And here Tom Kite tells about it.

Note translator.
For completeness, mention should be made of the possibility to declare this function as DETERMINISTIC , then in the query
SELECT get_colour (fruit_name) FROM fruits; it will be executed only 3 times.