SQL avoid calling same function twice in select statement

Question

I have a select statement calling same function two times for returning two different columns, Since the same function is called twice, it's creating a performance issue.

I want to call function only once and copy its value to another column. Is it possible in oracle ?

SELECT ID
    ,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "BLOCK" 
    ,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "MASK"
    from table 
    where ID=condition;

One option to consider is to add RESULT_CACHE to the function. — Jeffrey Kemp
– Jeffrey Kemp, Commented Nov 27, 2018 at 5:14

Ori Marko · Accepted Answer · 2018-11-20 06:54:57Z

6

You can use with clause to get data once

with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block 
from table
 where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

answered Nov 20, 2018 at 6:54

Ori Marko

59.3k26 gold badges157 silver badges259 bronze badges

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

Collectives™ on Stack Overflow

SQL avoid calling same function twice in select statement

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