4
I have a table with about 4 million records.
What is the best method to delete all of them to get the best time?
Indexing helps something, since it is only a reference to find data?
OBS
`DELETE FROM tb_name;` // Demorou em torno de 45min
2 answers
6
Use TRUNCATE to delete all records.
TRUNCATE table;
It is an optimized method for complete deletion of records. If you want to propagate the effect to tables that reference a primary key in the table suffering TRUNCATE, use the parameter CASCADE;
TRUNCATE table CASCADE;
Source:
6
Truncate has better performace than a delete without Where because the first does not log the deleted lines while the second does it for each one.
TRUNCATE table nome can be translated by the bank as, deleted the records, truncate is DML.
DELETE FROM nome, is translated as, I logged record 1, deleted record 1 ... Until N. delete is a DML
-
I think TRUNCATE is logged by operation (instead of registration - that is, you can give a ROLLBACK in a TRUNCATE), but only DROP/CREATE is considered DDL (but I could be wrong! ) ) - http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx
-
@Onosendai, I think I made a mistake, truncate seems to be a DDL in oracle.
-
Ah, true - in Oracle Truncate is treated as DDL.
-
Both answers are very good, and are complementary, I am in doubt on which to mark as correct. In yours you bring the issue of the log ja in @Onosendai it brings the question of
CASCASE. -
@Guilhermelautert toss a coin. = ) The rray response is excellent.
Browser other questions tagged sql postgresql runtime
You are not signed in. Login or sign up in order to post.
If you can use truncate. Which bank is?
– rray
@rray postgresql.
– Guilherme Lautert
Maybe dropping a table and recreating will be faster.
– Mauricio Ferraz