Very bad performances on Oracle

From: Florian RIVAL <florian.rival_at_caramail.fr>
Date: Fri, 12 Feb 1999 13:03:15 +0100
Message-ID: <7a157a$k83$1@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;

  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 - 06:03:15 CST