Re: Literal SQL and sys.dual

Why don't you do:

variable1 := 1+ 1;

instead of "select (1+1) into variable1 from dual"?

Igor Neyman, OCP DBA
ineyman_at_perceptron.com   

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, April 02, 2002 2:03 PM

> I am working on a database optimization project. My Shared pool is
> filled with SQL like
>
> select 0 from dual;
> select 1/1 from dual;
> select 1 - 1/(2 + 2) from dual;
>
>
> I tried to use the bind variables to minimize the literal SQL. However
> I need a different SQL script for each case.
>
> select :b1 from dual;
> select :b1/:b2 from dual;
> select :b1 - :b2/(:b3 + :b4) from dual;
>
> first one will handle all cases for b1 from 0 to any number which is
> good. And second sql will handle all the cases for any number for b1
> and b2. So I do reduce the literal SQL. However, I need to know ahead
> of time what type of data I am calculating and then use the appropriate
> SQL.
>
> I think the easy solution would be to use arithmetic. That is to pass
> the string like ( 1 + 1 / (2 -2) ) to some function that can return me
> the result of this sting. So I would not be using SQL script, to
> minimize SQL execution, sys.dual contention or literal SQL filling
> shared pool and causing both library cache and shared pool. Not to
> mention saving in CPU processing by not parsing SQL scripts.
>
> Anybody, aware of such function in PL/SQL? Is there any other way to do
> calculations other than 'select 1 + 1 from dual' ?
>
> I would really appreciate if you could let me know.
>
> Thanks
>
> Mohammed Shakir
>
> =====
> Mohammed Shakir
> CompuSoft, Inc.
> 11 Heather Way
> East Brunswick, NJ 08816-2825
> (732) 672-0464 (Cell)
> (732) 257-6001 (Home)
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://http://taxes.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammed Shakir
> INET: mshakir08816_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).