Oracle Database: Program with PL/SQL — Question 22

Identify the two correct scenarios where a function can be optimized using the function result cache feature.

Answer options

• A. A function which inserts multiple records into a DEPARTMENTS table as part of one-time data setup for an HR application.
• B. A function which accesses multiple tables and calculates the commission to be given to a sales representative based on the number of products sold by that representative.
• C. A function which deletes all the records from an EMPLOYEES_AUDIT table based on their LOG_DATE.
• D. A function which updates the SALARY of all the employees in an EMPLOYEES table by a fixed percentage based on their DESIGNATION.
• E. A function which calculates the factorial of a given number without accessing any table.

Correct answer: D, E

Explanation

The correct answers D and E can effectively utilize the function result cache because they involve deterministic calculations where the results can be reused without accessing the database. Options A, B, and C involve data manipulation or retrieval from tables, which makes caching less applicable since their results can change based on the underlying data.