Home » SQL & PL/SQL » SQL & PL/SQL » Need a query for updating a table with 1 billion records (Oracle 10g)
Need a query for updating a table with 1 billion records [message #331610] Fri, 04 July 2008 04:00 Go to next message
rkmbala
Messages: 7
Registered: July 2008
Location: Chennai
Junior Member

Hi All,
I need to update a table which has over 1 billion records. And also I need to add a column with a default value in that table.


Thanks in advance
Re: Need a query for updating a table with 1 billion records [message #331614 is a reply to message #331610] Fri, 04 July 2008 04:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Go ahead.

Regards,
Rajat Ratewal
Re: Need a query for updating a table with 1 billion records [message #331618 is a reply to message #331610] Fri, 04 July 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Er.... UPDATE?

Regards
Michel
Re: Need a query for updating a table with 1 billion records [message #331622 is a reply to message #331618] Fri, 04 July 2008 04:29 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
rkmbala you have not shared what actually is your problem.

1) Whether you need statement to update data in oracle.
2) Or Want to make update opeartion faster.

Regards,
Rajat
Re: Need a query for updating a table with 1 billion records [message #331660 is a reply to message #331622] Fri, 04 July 2008 07:31 Go to previous messageGo to next message
rkmbala
Messages: 7
Registered: July 2008
Location: Chennai
Junior Member

I have a table with 28 columns and more than one billion rows. I need to add a column with a default value for all rows. That's my exact requirement. Moreover I to make update opeartion faster

[Updated on: Fri, 04 July 2008 07:38]

Report message to a moderator

Re: Need a query for updating a table with 1 billion records [message #331662 is a reply to message #331660] Fri, 04 July 2008 07:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Updating 10 rows in a table with 10 zillion rows, selecting the rows by means on an index is no slower than updating 10 rows in a 1000-records table.
Provide us with more details about the update that needs to be faster.

Adding a column with a default value can mean different things:
- existing rows get a null value
- existing rows get the default value
- existing rows get a value, based on their contents.

Updating a billion records simply WILL take time.
Re: Need a query for updating a table with 1 billion records [message #331668 is a reply to message #331662] Fri, 04 July 2008 08:17 Go to previous messageGo to next message
rkmbala
Messages: 7
Registered: July 2008
Location: Chennai
Junior Member



Suppose I have a table like following structure.

Name Null? Type
-------------------------------------- --------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
DUMMY VARCHAR2(1)



Assume the above said table has 1 billion rows.

I want to add a column called STATUS whose datatype is VARCHAR2(1).

After adding this column i want to update the column STATUS with value 'N' for all rows.


This is my requirement.

Note: I want to update the above said table more quickly

Thanks in advance

Bala
Re: Need a query for updating a table with 1 billion records [message #331670 is a reply to message #331610] Fri, 04 July 2008 08:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You may get the idea from How to Update millions or records in a table thread on AskTom.
But, honestly, do you think that treating billion rows will be quick?
Re: Need a query for updating a table with 1 billion records [message #331671 is a reply to message #331668] Fri, 04 July 2008 08:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you dropped the requirement to have a default value for the field, or did you forget to include that in this post.

It would be quickest, if you can guarantee that no one will change the table while you're doing this, to add the constraint as ENABLE NOVALIDATE as this will not require the Db to check every single row for compliance with the constraint.
Re: Need a query for updating a table with 1 billion records [message #331672 is a reply to message #331668] Fri, 04 July 2008 08:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Note: I want to update the above said table more quickly

You could try the new hint provided by oracle /*+ run_very_fast_update */ . Try it out and see if that is of any help to you. Also please note making your post in bold is very useful rather than following the forum guidelines and giving some pointless information like
a) oracle version
b) Any indexes
c) What have you tried so far
These information are completely pointless to add along with your requirement.

So as I mentioned earlier try the hint which I have provided and see if that is of any help to you.

Regards

Raj

[Edit:] Typo

[Updated on: Fri, 04 July 2008 08:50]

Report message to a moderator

Re: Need a query for updating a table with 1 billion records [message #331673 is a reply to message #331671] Fri, 04 July 2008 08:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Would adding a default value be treated as a constraint?
It's not like (from the db's perspective) every row must have this value.
Re: Need a query for updating a table with 1 billion records [message #331680 is a reply to message #331673] Fri, 04 July 2008 09:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmm - looks like you're right there - adding a default doesn't require a check on the existing table. Mea culpa there.
Re: Need a query for updating a table with 1 billion records [message #331957 is a reply to message #331672] Sun, 06 July 2008 23:23 Go to previous messageGo to next message
rkmbala
Messages: 7
Registered: July 2008
Location: Chennai
Junior Member

Hi All,
Once again i m telling my exact requirement to update my table.

Suppose I have a table like following structure.

Name Null? Type
-------------------------------------- --------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
DUMMY VARCHAR2(1)



Assume the above said table has 1 billion rows.

I want to add a column called STATUS whose datatype is VARCHAR2(1).

After adding this column i want to update the column STATUS with value 'N' for all rows or while adding the above said column I want to set default value as 'N'.


This is my requirement.

Note: I want to update the above said table more quickly
I am using Oracle 10g Release 10.2.0.1.0 - 64bit Production version
I have one index on that table.
I have tried with CTAS(create table as select ... from <tablename> command. That also taking long time. I want some other solution apart from CTAS.

Thanks in advance

Bala
Re: Need a query for updating a table with 1 billion records [message #331960 is a reply to message #331610] Sun, 06 July 2008 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Is using BOLD significant?

[Updated on: Sun, 06 July 2008 23:25] by Moderator

Report message to a moderator

Re: Need a query for updating a table with 1 billion records [message #331962 is a reply to message #331957] Sun, 06 July 2008 23:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Define 'a long time'
How long does it take to add the new column?
How long does it take to do the update?
How long did the CTAS take?

How long do you expect/want it to take?
Re: Need a query for updating a table with 1 billion records [message #331974 is a reply to message #331610] Mon, 07 July 2008 01:17 Go to previous messageGo to next message
sudharshan
Messages: 48
Registered: November 2006
Member
GO ahead and do it.

what is stoping you from updating the column with a default value.
Re: Need a query for updating a table with 1 billion records [message #331975 is a reply to message #331962] Mon, 07 July 2008 01:34 Go to previous messageGo to next message
rkmbala
Messages: 7
Registered: July 2008
Location: Chennai
Junior Member

Hi Frank,

I have checked with CTAS for 2000000 records. It is taking 3 mins.

I have following function to create and update the column with value 'N'


set serveroutput on
create or replace function updstatus(s varchar)
return varchar2 is
x varchar2(1);
begin
x:='N';
return(x);
end;


Then I had put following create statement to create the new table with CTAS.


create table t2 as select OWNER,NAME,TYPE,LINE,TEXT,DUMMY,updstatus(DUMMY)status from t1;



From the above statement I got the table with new column called status with value 'N'.


Suppose I'l use the above CTAS command what about the other objects related to that table?(ex. Index,...etc)



Bala

[mod-edit] BOLD removed.

[Updated on: Mon, 07 July 2008 07:51] by Moderator

Report message to a moderator

Re: Need a query for updating a table with 1 billion records [message #331979 is a reply to message #331975] Mon, 07 July 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the need of the function?

Regards
Michel
Re: Need a query for updating a table with 1 billion records [message #331980 is a reply to message #331975] Mon, 07 July 2008 02:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
3 minutes for 2 million rows and you complain that it is slow?
How many rows per second do you want?

And how did you limit that to 2 million?

Like Michel said, why on earth do you use that idiotic function?
Why not simply ..as select (col1, col2, 'N') from old_table

How long did the addition of the column plus the update take ?
Re: Need a query for updating a table with 1 billion records [message #331981 is a reply to message #331980] Mon, 07 July 2008 02:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I think he has just tested on a dummy table with few columns.

Not on the exact table with 28 columns.

Regards,
Rajat Ratewal
Re: Need a query for updating a table with 1 billion records [message #331982 is a reply to message #331980] Mon, 07 July 2008 02:20 Go to previous messageGo to next message
rkmbala
Messages: 7
Registered: July 2008
Location: Chennai
Junior Member

Hi Frank
You have mentioned as

select (col1, col2, 'N') from old_table

Your point also correct.


Totally it takes 3 mins(i.e for addition of the column plus the update).

Definitely it is slow. I need to reduce that time into 50 seconds.( I mean total update time should takes place 50 secs).

I have created that table(for sample purpose) from another one table using rownum clause in where condition.

I didn't touch the original table, because it is in production environment.

Bala



Re: Need a query for updating a table with 1 billion records [message #331984 is a reply to message #331982] Mon, 07 July 2008 02:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Try it with nologging.
Again: how long does the update of the original table take?
Copy that table to your dev-environment; how else would you ever be able to test?!

Quote:
I need to reduce that time into 50 seconds.

Based on what? Some PHB stating that the overall change should not exceed 7 hours?
If so, let him suggest you what to do and tell him in the future it would be great to not touch such big tables anymore..
Re: Need a query for updating a table with 1 billion records [message #331998 is a reply to message #331984] Mon, 07 July 2008 03:46 Go to previous messageGo to next message
rkmbala
Messages: 7
Registered: July 2008
Location: Chennai
Junior Member

Hi Frank

Thanks.. I'll try that in Development environment and revert you. One more thing what is PHB?.



Bala

Re: Need a query for updating a table with 1 billion records [message #332004 is a reply to message #331998] Mon, 07 July 2008 03:53 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Pointy Haired Boss
Previous Topic: how to call an exe from oracle trigger
Next Topic: how to create view dynamically
Goto Forum:
  


Current Time: Fri Dec 09 03:58:26 CST 2016

Total time taken to generate the page: 0.16959 seconds