Oracle Database Hints Result Cache
Example
Oracle (11g and above) allows the SQL queries to be cached in the SGA and reused to improve performance. It queries the data from cache rather than database. Subsequent execution of same query is faster because now the data is being pulled from cache.
SELECT /*+ result_cache */ number FROM main_table;
Output -
Number
------
1
2
3
4
5
6
7
8
9
10
Elapsed: 00:00:02.20
If I run the same query again now, the time to execute will reduce since the data is now fetched from cache which was set during the first execution.
Output -
Number
------
1
2
3
4
5
6
7
8
9
10
Elapsed: 00:00:00.10
Notice how the elapsed time reduced from 2.20 seconds to 0.10 seconds.
Result Cache holds the cache until the data in database is updated/altered/deleted. Any change will release the cache.
