Column encoding change on vertica 10 not work
sergey_h
Vertica Customer ✭
Hello,
I try change column encoding, but it doesn't work as expected.
My test:
I have table :
openx=> select export_objects('','test.test1'); export_objects ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ CREATE TABLE test.test1 ( language_id int ); CREATE PROJECTION test.test1 /*+createtype(P)*/ ( language_id ) AS SELECT test1.language_id FROM test.test1 ORDER BY test1.language_id SEGMENTED BY hash(test1.language_id) ALL NODES KSAFE 1;
Check size
SELECT projection_name, column_name, encodings, compressions, ROUND(SUM(used_bytes)/(1024^2),1)::FLOAT AS size_Mb FROM column_storage WHERE anchor_table_name = 'test1' and projection_schema = 'test' -- and projection_name = 'mnt_compiled_stats_full_new_b0' GROUP BY 1,2,3,4 ORDER BY 5 DESC; projection_name | column_name | encodings | compressions | size_Mb -----------------+-------------+--------------+--------------+--------- test1_b0 | language_id | Uncompressed | int delta | 7005 test1_b1 | language_id | Uncompressed | int delta | 7005 test1_b0 | epoch | Int_Delta | none | 41 test1_b1 | epoch | Int_Delta | none | 41
Tnen, i change encoding and execute mergeout
openx=> alter table test.test1 alter column language_id encoding BLOCK_DICT projections (test.test1_b0,test.test1_b1); WARNING 9385: New encoding will not be applied to existing projection data of this table; this change will be applied only on mergeout ALTER TABLE openx=> select do_tm_task('mergeout','test.test1'); do_tm_task ----------------------------------------------------------------------------------------------------------------- Task: mergeout (Table: test.test1) (Projection: test.test1_b0) (Table: test.test1) (Projection: test.test1_b1) (1 row)
Check size again - nothing changed.
projection_name column_name encodings compressions size_Mb --------------- ----------- ------------ ------------ ------- test1_b0 language_id Uncompressed int delta 7005.0 test1_b1 language_id Uncompressed int delta 7005.0 test1_b0 epoch Int_Delta none 41.0 test1_b1 epoch Int_Delta none 41.0
Vertica vertsion
openx=> SELECT version(); version ------------------------------------- Vertica Analytic Database v10.0.0-3
0
Answers
I tried your scenario in 10.0.1, and the result was as expected.
[Before]
[After]
Please check the encoding type has been changed on your projection on exported DDL exported by the following statement.
When you ran the mergeout, did you see any error message in vertica.log file?
Hello @Hibiki,
DDL projection is changed, but old data not re-encoding.
No, i didn't see, mergeout worked without errrors in vertica.log
Can you check the projection definitions and the histories of mergeout by using the following queries?
When i execute next insert in this table and run mergeout - TM re-encoding
And column size changed
Thank you for sharing the detailed information. I guess Mergeout you ran after changing the column encoding didn't need to merge the ROS containers. Can you check the number of ROS files when you run Mergeout?