Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Database Designer is creating a design with some non recomended column list encodings types

ErickErick
edited January 2017 in General Discussion

Hello, I am a bit confused using the Database Designer and the encoding and compression types details described in reference manual: 

https://my.vertica.com/docs/8.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/encoding-type.htm

 

When Database Designer is run in comprehensive mode for VMartDB database using sample queries in vmart_queries.sql, it creates a design (VMartDesign01_design.sql) with projections using non-recomended column list encoding types for some columns in the following anchor tables: 

 

1- Anchor table "product.customer_dimension":
- Column "customer_type" has been configured automatically using AUTO encoding but this is a low cardinality column with only two values (Individual and Company). Why this column has not been configured to use RLE encoding?

 

2- Anchor table "store.store_sales_fact":
- Column "product_key" has been configured automatically using RLE encoding but this is a high cardinality column with more than 830 thousand many distinct and unique values. Why this column has not been configured to use DELTAVAL encoding?

 

Thank you in advance for your clarifications.

 

Regards,

 

 

 

Comments

  • Compression types are driven a lot by the ORDER BY clause of the projection definition. It's possible that the query you've supplied to the table is doing something that's creating an interesting ORDER for the projection, which has affected the compression/encoding types.

    A better solution might be to use a function like designer_design_projection_encodings() which will provide for you an independent list of column encoding types - and then you can modify the ORDER BY and the SEGMENTATION clauses independently on top of those recommended encoding types.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.