RE: Updating 100 M rows table
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: "smishra_97@xxxxxxxxx" <smishra_97@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, Josh.Collier@xxxxxxxxxxxx
- Date: Thu, 21 Jan 2010 15:22:32 -0800 (PST)
Now if you are the same Sanjay Mishra that co-authored a book on parallel
execution, I know for a fact that we do NOT have to go over that topic with
you... I read this book when I was researching for a white paper and
presentation... :)
Is this an OLTP or OLAP environment, (some folks might think I'm crazy for
asking that, but I have a 2TB OLTP, so I will...:))?
If you are just looking for a couple of options to use with your current
knowledge:
And...If you do have the ability to "quiet" this table along with partitioning,
performing CTAS of each partition, updating the new table and then exchanging
the partition with an update indexes statement is pretty easy to do unless
there is a massive quantity of partitions.
A table redefinition, creating the new table, performing the update on the new
table and then sync'ing up and ridding of the original. I've never done this,
but the DBA I work with loves this option..
I know this is always up for debate, but freelists and initrans. I've actually
been able to gain the same performance on huge updates by hiking the freelists
and initrans as I would have with nologging, (dataguard invironment that I
tired of rebuilding the secondary on after this monthly load process.) When
doing so, you must remember to match the new settings to the indexes as well.
This may give you added speed with nologging though!
Hope this helps,
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--- On Thu, 1/21/10, Josh Collier <Josh.Collier@xxxxxxxxxxxx> wrote:
From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
Subject: RE: Updating 100 M rows table
To: "smishra_97@xxxxxxxxx" <smishra_97@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Date: Thursday, January 21, 2010, 2:35 PM
Asktom
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6407993912330
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sanjay Mishra
Sent: Thursday, January 21, 2010 1:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Updating 100 M rows table
Can anyone suggest some experience with big updates. I had at table with 30
column (non-Lob or LONG) in 10g and has few indexes. I need to update 20
million Records of 100Million, what is the best appproach. We can use nologging
as it is not having any DR associated with it and Database is not in FORCE
Logging.
TIA
Sanjay
Other related posts:
