Re: Function slows the select

[Jared Still]

What version of database?


On Wednesday 27 June 2001 04:20, Csillag Zsolt wrote:
 Hi,

 In the following select statement I have a function GetSid that slows the
 update for hours even if the Link_keszit updatable table consists of a few
 rows.

   Update  UpdateTable--Varosokat updatel
 Set UpdateTable_Column1=
--
(
  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= 'KKEPX' and logo.logo_azonosito2=
 '20200'

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 advance

 Zsolt Csillag,
 Hungary


Content-Type: text/html; charset=us-ascii; name=Attachment: 1
Content-Transfer-Encoding: 7bit
Content-Description: 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

RE: Function slows the select

[Greg Solomon]

Hi

Try 
replacing 

GetSid 


with

(select SYS_GUID() from dual)

in the 
update statement, ie don't use a function.

Or 
use

DECLARE
vGetSid Varchar2(4000);
BEGIN

select 
SYS_GUID() into GetSid from dual;

Update UpdateTable--Varosokat updatel 
Set UpdateTable_Column1= 
 
-- 
(Select 
distinct 'Some string 
here'||vGetSid||UpdateTable_Column2||Logo.Logo_Filenev 
 From 
VarosTorzs,Logo, Link_keszit 
 Where 
logo_varos_unique_az = link_keszit_varos_az 
 
 and
logo.logo_azonosito1= 'KKEPX' and 
logo.logo_azonosito2= '20200' 
 and 
logo_varos_unique_az = 
varos_unique_azonosito 
 ) 
 
 Where UpdateTable_Column2 Is 
Not Null ;END;

HTH
Greg

  -Original Message-From: Csillag Zsolt 
  [mailto:[EMAIL PROTECTED]]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--Varosokat updatel 
  Set UpdateTable_Column1= 
   
  -- 
  (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= 'KKEPX' and logo.logo_azonosito2= 
  '20200' 
   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 
  ASBEGIN 
  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 

Function slows the select

[Csillag Zsolt]

Hi,

In the following select statement I have a function GetSid
that slows the
update for hours even if the Link_keszit updatable table consists of a
few rows.

Update
UpdateTable--Varosokat
updatel
 Set UpdateTable_Column1=


--
 (
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= 'KKEPX' and logo.logo_azonosito2= '20200'
 
 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 advance

Zsolt Csillag,
Hungary