Does primary key structure impact UPDATE performance?

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Sun, 16 May 2004 21:07:50 -0500
Message-ID: <358728A276824E419580403633AABFD0021F6191@INDYSMAIL03.am.thmulti.com>

We have a process that selects from one table in a cursor loop and updates two tables within the loop. These two tables are nearly identical in structure but have different primary keys. The first table's primary key is on a NUMBER column that is also the first column in the table. The 2nd table's primary key is a catenation of 3 columns, VARCHAR2(80), VARCHAR2(30), and a DATE, and these columns are among the last columns in the table (which has a total 70 columns.)
>From the 10046 traces, the performance of the update on the first table
is clearly far better then on the 2nd table. Just one update on the 2nd table is taking an average of 2 seconds to complete.

In addition to the primary key differences, the composition of the data in the first primary key column of the 2nd table is actually a catenation
of 4 other columns (the data comes from a SAP system), that looks something like this: '~~500AEI~500ID~43431AWQQE~AA44E~400ID', etc.

So, would this kind of character data, or the way the 2nd table's primary key is structured in comparision to the first table impact update performance?

Thanks!

Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.

Email: jeff.thomas_at_thomson.net

Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sun May 16 2004 - 21:05:05 CDT