Running query that deletes all rows from a table in Oracle or Microsoft SQL can take extraordinary amounts of time and disk space on huge tables. Query such as
delete from calcerrorlog
takes time because transaction log entry is written for EACH row within the table. If you take a table with 50 million rows for instance, it is a lot of log entries. The log is also persisted to disk, which means a query like that can grow the transaction log file and several gigabytes. depending on the width of the table it may be on the order of 10's of gigabytes.
HOWTO Solution : Use TRUNCATE TABLE command instead. Such as
truncate table calcerrorlog
This function writes one transaction log entry, and is basically instantenous. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table.
Consult documentation for both Oracle and MSSQL on how to use it. There are a few gotcha's that you have to be aware of, such as
- TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
- You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
- TRUNCATE TABLE may not be used on tables participating in an indexed view.
Once again, consult documentation for your current version of DBMS before using this.