emptying large tables

is there a better way to empty a large table than

DELETE FROM atable;
COMMIT;

Code (markup):

??

i've been working on migration scripts and so i've been populating tables, testing their accuracy, emptying them, making any necessary changes and rerunning my scripts..

one of the tables has 2000000+ records.. and when i use the delete from method it seems to take forever.. is there a better way?

do i need to empty it in chunks? like

delete from atable where id between 0-100;
commit;
delete from atable where id between 101-200;
commit;
etc.

Code (markup):

??

my problem is coming from mssql i never had to do commit's.. now working in oracle i don't know all the rules surrounding them.. like memory management

 

post script..

the reason i've got to ask this is because it's beginning to take up to an hour to empty out all my tables..

 

Save the table structure in a sql file somewhere. DROP the table and recreate it. Should only take a few seconds.

 

TRUNCATE TABLE table_name

Code (markup):

The above code will delete all entries from the table - and leave it's structure intact.

http://beginner-sql-tutorial.com/sql-delete-statement.htm

 

thank the lord.. i haven't tried it out yet to see the time difference but i'm certainly optimistic that i can finally pull off the deletes in a reasonable amount of time

 

Definitely use Truncate (Truncate table table_name).

DELETEs get written to log file butr truncate doesn't.

Safer than dropping and recreating because at some time in the future, somebody (maybe even you) is going to change the structure of the table (maybe adding an extra column) and won't know (or will forget) to change the table structure code that you've got saved.

 

sounds like you want to drop and then recreate the table - easy

 

Use Truncate table, as it does not log infomation on a record level, it will be faster than the normal delete statement.

 

Hello,

Use TRUNCATE as DELETE was made for smaller queries such as removing a 'row' with a 'WHERE' condition.

 

Export Db structure as sql file and remove it and rebuild your table or import sql file again.

 

i've been trying to use the truncate command to no avail..

truncate keeps claiming their are foreign key records in another table.. however it will allow me to delete those records..

say i have these tables
TableA
col1 number pk
col2 string

TableB
col3 number fk references TableA.col1
col4 char

I should be able to run a script that would first
Truncate table TableB

And second
Truncate table TableA

shouldn't i?