Archive

Archive for February, 2011

Using Dual to reduce function calls (and getting your 5-a-day)

February 7, 2011 1 comment

An introduction to an interesting feature of
Oracle SQL through the medium of fruit…

 

 

Make a FRUITS table.

CREATE TABLE fruits (fruit_name varchar2(30));

Put in 5 bananas, 7 apples and 3 blueberries.
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('banana');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('apple');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');
INSERT INTO fruits VALUES ('blueberry');

Create a sequence for keeping track of the number of times our function below is called.
CREATE SEQUENCE seq START WITH 1;

Make our function which returns a fruit’s colour (and increments our sequence each time it runs).
CREATE OR REPLACE FUNCTION get_colour (p_fruit_name IN varchar2)
RETURN varchar2
IS
l_num number;
BEGIN
SELECT seq.nextval INTO l_num FROM dual;

CASE p_fruit_name
WHEN 'banana' THEN RETURN 'yellow';
WHEN 'apple' THEN RETURN 'green';
WHEN 'blueberry' THEN RETURN 'blue';
END CASE;
END get_colour;
/

Get the colour of each fruit in our table.
SELECT get_colour(fruit_name) FROM fruits;

QUESTION ONE: What will the following return?
SELECT seq.nextval FROM dual;

Well, there are 15 rows in our table so we’d expect the function to have run 15 times. And indeed it has: seq.nextval will show a value of 16.

Now, let’s reset the sequence for the next experiment.
DROP SEQUENCE seq;
CREATE SEQUENCE seq START WITH 1;

And again get the colour of all of our fruits using our function but this time wrapping it inside a SELECT FROM dual.
SELECT (SELECT get_colour(fruit_name) FROM dual)
FROM fruits;

QUESTION TWO: What will the following return now?
SELECT seq.nextval FROM dual;

Well, we might be tempted to think that the function would have run 15 times as before and hence our sequence’s nextval would now be 16, as before. However, this is not the case. You’ll find the value returned is in fact 4, meaning that the function ran 3 times.

What happened there?
How can the function have run only 3 times if we gave it 15 bits of data to process (i.e. 15 fruits, one for each row from our table) and it returned the correct answer in each case?

The answer is Scalar Subquery Caching whereby the result of a “SELECT some_function(x) FROM dual” will be remembered for x as soon as it is run once. Hence, the function need only be run for each of the different fruits in the table and since we have only 3 different fruits (blueberry, apple and banana), the function is only run 3 times.

Tom Kyte talks about it here.

Nifty, eh?

Advertisements
Categories: SQL