Hi!
The Result Cache is a newer structure for Oracle11 and newer databases. This feature remembers the invariables and return values across sessions for your applications! This feature can seriously cut down on repeat executions to common functions used by your applications.
Lets start with RapidSQL. I used the ‘Create Function’ code template found under Edit à Paste SQL Syntax
Working from this template, I created a simple function that displays the DNAME from the DEPT table when the DEPTNO is passed into the function.
Line 3 has the RESULT_CACHE compiler option in it. This function will save each unique invariable and the returned data item. ANY session using this database can now retrieve the cached return data item when using a previously-accessed in variable item.
My example will use DEPTNO. I will supply a valid DEPTNO and you will see the “Function Executed” line at line 12 will display just once…and the other 4 times…the return item is accurate but the function was NOT executed…the code at line 12 did not appear.
I log into another session (using SQL*Plus), and using the same invariable to our function, the function does not execute.
I make a change to the underlying DEPT table…I add a row.
You can right click on the DEPT Table (under tables) and select Generate à Insert statement. Watch the webinars…you can see me use RapidSQL in action…along with code templates.
This blog I want to show how RapidSQL can improve your coding productivity and technique by using its templates. A template saves me from having to look up the exact syntax.
To use, simply put your cursor on your code where you want a template and use the key strokes CTRL + Space bar. This brings up the template popup window:
I reran the function we created and you can see the function executed once. Inserting the row (any DML on the table) flushes the Result Cache and the function executed once, reloading the Result Cache with the invariable and the return value…and the process then simply reloads all over.
The Result Cache saves function executions. IF the same invariable is submitted, Oracle11 and 12 simply look up in the memory structure the result from the prior execution and returns that. IF the invariable is not found, the item is added to the Result Cache.
Result Cache is useful for those tables, such as tax tables, the 50 United States names, city names, etc…items that don’t change much.
Result Cache is NOT to be used if your function may return different values based on outside influences. Be careful out there!
I hope this technique helps you in your day-to-day use of Rapid SQL.