Home » SQL & PL/SQL » SQL & PL/SQL » Need to move 400000 rows from one table to another
Need to move 400000 rows from one table to another [message #19635] Tue, 02 April 2002 01:49 Go to next message
Sushil
Messages: 16
Registered: October 2000
Junior Member
I am using Oracle 8i and need to MOVE around 400000 rows from one table A to another table B in the same database. Average row size is 175 bytes. Currenly I am using Direct Load Insert for Table B and then delete the same rows from Table A. But it is taking huge time.

a) Am I wrong in my approach - Is their a better approach to do it?
b) Although it depends on database setup but any idea about how much time approximately it should take for such a data transfer?

any hints are welcome.

Thanks in advance
Sushil

Report message to a moderator

Re: Need to move 400000 rows from one table to another [message #19644 is a reply to message #19635] Tue, 02 April 2002 08:09 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Having indexes on table B will slow the operation down a lot. Try dropping the indexes on B before the insert and rebuilding them after the insert. If A is to be completely emptied, then "truncate table A;" (note - you can't roll back after truncate).
http://asktom.oracle.com/pls/ask/f?p=4950:8:651721::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3240272261626,

Report message to a moderator

Re: Need to move 400000 rows from one table to another [message #20273 is a reply to message #19635] Fri, 10 May 2002 03:23 Go to previous message
Heinz
Messages: 3
Registered: May 2002
Junior Member
Try to rename Table A to table B. (alter table a rename to b;) Then create a new Table A. But take care of your indexes and triggers.

Report message to a moderator

Previous Topic: What is Pinning
Next Topic: Loading duplicate rows into an exception table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 09:50:48 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.