Using the RESULT_CACHE hint

Khaled Alkhudari
A free video tutorial from Khaled Alkhudari
Sr Oracle Developer/ Sr system analyst
4.5 instructor rating • 7 courses • 31,464 students

Learn more from the full course

Oracle PL/SQL is My Game: EXAM 1Z0-149

Become Oracle PL/SQL Developer / Covers ORACLE university track 100%. All the presentations & scripts are attached

21:51:49 of on-demand video • Updated January 2020

  • The full track of Oracle university Exam 1Z0-144 : Program with PL/SQL
  • Oracle Database 12c Architecture
  • Downloading and installing oracle database 12c in details
  • Overview of PLSQL
  • Declaring PLSQL Variables
  • Writing executable statements
  • Interacting with Oracle DB Server
  • Writing Control structure
  • Working with Composite Data Types
  • Using explicit cursors
  • Handling Exceptions
  • Creating Procedures
  • Creating Functions
  • Creating Packages
  • Working with Packages
  • Using oracle-supplied Packages in Application Development
  • Using dynamic SQL
  • Design consideration for PLSQL Code
  • Creating triggers
  • Creating Compound, DDL, and Event Database Triggers
  • Using the PLSQL Compiler
  • Managing PLSQL Code
  • Managing Dependencies
English [Auto] Hello everyone will come back in their previous lecture. We understand what is the meaning of battle and the score. And this lecture. We will learn something very nice using that cross section and sequel function result. Gosh. So actually this is an option we can use it with function to understand this topic. Let's go to the exercises and I will describe for you everything. Now this is the exercise. Create or replace function get underscores some underscores underscore that. So in this function I will return the sum of salaries for any department and this function taking the department ID as number return number. After that I get this word result underscore gush and I will describe for you what is the meaning of this as the underscore number begin. I have this select segment Sillett sum of salaries into this variable from employees were department ID equal dept underscored ID which is the parameter and I will return that he said. It's very simple. So I will give to the function the department ID and it will return for me the sum of salaries and Tebbutt employees for this department. So now I will create this function I will connect to the H R user you will find your function get some cell comp.. Now what is the meaning of results underscore gosh this mean each time I execute the function the result will be stored in a cache in memory. For example I make sealift the function I give its value then from module I would execute this. It give me four thousand four hundred. So this means the value can give me the result four thousand four hundred and it is stored in a cache. OK let's back again. I will execute the function. Get underscores some size up to 20. I will give it to 20. I will execute this and the result is 19000. So when I give it 20 it give me nineteen thousand. Now I will execute the function again. I will give it 30. I would execute this and this is the result. So when I give barometer 30 this is the result. So actually all these values stored in a cache. So now when I make select some small dipped 10 from a jewel in this case it should be faster because the result is stored in the cache. So this means Oracle server will bring this value data without executing the code inside this function. So I suppose this function contains a very complicated so statement. So in this case Oracle will bring that as a directive from the cache. And this is across all sections. So this means that you will save a lot a lot of times. So now let's go to the presentation using that cross section as sequel function as Allagash each time result. Gosh be as equal function is code with different parameter values. Those parameters and their results are stored in cash and I show you this in the Excel sheet. The function result cash is stored in a share of the global area SGA. Make it available to any. Remember to an decision that runs your application. Subsequent calls to the same function with the same parameters uses the results from the cash and I show you this in the example. So it improves the performance and the scalability use with functions that are called frequently and depend on information that change infrequently. So actually all these points I describe it for you in details in the exercise now are very important not know. Number one is that data base object that used to compute the value changed then a result pre-computed. This is a very important note. For example suppose that I adding new employees in this table right. I add the new employees with a new salary. So in case any changes happen to the original table that calculation will be calculated again. So don't worry about this point. Oracle will figure by himself. Another note if function execution result in an unhandled exceptions the exceptions result is not stored in the cache. So case if you have exceptions this means the result of the function will not be stored in the cache. So these two notes are very very important. Now let's go to the exercise. There is something called relies on and you will mention the table. And this is optional. This option has become absolute since virgin olive in general is to the database. Figures out where the function relies on so much need for this but in case you see this I would describe what is the meaning of this. You can still include that it lives on close but it will for documentation purposes only. So I will create the same function to create replace function and this function taking parameter as number return numbers. Gosh then I make these sentences generalize on employees. So what is the meaning of this. This means that I am telling the Oracle server in case of any changes happen to this table. Please please compute the calculation again. But this is not important at all because starting from 11 G. Oracle discover what is that changes by himself. So no need for this option. OK so this option is only for documentation so if anyone reads this function he will know that the main table Datatrieve that data is in please that's it. So never use this option relies on because starting from a live in G. No need for this option. So thanks for listening and see you in the next vid you.