Performances SELECT ... FROM DUAL;

[hpaille]

Hi,

While writing some code, I was wondering if it is better to select 1 info per select from dual, or to group them into 1 select ?

i.e.:
is SELECT a, b, c
INTO v_a, v_b, v_c
FROM dual;
better than
SELECT a into v_a from dual;
SELECT b into v_b from dual;
SELECT c into v_c from dual;
?

Oracle 9i.

Thanks for your answers.

 

[sem]

The 1-st is better, but this may matter only if you plan to execute your code thousand times per second.

Regards, Dima

http://seminihin.narod.ru

 

[carp]

And if this is going to be in a piece of PL/SQL,
v_a := a;
v_b := b;
v_c := c;
is even better.

 

[hpaille]

yes, well, "a" is not a simple variable/value but an expression (greatest or some date function like extract...)

Thanks for your answers.

 

[carp]

But if you are able to get "a" by selecting from dual, this implies that you don't need any tables.

So if you are getting the value from a function,
v_a := greatest(1,3,v_b);
is still more efficient than
SELECT greatest(1,3,v_b) INTO v_a FROM dual;

 

[sem]

Carp, how about DECODE ?

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Dima,

I don't understand your question...Since "DECODE" is only available in a SQL statement, I don't understand how your question applies to an obviously PL/SQL situation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

That was a remark for Carp. The idea was that not all SQL functions exist in PL/SQL thus select from dual is not exactly the same as :=.

Regards, Dima

http://seminihin.narod.ru

 

[carp]

How absolutely true! But then again, if you were really concerned with efficiency and didn't want to use a query to simply assign a value to a variable, you could recast your DECODE as a CASE.
But I take your well-founded point. My absolutism is not so absolute!