Stephane Vander Clock wrote:
When I do DROP TABLE, sometimes it takes hours to return (especially on big table). Why?
Ann W. Harrison answers:
Dropping a big table with lots of indexes will take longer than dropping an empty table because of the amount of work done. Each page used by the table must be marked free on its Page Inventory Page.
To release a data page, Firebird reads the RDB$PAGES table to find pointer pages and marks the pages listed on the pointer pages as free until the page is empty, then removes the pointer page entry from RDB$PAGES, then marks the pointer page as free on its page inventory page. If enough entries are removed from RDB$PAGES to free up pages there, then those pages are also released, using the same algorithm.
To release index pages, Firebird must read the whole index. It's been a very long time since I looked at the code that removes an index, but my recollection is that it goes to the lowest level of the index and releases pages from left to right, following the sibling pointers, then starts from the top again and releases the next level up.
Checking the performance statistics for the number of pages read and written will help you understand what's going on.
Pavel Cisar adds:
With FORCED WRITES (ON), each changed page is immediately written back to disk. Taking into account the number of pages changed during DROP of large table with lot of indices, it's most likely the reason you see so bad performance. Turn FORCED WRITES OFF before DROP operation.