Home » RDBMS Server » Performance Tuning » Update only 8 rows in a huge Table (Oracle 10g Rel 2)
Update only 8 rows in a huge Table [message #293933] Tue, 15 January 2008 13:16 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi

This is an update statement that needs to be run in PROD , it's a business requirement.
Table has 170 mill rows , and probably only 8 rows are there that needs to be updated out of 170 mil.

I ran in Test region where only 70 mil rows are there it took 7 min to complete,
There is no Index on SETL_STATUS_CD, becz we don't want since this is the only time we will use this column in where clause, Don't want an extra index on this table/column.

In PROD when i will be running there will be no activity at all we will run this during outage, Is there any room to do this simple update in a better and less time.
UPDATE PAYMENT SET SETL_CHANNEL_ID = NULL WHERE SETL_STATUS_CD = '2';


thanks
Re: Update only 8 rows in a huge Table [message #293941 is a reply to message #293933] Tue, 15 January 2008 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any room to do this simple update in a better and less time.

Create an index.
Oh! you don't want then you are stuck with full scan.

Regards
Michel
Re: Update only 8 rows in a huge Table [message #293948 is a reply to message #293941] Tue, 15 January 2008 14:09 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Michel for response,

I understand Creating index will help,
Creating index will take 15 min on 170 mil rows table and then some time for analyzing it and the running the update.

at the end time will be more or less same with and without index.


Thanks
Re: Update only 8 rows in a huge Table [message #293951 is a reply to message #293948] Tue, 15 January 2008 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Index should be created only once.
How many times do you have to execute the update?

Regards
Michel
Re: Update only 8 rows in a huge Table [message #293953 is a reply to message #293951] Tue, 15 January 2008 14:17 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
hi,

Only 1 Time.


Thanks

Re: Update only 8 rows in a huge Table [message #293962 is a reply to message #293953] Tue, 15 January 2008 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So who cares how long it will last?
Less time than this topic.

Regards
Michel
Re: Update only 8 rows in a huge Table [message #293988 is a reply to message #293962] Tue, 15 January 2008 20:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Parallel DML?

Ross Leishman
Re: Update only 8 rows in a huge Table [message #294180 is a reply to message #293933] Wed, 16 January 2008 12:51 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
if you are really desperate, you could try partitioning the table by SETL_STATUS_CD

Then Oracle won't bother to scan anything and instead will just update everything in the one partition. Of course you will have to buy into everything that paritioning entails so you should do some reading about it. At least this way you do not have the extra index.

However, depending upon why you don't want this extra index, you may decide partitioning is an even worse choice. My guess is: if you are not willing to create a one column index, then you won't bite on the even bigger investment of partitioning.

My advice, same as Michel's, create a permanent index or eat the seven minutes. Ross's suggestion of Parallel DML sounded worth exploring as well, though you should understand how it works in Oracle before you adopt it.

Good luck, Kevin

Previous Topic: Performance tuning of SQL query
Next Topic: index monitoring
Goto Forum:
  


Current Time: Sun Dec 04 00:32:32 CST 2016

Total time taken to generate the page: 0.08658 seconds