Which two types of query results cannot be stored in the query result cache?

Which two types of query results cannot be stored in the query result cache? (Choose two.)

Which two types of query results cannot be stored in the query result cache? (Choose two.)

A.
subquery results

B.
results of a query having the SYSDATE function

C.
results of a query having the GROUP BY clause

D.
results of a query having the DATE data type in the WHERE clause

Show Hint

Leave a Reply 5

Your email address will not be published. Required fields are marked *

ja

ja

“A” could be valid, if we will think about subquery as temp table.

————–
You cannot cache results when the following objects or functions are in a query:

Temporary tables and tables in the SYS or SYSTEM schemas

Sequence CURRVAL and NEXTVAL pseudo columns

SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, and SYS_TIMESTAMP

Reply

PIERO

PIERO

B , D

date info are dynamic

Reply

PIERO

PIERO

ops, answer wrong ? i’ve seen the “Show Answer…”

it’s not immediate.

date info are dynamic, so, not useful to store in SGA…
BUT
a function based on a test of a “date data type” can return differents results along the time if the current_date is part of the test…
also if i admit, it’s difficult that it happens.

also “A” in effect is correct, a subquery is useful for its scope (create table, insert..), not useful to share in SGA)

so….. new answer

A , B

Reply

gelete

gelete

A.
Database Performance Tuning Guide
7 Configuring and Using Memory

7.6.2.1 How the Client Result Cache Works
The client result cache stores the results of the outermost query, which are the columns
defined by the OCI application. Subqueries and query blocks are not cached.

The Oracle Call Interface (OCI) client result cache is a memory area inside a client process
that caches SQL query result sets for OCI applications
The client result cache is distinct from the server result cache, which resides in the SGA.
OCI drivers such as OCCI, the JDBC OCI driver, and ODP.NET support client result caching.

docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#PFGRF985

B.
Database Performance Tuning Guide
7 Configuring and Using Memory

7.6.4.2 Additional Requirements for the Result Cache
You cannot cache results when the following objects or functions are in a query:
– Temporary tables and tables in the SYS or SYSTEM schemas
– Sequence CURRVAL and NEXTVAL pseudo columns
– SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP,
USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE,
and SYS_TIMESTAMP

http://docs.oracle.com/cd/E11882_01/server.112/e41573/memory.htm#PFGRF983

Reply