| Update only 8 rows in a huge Table [message #293933] |
Tue, 15 January 2008 13:16  |
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 #293948 is a reply to message #293941] |
Tue, 15 January 2008 14:09  |
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 #294180 is a reply to message #293933] |
Wed, 16 January 2008 12:51 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
