CODEWITHSUNDEEP
teradata
DeepNightTwo
DeepNightTwo

Reputation: 4961

Using varchar column as partition in Teradata to speedup truncate

I have a Teradata table and two columns company_name varchar(500) and case_name varchar(500).

The value of the two partitions are limited in hundreds level. But it is not controlled by me. And I can't predefine a set of their value.

It is a daily operation to truncate all data of specified company_name and case_name. So I want to use these two columns as partitions.

Is it supported to do so? And will it helps if truncate data by partition in TD? If this is not supported. Is there a best practice to truncate data by two varchar columns?

Upvotes: 0

Views: 443

Answers (1)

dnoeth
dnoeth

Reputation: 60482

When the access to those columns is (mainly) based on where company_name = 'foo' and case_name = 'bar' you can apply a calculation like this

PRIMARY INDEX ( PIcol)
PARTITION BY
   Range_N(HashBucket(HashRow(company_name,case_name)) MOD 65533 BETWEEN 0 AND 65532 EACH 1)

A delete from where company_name = 'foo' and case_name = 'bar' will access a single partition, but it's not a FastPath delete, it will be transient journaled.

Upvotes: 2

Related Questions