Re: Slow SQL_Statement

"Thomas" <echo88de_at_yahoo.de> wrote in message news:1134401450.615856.24350_at_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
>

You have made it very hard for Oracle to find any efficient way to perform this query.

Your optimum strategy is probably to use an updateable join view - but you will need to declare a primary key (on the account table) for that to work.

You've said that mandant,kndid,ktoid is
a unique tuple (I assume on account) -
so declare it as unique - is it also not null, if so it would appear to be the primary key.

The following code should then do what
you want at reasonable speed (though it won't necessarily use the index on account - it just needs the primary key declaration).

update

    (select getscore -- assume this is in acct_relation only     from

        account ac, acct_relation ar     where

            ar.mandant = ac.mandant
    and ar.kndid = ac.kndid
    and ar.ktoid = ac.ktoid
    )
set getscore = 1
;

The primary key is necessary on ACCOUNT so that the optimizer can "know" that when it picks a row from acct_relation, this will not be expanded to multiple rows by the action of the join.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005