Skip to main content

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

  • January 28, 2017
  • 1 reply
  • 7 views
Erick

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,

 

 

 

    1 reply

    Vertica_Curtis
    Forum|alt.badge.img+1
    • Vertica_Curtis
    • Participating Frequently
    • March 17, 2017

    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.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement