Performance in Oracle insert to table from select with function calls

Question

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?

Obviously the problem are the function, so please show us their code. Perhaps RESULT_CACHE may help. — Wernfried Domscheit
– Wernfried Domscheit, Commented Aug 20, 2018 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
– Dee, Commented Aug 20, 2018 at 10:17
Just note, enabling RESULT_CACHE would require a change in function code. — Wernfried Domscheit
– Wernfried Domscheit, Commented Aug 20, 2018 at 11:00

Cromm · Accepted Answer · 2018-08-20 11:02:09Z

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.

answered Aug 20, 2018 at 11:02

Cromm

3511 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Performance in Oracle insert to table from select with function calls

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related