Oracle Assignment vs Select Into

oracle, performance, sql

Solution

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

Problem

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.

Original source