Re: Slow SQL_Statement

Hi Thomas,

Wow, I can see some major problems with this statement. You are going to completely disable index usage (concatenation of columns disables index usage by default, unless you're using a function-based index), if an index exists on any of these columns.

To put this bluntly, this is not a performance problem with the database. This is a performance problem with your SQL and possibly your database design at the table level.

Need some more information, mate, but I can help you to provide me with this.

1. Does an index exist on any of the columns in your WHERE clause? You can find out by running the following query:

SELECT * FROM all_ind_columns
 WHERE table_name = 'ACCT_RELATION';

2. Use a composite subquery instead of concatenation. This will not disable any indexes from being used (if they exist). Instead of what you have, you should try:

UPDATE ACCT_RELATION SET GETSCORE=1
    WHERE (mandant, kndid, ktoid) IN ( SELECT mandant, kndid, ktoid FROM ACCOUNT) ; I think you should find this solves your problem, BUT you MUST have some kind of indexing strategey on the ACCT_RELATION table for this to work.

3. You might want to consider reading a little bit about SQL performance tuning (a gargantuan topic in its own right), but in order to understand what has happened in your case, you wound't need to read into it super-deeply (in my opinion). Try the O'Reilly mini-guide "Oracle SQL Tuning" to get you started. It's not expensive.

Hope this helps to guide you in the right direction anyway.

Cheers.

James

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?
>
> 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 - 10:56:53 CST