Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Very bad performances on Oracle

Re: Very bad performances on Oracle

From: Kostya Ogrodnichy <kostya_at_kpvti.kiev.ua>
Date: Fri, 12 Feb 1999 17:06:26 +0200
Message-ID: <918831621.383233@ns.kpvti.kiev.ua>


Hi!

Florian RIVAL wrote in message <7a157a$k83$1_at_minus.oleane.net>...
>
>I have two scripts : one for SQLServer and one other for Oracle. Althought
>these two scripts do the same job (upgrade of a database), the performances
>are very different : less than 1h30 on SQLServer, more than 9h00 on Oracle.
>For the most part of this upgrade, I used cursor in order to select datas
>from a table and to update this table.
>
>For example :
>
>MyTable
>{
> Name varchar2 (30), /* Primary key */
> PostCode Number (6)
>}
>
>The aim is to convert the PostCode in characters.
>
>alter table MyTable add PostCodeChar varchar2 (6) null
>/
>
>DECLARE
>
> Key varchar2 (30);
> Buffer integer;
>
> CURSOR Selection IS
> SELECT Name, PostCode
> FROM MyTable
> where PostCode is not null;
>BEGIN
>
> OPEN Selection;
>
> FETCH Selection INTO Key, Buffer;
>
> WHILE Selection%FOUND
> LOOP
> update MyTable set PostCodeVar = TO_CHAR ( Buffer ), PostCode =
null
> where Name = Key;
>
> FETCH Selection INTO Key, Buffer;
> END LOOP;
>
> CLOSE Selection;
>

Try write:

CREATE OR REPLACE TRIGGER UPDATE_MyTable BEFORE UPDATE  ON MyTable FOR EACH ROW
BEGIN
:new.PostCodeVar := TO_CHAR ( :old.PostCode ); END;
 /
update MyTable set PostCode = null

     where PostCode is not null;
/
DROP TRIGGER UPDATE_MyTable;
/
 commit;
/
>
>END;
>/
>
>For a table of 450 000 lines, this operation takes 2h00 on Oracle and 10
>minutes on SQLServer.
>
>I would like to know why Oracle is very slow.
>
>If someone have got any idea to optimize this script, it will be welcome.
>
>Thanks.
>
>
Received on Fri Feb 12 1999 - 09:06:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US