Have you ever wanted to delete a table with millions of rows? Using
DELETE FROM TABLE1 SQL statement? Then you know this is very, very slow and it fills transaction logs. The fastest delete methond I've ever seen goes little like this:
1.) First you export 0 rows from the table:
EXPORT TO TABLE_NAME.IXF OF IXF SELECT * FROM TABLE_NAME WHERE 1 = 2
2.) Then you do a LOAD with REPLACE:
LOAD FROM TABLE_NAME.IXF OF IXF REPLACE INTO TABLE_NAME
I've been able to delete rows from tables with tens of millions of rows in a second.
I hope this little tip is usefull for a lot of DBA's out there.
3 komentarji:
sir,
this is a great way to delete rows from big tables!! i love it
This works really fine! However you can do it using a single command:
LOAD FROM /dev/null OF IXF REPLACE INTO SCHEMA.TABLE_NAME
Your solution works great. I am glad that I have googled your blog entry.
Best regards,
Holger
www.glucosurfer.org
Objavite komentar