Hybrid columnar automatic format

Database

Hybrid columnar automatic format

Hi,

I'm interested in using the columnar feature, and after doing some intinial research, I found that Teradata automatically determines if a column should be row or column format. I was hoping to get more information on how Teradata actually determines this, and if there is a certain way to enable this, or if the DDLs have to be in a certain format? 

I did find many resources online with sample DDLs. In some, after the PARTITION BY COLUMN statement, the columns are specfied, and then ROWs are specfied. In others, they are not specified at all:

create table employee_hybrid

(emp_no integer,

dept_no integer,

first_name varchar(20),

last_name varchar(20),

salary decimal(10,2))

no primary index

partition by column

(emp_no no auto compress,

dept_no

,Row (first_name, last_name, salary)

no auto compress);
create table employee_columnar

(emp_no integer,

dept_no integer,

first_name varchar(20),

last_name varchar(20),

salary decimal(10,2))

no primary index

partition by column;

My question is, if Teradata automatically deterimnes which format to store, then how exactly does it do so, and what format would the DDLs have to be in? Would the second example be enough?

I also found a ppt deck from Teradata labs here: 


The ppt indicates that Teradata automatically determines which format, but doesn't go into much detail afterwards. Any insight would be appreciated.

Thanks

2 REPLIES
Senior Apprentice

Re: Hybrid columnar automatic format

I would suggest reading the manuals, there should be lots of details in the DDL and the Database Design manuals plus the Columnar Orange Book.

Dieter

Teradata Employee

Re: Hybrid columnar automatic format

Teradata does not automatically determine if a table should be row format or column format. That needs to be specified by the DBA at physical table definition time. The examples you provide show a couple of the options, the second is the most simple version making every column column storage. The first is a more complex example which shows a mix of column store and row store in the same table. 

What Teradata does do automatically is to determine what compression algorithms to apply to the data in column stored columns (unless you tell it not to with No Auto Compression).