RE: Function slows the select

From: Greg Solomon <greg.solomon_at_betfair.com>
Date: Thu, 28 Jun 2001 09:00:00 -0700
Message-ID: <F001.0033BF3A.20010628080928@fatcity.com>

<SPAN

class=623015014-28062001>Hi
<SPAN

class=623015014-28062001> 
Try
replacing
<SPAN

class=623015014-28062001> 
GetSid

<SPAN

class=623015014-28062001> 
<SPAN

class=623015014-28062001>with
<SPAN

class=623015014-28062001> 
<SPAN

class=623015014-28062001>(select SYS_GUID() from dual)
<SPAN

class=623015014-28062001> 
in the
update statement, ie don't use a function.
<SPAN

class=623015014-28062001> 
Or
use
<SPAN

class=623015014-28062001> 
<SPAN

class=623015014-28062001>DECLARE
<SPAN

class=623015014-28062001>vGetSid Varchar2(4000);
<SPAN

class=623015014-28062001>BEGIN

select
SYS_GUID() into GetSid from dual;
<SPAN

class=623015014-28062001> 
<FONT

size=2> Update  UpdateTable<FONT
size=2>--Varosokat updatel  
Set UpdateTable_Column1=

--<FONT

size=2>      
(        Select 
distinct  'Some string 

here'||vGetSid||UpdateTable_Column2||Logo.Logo_Filenev                  From
VarosTorzs,Logo, Link_keszit        
         <FONT
size=2>Where<FONT
size=2>         
logo_varos_unique_az  = link_keszit_varos_az          
          and
logo.logo_azonosito1= 'KKEPX' and
logo.logo_azonosito2= <FONT
size=2>'20200'<FONT
size=2>         
          and
logo_varos_unique_az  =
varos_unique_azonosito      

           ) 
         
     Where UpdateTable_Column2 Is 

Not Null ;END;
<SPAN

class=623015014-28062001> 
<SPAN

class=623015014-28062001>HTH
<SPAN

class=623015014-28062001>Greg

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Csillag Zsolt   [mailto:starsoft_at_interware.hu]Sent: Wednesday, 27 June 2001   12:20To: Multiple recipients of list ORACLE-LSubject:   Function slows the selectHi,In the
  following select statement I have a function "GetSid" that slows theupdate   for hours even if the Link_keszit updatable table consists of a few   rows. Update  UpdateTable<FONT color=#ff0000   size=1>--Varosokat updatel  
  Set UpdateTable_Column1=

  --<FONT

  size=1>      
  (        Select 
  distinct  'Some string 

  here'||GetSid||UpdateTable_Column2||Logo.Logo_Filenev                    From
  VarosTorzs,Logo,
  Link_keszit        
          
  Where         
  logo_varos_unique_az  = link_keszit_varos_az            
            and
  logo.logo_azonosito1= <FONT color=#008000   size=1>'KKEPX' and logo.logo_azonosito2=   '20200'<FONT
  size=1>         
            and
  logo_varos_unique_az  =
  varos_unique_azonosito      

             ) 
           
       Where UpdateTable_Column2 Is 

  Not Null ;The GetSid function is:CREATE   OR REPLACE Function GetSid Return VarChar2   AS BEGIN  
  return(SYS_GUID()); END GetSid;So the   problem is that _with_ GetSid function it takes many hours instead of a few   minutes.Thank you in advanceZsolt
  Csillag,Hungary Received on Thu Jun 28 2001 - 11:00:00 CDT