Oracle Assignment vs Select Into

Question

I know this is micro-optimization - this is more a question of curiosity.

I am curious which is faster of the two options below:

1) :new.a := upper(:new.a);
2) select upper(:new.a) into :new.a from dual;

I didn't find any performance info here or here, though those are usage docs, so I'm probably looking in the wrong place. I did run an explain plan on the second, but couldn't figure out how to get one working on the first.

Answer 1

SQL> set timing on
SQL> DECLARE
  2    i number;
  3  BEGIN
  4    FOR j IN 1..100000 LOOP
  5      i:=i+j;
  6    END LOOP;
  7  END;
  8  /

Procedura PL/SQL zosta-a zako˝czona pomyťlnie.

Ca-kowity: 00:00:00.03
SQL> DECLARE
  2    i number;
  3  BEGIN
  4    FOR j IN 1..100000 LOOP
  5      SELECT i+j INTO i FROM dual;
  6    END LOOP;
  7  END;
  8  /

Procedura PL/SQL zosta-a zako˝czona pomyťlnie.

Ca-kowity: 00:00:05.98
SQL>

300 miliseconds vs. 6 seconds ===> ~ 20 times faster

Answer 2

Tom Kyte says:

I have no idea why someone would want to code:

select f(x) into l_x from dual; 

over

l_x := f(x); 

It is just "stupid" (it must come from T-SQL and Sqlserver/Sybase coders where they had to do it that way)

I realise it doesn't entirely address your question. You will be doing a context switch in your second version, though I would expect the effect to be negligible except under extreme conditions, e.g. in a very tight loop. I have a vague memory that selecting from dual has been optimised out of PL/SQL calls when it is seen to be uneccessary but I can't find anything to back that up, so I might have imagined it. (And kordirko's test suggests I did!)

There is certainly no benefit in using the select over the assignment, though.

Answer 3

Option #1 Is done with the PL/SQL engine itself. Result immediately available. More over In-Built function, very fast!

Option #2 One context Switching happens from PL/SQL Engine to SQL Engine

Both works very fast. But why waste a SQL context switching?(When called multiple times.. multiple swaps) It's a negligible one, but still. Option #1 wins hands down!