Home

Description

The result_cache hint should be avoided as it can lead to unexpected results and performance issues. This hint is used to force the query optimizer to cache the result set of a query, which can lead to incorrect results if the underlying data has changed since the query was executed. Additionally, the result set can become stale if the underlying data is updated, leading to performance issues. Therefore, it is recommended to avoid using the result_cache hint and instead use other methods such as materialized views to improve query performance.

Key Benefits

• Performance: The result_cache hint can cause a significant performance decrease when used in the wrong context.
• Maintenance: The result_cache hint should be avoided as it can lead to unexpected results when changes are made to the underlying query.
• Accuracy: The result_cache hint can lead to inaccurate results if the underlying query is not up-to-date.

Non-compliant Code Example

CREATE OR REPLACE FUNCTION GetCustomerPhoneNumber (customerId IN INTEGER)
  RETURN NUMBER
  RESULT_CACHE RELIES_ON (CUSTOMERS)
AS
  phone_number CUSTOMERS.PHONE_NUMBER%TYPE;
BEGIN

  SELECT /*+ result_cache */  --Non compliant code (The result_cache hint is added)
	PHONE_NUMBER
  INTO   phone_number
  FROM   CUSTOMERS
  WHERE  Id = customerId;
  
  DBMS_LOCK.sleep(2);
  
  RETURN phone_number;
END GetCustomerPhoneNumber