I need to drop huge database table having 1.7 Billion records.
DB: Sybase, DB size: 350 gb.
I ran 'drop table' statement and its taking more than 18 hours. Is there any other way to optimize it.
Asked
Active
Viewed 815 times
0
-
Does your RDMS support TRUNCATE. In sql server this command bypasses logging and just rearranges pointers and it is a snap executing in a few seconds. – Ross Bush Feb 04 '15 at 20:52
-
Disregard my earlier answer. I thought you meant delete all records from a table in which case TRUNCATE would be preferred. I do not know which would be more performant a DROP or TRUNCATE DROP. DROP probably performs a TRUNCATE before all metadata is cleared. I have deleted my answer. – Ross Bush Feb 04 '15 at 21:26
-
I don't know how sybase works, but in mysql you can delete those database files from the filesystem. – Aitch Feb 07 '15 at 23:52
-
How about `DROP DATABASE`? – Code Different Feb 07 '15 at 23:53
-
@ZoffDino No, I cant. Don't want to delete other tables – suraj_fale Feb 10 '15 at 19:58
-
Is there something in the logs? can you post the log? – Ofir Winegarten Feb 24 '15 at 19:05
-
@OfirW Yeah. That's another issue. Due transaction logs, space is getting filled up. – suraj_fale Feb 25 '15 at 14:53
-
If you want to keep the log of the delete you shouldn´t use truncate because it is a minimally logged operation . In case you are running out of space use _disk resize _ on the device in which the log is mounted. There is a parameter equivalent to mysql innodb_flush_log_at_trx_commit that waits certain time before saving the buffer log to the file log I don´t remember right now which is the name but as soon as i remember I´ll post it. – jcromanu May 30 '15 at 03:41
1 Answers
1
This post is too big for a comment. If it doesn't help I'll delete it. I found the following on a sybase forum ie make sure all foreign keys etc have been removed.
http://nntp-archive.sybase.com/nntp-archive/action/article/%3C4a3b0950.27df.1681692777@sybase.com%3E
P.S. I found a workaround that consistently speeds up the table and view drops: DROP FOREIGN KEYS from the database first. My script that drops tables and views was previously taking 5 hours (about 20 seconds to drop each object); if FKs are dropped first, it finishes in 20 minutes.
They did it for the whole database in your case you would need to do it for the table in question, I'm not sure this will help because the docs here
Actually say that foreign keys etc are automatically dropped in tables that reference the one you are dropping. Although it does not mention what happens if those keys are currently part of a running transaction when you call drop.
Harry
- 11,298
- 1
- 29
- 43