On Oracle there’s a kind of cache that works with scalar subqueries (look below for a simple example of a scalar subquery) which prevents the database engine to repeat unnecessary work to get the same result again and again.
SELECT ( SELECT sysdate FROM dual ) FROM dual
But while this seems to be less than useful with pure Oracle SQL when you throw some Oracle PL-SQL into it may be a great performance tool.
So, try this: on your favorite IDE (I may suggest Eclipse with the Toad Extension) create an Oracle stored procedure that gets one or more parameters and returns any value. Now create a SQL Select with a few number of results and call our new Oracle procedure from its select list. Observe how many times our Oracle procedure was called (you may use profiler to do so or do it manually).
I’ll spoil the result for you: as many times as rows returned.
Now, on the same query, call the Oracle procedure from inside a select … from dual block and observe the result.
Have you seen a lot less calls made to the procedure? This is because Oracle will cache every result from the subquery and use it whenever it gets the same parameter value.
This started to be a useful SQL tool as a scalar subquery with the same parameters would never return different results from the same transaction. Extending it to PL-SQL turned out to be a great addition.
I’ll leave you with a useful link if you want to know more about scalar subqueries.
Use it wisely and don’t forget to test for benefits for any specific case though.