Search This Blog

Monday, January 06, 2014

SQL Query Result Cache

True statements about the SQL Query Result Cache:
  1. It can be set at the system, session, or query level.
  2. Cached query results become invalid when the data accessed by the query is modified.
  3. Conditions must be true for a PL/SQL function to be result cached
    1. It must not be defined in an anonymous block.
    2. It must have at least one OUT or IN OUT parameter. 

Types of query results cannot be stored in the query result cache:

  1. Subquery results
  2. Results of a query having the SYSDATE function
  3. The query on dictionary and temporary tables
  4. Queries having SYSDATE and SYS_TIMESTAMP SQL functions
  5. For Functions
    1. If the first time a session on this database instance invokes the function with a parameter value.
    2. If a session executes a data manipulation language (DML) statement on a table or view that was specified in the RELIES_ON clause of a result-cached function.

No comments: