0

I am writing a query to calculate some values and save them onto a table on a periodic basis. The values I needs are being returned from a function call.

These values need to be saved on to a table, and I have a dbms schedule job to insert these to a table. The job executes the following;

BEGIN
execute immediate 'truncate table tbl1';
INSERT /*+ append*/ INTO tbl1(v1, v2, v3, v4)
SELECT
    function1(input), 
    function2(input),
    function3(input),
    SYSDATE
FROM tbl;
END;

The scheduled jobs runs for over 8 hours, for over 500k records! I understand the logic int he functions are time consuming, and there is the context switching in selecting from functions. Is there another way for me to achieve this, with better performance?

I am on Oracle 12c. Are there any suggestions on how I can make this job run faster?

sticky bit
  • 36,626
  • 12
  • 31
  • 42
Dee
  • 199
  • 4
  • 17
  • Obviously the problem are the function, so please show us their code. Perhaps `RESULT_CACHE` may help. – Wernfried Domscheit Aug 20 '18 at 10:13
  • @WernfriedDomscheit Thank you for the input. Unfortunately, I cannot alter the content of the functions.. I am only using the functions to built queries, and have to figure out something from my end. :( I will check on RESULT_CACHE thanks! :) – Dee Aug 20 '18 at 10:17
  • Just note, enabling `RESULT_CACHE` would require a change in function code. – Wernfried Domscheit Aug 20 '18 at 11:00

1 Answers1

0

Is the function complex ?

If not, can't the function be called after each insert into tbl and store the result in a new column ? So when the job is called, the function1, 2 and 3 are replaced by the colmuns where the value is stored.

Cromm
  • 328
  • 6
  • 25