11g new feather (8)

I’ve often wished that the Oracle database would provide a method to retain in memory the result set from a complex query that contains what I like to call reference information. These are data that hardly ever change, but must still be read and used across multiple applications - for example, a list of all country codes and their corresponding names for lookup when processing addresses for new international customers, or a list of all ZIP Codes in the Midwestern US.

Oracle Database 11g fills this gap with three new structures called result caches, and each structure has a different purpose:

• The SQL query result cache is an area of memory in the Shared Global Area (SGA) that can retain the result sets that a query generates.
• The PL/SQL function result cache can store the results from a PL/SQL function call.
• Finally, the OCI client result cache can retain results from queries or functions on the application server from which the OCI call originated.

11