Best ways for updating a table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
Best ways for updating a table
Hi,
I am updating a table that contains 10 million records. I am updating 4 columns with 7 key columns that make unique for each row.
It took 10 hrs to complete 2 million records and if it moves at this pace it takes 50hrs. Can some let me know what is the best approach for it to make it faster?
I am updating an oracle table from a dataset.
Thanks
I am updating a table that contains 10 million records. I am updating 4 columns with 7 key columns that make unique for each row.
It took 10 hrs to complete 2 million records and if it moves at this pace it takes 50hrs. Can some let me know what is the best approach for it to make it faster?
I am updating an oracle table from a dataset.
Thanks
"7 key columns that make unique for each row" only helps if there is an index over those columns, or at least an index where the leading columns include as many of these as possible.
Is that the case? Otherwise, each update results in a full table scan.
Is that the case? Otherwise, each update results in a full table scan.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
On Oracle I would never update all records of a table. Even if you have 100 columns and only need to update 4 it will be faster to create a new table, load it with the full records(while loading change the 4 columns you need to update offcource.) Once this is done rename the old table(to _archive) or drop it and rename the new table to the old name.
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
Can you please explain more on this?jasper wrote:On Oracle I would never update all records of a table. Even if you have 100 columns and only need to update 4 it will be faster to create a new table, load it with the full records(while loading change the 4 columns you need to update offcource.) Once this is done rename the old table(to _archive) or drop it and rename the new table to the old name.
Thanks
I don't know your job so I'll make some assumptions.
I suppose you have a second source where you have the 7 key colums+ the 4 columns to be updated. If you select all records from your table, all columns exept the 4 and you join this with the second source, you get the full new record. Insert this into a new table and then rename to make the new table act like the old.
The rename is not something you can do in standard datastage, but do this as an afterjob routine or something (close command also possible.)
I suppose you have a second source where you have the 7 key colums+ the 4 columns to be updated. If you select all records from your table, all columns exept the 4 and you join this with the second source, you get the full new record. Insert this into a new table and then rename to make the new table act like the old.
The rename is not something you can do in standard datastage, but do this as an afterjob routine or something (close command also possible.)
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
As per my understanding..i will be doing the following...please correct me if i am wrongjasper wrote:I don't know your job so I'll make some assumptions.
I suppose you have a second source where you have the 7 key colums+ the 4 columns to be updated. If you select all records from your table, all columns exept the 4 and you join this with the second source, you get the full new record. Insert this into a new table and then rename to make the new table act like the old.
The rename is not something you can do in standard datastage, but do this as an afterjob routine or something (close command also possible.)
dataset with records excluding 4 columns
\
\
\ --------------->
/
dataset with 4 changed columns........ /
MergeState------>Dataset(full table)------>OracleStage (create newtable)--->change the name of the table/drop the old table
Take that advice with a grain of salt. It can easier said than done to just 'build a new table and swap it around for the old one' when there are constraints / foreign keys / RI involved. That should have at least been mentioned.
And unless we're talking about a work table, not everyone has the grants they'd need to create a new table or rename existing ones, let alone the outage that could potentially cause.
Food for thought.
And unless we're talking about a work table, not everyone has the grants they'd need to create a new table or rename existing ones, let alone the outage that could potentially cause.
Food for thought.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
Re: Best ways for updating a table
Hi kirankota79
Can you mentioned the Database names (source and target)
More explain about your design
Why I am asking is earlier I faced same problem. At that time the table has over 100 million rows. Working on AIX DevBCU platform and DB29.1
I changed my design by using DB2EE / NODE for performance.
My job is finished with in 45 min. (earlier that has taken 14hrs.)
It may helpful all.
Can you mentioned the Database names (source and target)
More explain about your design
Why I am asking is earlier I faced same problem. At that time the table has over 100 million rows. Working on AIX DevBCU platform and DB29.1
I changed my design by using DB2EE / NODE for performance.
My job is finished with in 45 min. (earlier that has taken 14hrs.)
It may helpful all.
kirankota79 wrote:Hi,
I am updating a table that contains 10 million records. I am updating 4 columns with 7 key columns that make unique for each row.
It took 10 hrs to complete 2 million records and if it moves at this pace it takes 50hrs. Can some let me know what is the best approach for it to make it faster?
I am updating an oracle table from a dataset.
Thanks
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
Re: Best ways for updating a table
Hi Sreddy,
I have two jobs. First job writes the key columns and the columns i need to update (here 7 key cols + 4 cols need to be transformed and updated) to a dataset.
Design: oracle enterprise stage----->transformer----->dataset
Second job is used the dataset from the first job and updates the 4 cols.
Design: dataset----->transformer----->oracle enterprise stage
Our server is 4 node setup and the OS is windows 2003 server. DS is 7.5x2
The job is just finished and to took 60 hrs for updating 10million records.
Please let me know if i need to chang any setting to make it faster?
Thanks
I have two jobs. First job writes the key columns and the columns i need to update (here 7 key cols + 4 cols need to be transformed and updated) to a dataset.
Design: oracle enterprise stage----->transformer----->dataset
Second job is used the dataset from the first job and updates the 4 cols.
Design: dataset----->transformer----->oracle enterprise stage
Our server is 4 node setup and the OS is windows 2003 server. DS is 7.5x2
The job is just finished and to took 60 hrs for updating 10million records.
Please let me know if i need to chang any setting to make it faster?
Thanks
[/quote]sreddy wrote:Hi kirankota79
Can you mentioned the Database names (source and target)
More explain about your design
Why I am asking is earlier I faced same problem. At that time the table has over 100 million rows. Working on AIX DevBCU platform and DB29.1
I changed my design by using DB2EE / NODE for performance.
My job is finished with in 45 min. (earlier that has taken 14hrs.)
It may helpful all.
How long does Job 1 take? Combining these two jobs and removing the DataSet will certainly speed up processing.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>