Re: Slow SQL_Statement

From: Lothar Armbruester <lothar.armbruester_at_t-online.de>
Date: Mon, 12 Dec 2005 18:13:36 +0100
Message-ID: <pan.2005.12.12.17.13.36.84355@t-online.de>

On Mon, 12 Dec 2005 07:30:50 -0800, Thomas wrote:

> Hi,
>
> I have a great performance problem on an Oracle 9.2.0.1 Server...
> (This Problem is independent from the version)
> I try to update a table containing about 800,000 Rows with the
> following statement:
>
> UPDATE ACCT_RELATION SET GETSCORE=1
> WHERE mandant||kndid||ktoid IN (
> SELECT mandant||kndid||ktoid
> FROM ACCOUNT) ;
>
> Only Four rows but about two days of processing time. This is not the
> only statetement used, so the complete job would sum to about 10 days
> of processing time. This is much, much too long.
> Has anyone an Idea how to speed up the processing by changing the
> statement?
>

Hello Thomas,
with your current SQL only a function based index would help since an index is not used when functions are applied on the indexed columns and IIRC concatenation is considered a function. If you want to avoid a function based index you could try the following:

update acct_relation set

   getscore=1
where

   (mandant,kndid,ktoid) in
   (select mandant,kndid,ktoid
    from account);

This statement should benefit from an index over the three columns. Depending on the amount of updated rows versus total number of rows you also could try a variant with "where exists". Finally you can try the following if you define primary keys or at least unique indexes properly:

update

   (select

       ar.getscore,
       ar.mandant,
       ar.kndid,
       ar.ktoid
    from
       acct_relation ar,
       account a
    where
       ar.mandant=a.mandant and
       ar.kndid=a.kndid and
       ar.ktoid=a.ktoid) set

   getscore=1;

For this to work you have to ensure that the key of the table to be updated also is a key for the whole join. Maybe you will have to look up the topic "key preserved" (IIRC) in the documentation.

> more information:
> both tables ACCT_RELATION and ACCOUNT have about 800000 rows. no index
> no primary defined
> mandant,kndid,ktoid is a unique tuple. Would an index help or does
> oracle make a tablescan even if the index exists?
>

Hope that helps,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |

Received on Mon Dec 12 2005 - 11:13:36 CST