jeudi 31 décembre 2015

Remove unused data inside sqlite

I use this topic to remove some columns.

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

But the size of the database is the same as before. If use a deep copy for each table of the old database, the size of new one dramatically decrease with respect to the number of new columns in new tables:

BEGIN TRANSACTION;
CREATE TABLE NEWDB.tb (c1 INTEGER PRIMARY KEY ,c2);
INSERT INTO NEWDB.tb (c1,c2) SELECT c1,c2 FROM tb;
COMMIT;

In my case, the size of old table is 220MB, the new one - 5 MB. What is 215MB? This is because of there are a lot of used data in the old table. Am I right? What kind of data consumes so much disk space? Is there the other methods to decrease the size of the table without the deep copy?

Aucun commentaire:

Enregistrer un commentaire