Slow SQL_Statement

From: Thomas <echo88de_at_yahoo.de>
Date: 12 Dec 2005 07:30:50 -0800
Message-ID: <1134401450.615856.24350@o13g2000cwo.googlegroups.com>

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?

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?

Thank you in advance...

Greetz from germany
Thomas Received on Mon Dec 12 2005 - 09:30:50 CST