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