Multi-Partitioning - how to hit the partition?

Database

Multi-Partitioning - how to hit the partition?

Hi,

I need to rewrite the following query in order to hit the partition. I'm not an teradata-expert and couldn't find a solution in the internet, if you have an idea how to solve my problem, then please post it. Thx a lot!

table "t_day" - having Primary Index on column 'day'
table "t_fact" - huge table with primary index on the columns 'day','col1','col2' and multipartitioned on column 'col2' and 'day'

Query:

sel a.day, sum(a.cost)
from t_fact a
join t_day b
on (a.day=b.day)
where b.week in (1,2,3);

Changing the where condition to "where b.day in (1,2,3)" will hit the partition, just the problem is that this where condition on week is hardcoded in a many many SQLs and to replace that by a condition on day would be a pain in the neck. That's why we would need to create a view which allows filtering on week but still hits the partition on day. I also tried to use sys_calendar but without any improvement of the performance.

Question: Would it help to create a partition 'day' or a partition 'week' for the table "t_day"? Do you have any other idea how I can have this partition on day, a where condition on week but still hit the day-partition?

Thanks in advance!
Lotta
3 REPLIES
Teradata Employee

Re: Multi-Partitioning - how to hit the partition?

You can't get "static" partition elimination, but you may be able to get a join plan that is "enhanced by dynamic partition elimination". What stats have you collected on t_day? How about multicolumn stats on (week,day)? Have you collected stats on pseudo-column PARTITION for t_fact?
Enthusiast

Re: Multi-Partitioning - how to hit the partition?

I think the condition nerver use the column "day", so it doesn't hit the partition. If you want to hit the partition, so you should filter the conditon of column "day".
Enthusiast

Re: Multi-Partitioning - how to hit the partition?

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.