Encoding in Create Table Statement

Comments

• Options

  TomM

  August 2017

  Hi Daril. Welcome to Vertica. My guess is you're somewhat new to the product, however, if that's not the case no disrespect intended. You probably know Vertica is a column store. Each column is stored as its own object on disk. Because of this every single column is encoded or compressed. When you CREATE TABLE AS SELECT (CTAS) you get an AUTO super projection. That AUTO super projection uses AUTO encoding (mildly confusing). AUTO encoding is OK, and you can do better. You want to do better because it reduces your footprint on disk. Additionally, we can operate on encoded data (as opposed to compressed data). This contributes to query performance. The easy way to get encoding recommendations is via the Database Designer, whether you run it programmatically through Admintools, Management Console, or via the command line through the API's we expose. If you want to do it manually, via CTAS or otherwise, you'll need to look at the data type of each column, consult the documentation on encoding / compression for a recommendation, and apply your best guess. We offer a variety of training on Vertica, and I think you can save yourself some time and effort. Check out https://inter.viewcentral.com/events/cust/catalog.aspx?cid=arcsight&pid=1&lid=2&bu_id=6

  0