Home » SQL & PL/SQL » SQL & PL/SQL » Mass Insert /Update /Delete (Oracle9.2)
Mass Insert /Update /Delete [message #313235] Fri, 11 April 2008 03:30 Go to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
Hi All,


I want to

Insert,Update & delete some millions of rows in a table .

1)Insert from another table in the same schema
2)Update some millions of rows in the same table
3)Delete some millions of rows in the same table

The table doesn't have any Indexes,and no partitioning is also there.

Which is the best way to do it.. for the above 3 points ?


The same process if i need to do INSERT from a table through Database Link which is the best way to do it ?


Thanks

C.G.K
{Infinite Possibilities}
Re: Mass Insert /Update /Delete [message #313236 is a reply to message #313235] Fri, 11 April 2008 03:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Instructions on high-volume DML can be found here

Ross Leishman
Re: Mass Insert /Update /Delete [message #313454 is a reply to message #313235] Sat, 12 April 2008 09:20 Go to previous messageGo to next message
joe_nazy
Messages: 1
Registered: April 2008
Location: Chennai
Junior Member
Hi CGK,


I have option to delete mass records or rows from a table
using with rowid concept, but it will delete all the duplicate rows in a table. if you like to know i will share with you.


Regards
Gm.Nazeem

[Updated on: Sat, 12 April 2008 09:26]

Report message to a moderator

Re: Mass Insert /Update /Delete [message #313458 is a reply to message #313454] Sat, 12 April 2008 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you don't want to share it with us?

By the way, I doubt you have a new solution that those that have been already posted all these years.
But who knows? Post it.

Regards
Michel

[Updated on: Sat, 12 April 2008 10:22]

Report message to a moderator

Re: Mass Insert /Update /Delete [message #313485 is a reply to message #313235] Sat, 12 April 2008 23:01 Go to previous message
jaipal001
Messages: 1
Registered: March 2008
Location: Bangalore
Junior Member
hi friend this jayapal am doing mca, i have some knowledge about oracle.
1)u r asking insert the milions of the records into other table as same schema ok
first u can create table with same structure as u create table like this

create table emp_dup
as
select * from emp where 1=100;

-- this query gives to same struture of the emp table and with out coping data,because we r giving the where condition but it is not satisfing any field condition thats why simply it will create table structure.

now we can insert the values into emp_dup table

insert into emp_dup select * from emp;

(or)
create table emp_dup as select * from emp;

-- this query gives to same structure as well as it will copy total data of the emp table. no need to insert in this type.

2)u can update milions of the records

update <table name> set <col>=<value>/<exp>,
<col>=<value>/<exp>,
...................,
[where<condition>];
update emp set sal =sal+1000;
-- this query can update all records of the emp table
update emp set sal=sal+1000 where sal < 10000;
--this query can update where the condition is satisfied

3)zu can delete millions of the records

delete from <table name> [where<condition>];
delete from emp;
-- this query can delete all the records of the emp table.

delete from emp where sal =10000;
--this query can delete the emp records according to as given condition.
Previous Topic: External table Definitions
Next Topic: what's difference between RMAN and Exp/Imp?
Goto Forum:
  


Current Time: Fri Dec 02 14:02:21 CST 2016

Total time taken to generate the page: 0.12261 seconds