Partitioning by a character column

Database

Partitioning by a character column

I want to partition by column that has various single character values e.g. 'y', 'n', etc. My understanding is that it needs to be translated to an INTEGER value through a case statement. Is this correct? If so, does anyone have an example.
9 REPLIES
N/A

Re: Partitioning by a character column

Partitioning by chars is not possible, yet. Thus you can't use any character functions like POSITION for partitioning.

There's a trick to bypass that restriction, but i doubt it's really usefull:
e.g. PARTITION BY RANGE_N (HASHBUCKET(HASHROW(col)) MOD 10000 BETWEEN 0 AND 9999 EACH 1)

Dieter

Re: Partitioning by a character column

Hello,
I have been looking for answers to a basic question on table partitioning in Teradata. Can someone please shed more light. Here it is -

If we do Range Partitioning on a table based on Numeric values and then different jobs load data corresponding to different partition ranges in parallel - then do we get into a resource contention scenario OR do all parallel running jobs hit specific partition and only acquire lock on a particular partition. In short - the question is - while loading data into a partitioned table [in this case Range Partition], do we acquire a lock at the partition level and multiple programs can run in parallel acquiring lock at individual partitions OR the lock is acquired at whole table level and the parallel jobs get into a contention mode.

Ex: An error table where each program loads specific error information that is encountered based on error code. If the table is range partitioned on error codes of range of 1000s, will individual programs when run in parallel hit their specific partition acquiring only lock to it or they will acquire complete table lock and release them for subsequent jobs when error load is done.

Please advice.
Teradata Employee

Re: Partitioning by a character column

Hi Dieter,

Does including hashbucket/MOD function in TD13.10 cause any performance issues. I mean if I can use CHAR-PPI in 13.10 over the HASHBUCKET/MOD approach am I likely to see a performance benefit?

Thanks'

Sandesh R Gitte

N/A

Re: Partitioning by a character column

Hi Sandesh,

you will get partition elimination only for equality when using HASHBUCKET.

So CHAR-PPI is the preferred sollution in 13.10.

Teradata Employee

Re: Partitioning by a character column

Be careful using hashbucket in partitioning. It can cause issues when upgrading due to different hashmaps. 

Re: Partitioning by a character column

Hi,

I have a table with PPI on date column Fill_DT defined as fill_dt BETWEEN '2001-01-01'(DATE) AND '2014-12-31'(DATE) EACH INTERVAL '1' MONTH

Now I want to know who all users accessed partitions of 2008 and below.

I am doing it with dbql object and log table join and then drilling each sql but that realy difficult as the sqls are more than 10000 is there any way around for this.

Re: Partitioning by a character column

Hi,

Can anybody pls reply me.How to check wheather or not data related to particular partition is accessed or not.Is their any table capturing history data with partition ID.

Teradata Employee

Re: Partitioning by a character column

There is not a table or log that captures which partitions are used.

Also, be cautious when looking at the SQL for specific date qualifications because in many cases the partition inclusion/exclusion is done through Dynamic Partition Elimination during joins to reference tables (eg in your case joins through a calendar reference table/heirarchy).

Re: Partitioning by a character column

But that make sense to release space for partitions which are not used from long.In my case ppl who are not using partition 2008 and below I want to delete that data and free space(as part of system retirement).

Is there no way to do it.