CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
John S John
John S John

Reputation: 422

Which is better for PL/SQL, IF-ELSE OR SELECT DECODE IN ORACLE

I was thinking of converting an if block into decode in my pl/sql function. Since we can't use decode directly in pl/sql, the plan is to use 'select decode() into variable from dual'. The existing if block is as follows:

if var1 = 'a' then
 var2 := 'x';

elseif var1 = 'b' then
 var2 := 'y';

else
 var2 := 'z';

end if;

the replacement decode could be:

 select decode(var1,'a','x','b','y','z') into var2 from dual;

The codeline could be reduced significantly(for similar larger blocks) with decode, but is it the better approach?

Upvotes: 3

Views: 9155

Answers (2)

XING
XING

Reputation: 9886

In Oracle PLSQL block , 2 types of engines works. First SQL engine and another PLSQL engine. Whenever you write a SQL statement in a PLSQL block, the switching of engine takes place and this phenomena is called Context Switching. The more context switching the less performant application would be.

When you do :

if var1 = 'a' then
 var2 := 'x';

elseif var1 = 'b' then
 var2 := 'y';

else
 var2 := 'z';

end if;

The statement is evaluated in PLSQL engine and no context switching occurs. But when you do :

begin
select decode(var1,'a','x','b','y','z') into var2 from dual;
end;

PLSQL engine changes the control to SQL engine and context switching takes places. So this operation would make less performant.

Upvotes: 5

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

I think the first approach is better.

Since There's no need to produce an extra overhead through querying by dual, especially in large applications. I mean no need to apply database for a problem which could be resolved inside app. If you look at execution plan you'll see a cost value of 2, for querying dual table.

By the way, it's an extra load for networking between db and app server.

Upvotes: 1

Related Questions