Oracle With Clause Result Cache. Oracle 11g and above allows the SQL queries to be cached in the SGA and reused to improve performanceIt queries the data from cache rather than database. A common question with caching is whether the DETERMINISTIC option or the RESULT_CACHE is best.
The SQL-99 WITH clause is very confusing at first because the SQL statement does not begin with the word SELECT. If so Oracle by skip the fetch step of execution and call your rows directly from the cache instead of reading the rows from the daabase buffers. If you declare the function before defining it you must also include the RESULT_CACHE option in the function declaration.
To make a function result-cached include the RESULT_CACHE clause in the function definition.
11g SQL Result Cache vs times Ten hi TomA few monthes back i designed an architecture for one of our client. However invalidations work across instances. The SQL-99 WITH clause is very confusing at first because the SQL statement does not begin with the word SELECT. The Oracle SQL WITH clause will compute the aggregation once give it a name and allow us to reference it maybe multiple times later in the query.