OraFAQ Forum: SQL & PL/SQL » Need a query for updating a table with 1 billion records

Home » SQL & PL/SQL » SQL & PL/SQL » Need a query for updating a table with 1 billion records (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need a query for updating a table with 1 billion records [message #331610]

Fri, 04 July 2008 04:00

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

Report message to a moderator

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

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Go ahead.

Regards,
Rajat Ratewal

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Er.... UPDATE?

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

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

Frank
Messages: 7901
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.

Report message to a moderator

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

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

Report message to a moderator

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

flyboy
Messages: 1903
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?

Report message to a moderator

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

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.

Report message to a moderator

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

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

Frank
Messages: 7901
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.

Report message to a moderator

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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

Frank
Messages: 7901
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?

Report message to a moderator

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

sudharshan
Messages: 48
Registered: November 2006

Member

GO ahead and do it.

what is stoping you from updating the column with a default value.

Report message to a moderator

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

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is the need of the function?

Regards
Michel

Report message to a moderator

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

Frank
Messages: 7901
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 ?

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Frank
Messages: 7901
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..

Report message to a moderator