Does primary key structure impact UPDATE performance?
- From: "Thomas Jeff" <jeff.thomas@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <'oracle-l@xxxxxxxxxxxxx'>
- Date: Sun, 16 May 2004 21:07:50 -0500
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@xxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
