Home » SQL & PL/SQL » SQL & PL/SQL » Select from DUAL OR Assign Variable ??
Select from DUAL OR Assign Variable ?? [message #245180] Fri, 15 June 2007 07:11 Go to next message
kumarn
Messages: 13
Registered: June 2007
Location: London
Junior Member
Hi,

Can anyone tell me which one is better and why ??

1.
SELECT REPLACE(<string1>,<sting2>,<string3>) INTO vString FROM DUAL;


2.

vString := REPLACE(<string1>,<sting2>,<string3>);


In both the cases vString value is same.
Re: Select from DUAL OR Assign Variable ?? [message #245184 is a reply to message #245180] Fri, 15 June 2007 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2. Definitivly, if you are in PL/SQL of course.

Regards
Michel
Re: Select from DUAL OR Assign Variable ?? [message #245213 is a reply to message #245180] Fri, 15 June 2007 08:37 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
kumarn wrote on Fri, 15 June 2007 07:11
Hi,

Can anyone tell me which one is better and why ??

1.
SELECT REPLACE(<string1>,<sting2>,<string3>) INTO vString FROM DUAL;


2.

vString := REPLACE(<string1>,<sting2>,<string3>);


In both the cases vString value is same.


While the optimizer is smart enough to recognize that dual is a special case so that the physical database is never read, it still has to hit the optimizer. The equivalence will be interpreted by the sql engine, but will never hit the optimizer code and will be faster. Which is of course exactly what Michel said.

[Updated on: Fri, 15 June 2007 08:38]

Report message to a moderator

Previous Topic: NOT URGENT - do not answer. Please help PLS -00306
Next Topic: What will be maximum size of character variable
Goto Forum:
  


Current Time: Wed Dec 07 20:33:11 CST 2016

Total time taken to generate the page: 0.07276 seconds