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.
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/%[email protected]%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.