All Forums Database
Moutusi 38 posts Joined 03/13
25 Mar 2015
Performance with pertitioned table

Hi,
I have a table having below structure:
CREATE MULTISET GLOBAL TEMPORARY TABLE T1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID DECIMAL(18,0),
STRT_DT TIMESTAMP(0),
COL1, COL2, .....)
PRIMARY INDEX ( ID )
 
I have to frequently join this table with other tables on condition of ID and  year part of SRT_DT column.
 
1) If I do range partition on SRT_DT in T1 table will it improve the performance?
2) Will Teradata take more time to insert into a partitioned table than a non-pertitioned table?
 
Thanks,
Moutusi
 

Thanks, Moutusi
dnoeth 4628 posts Joined 11/04
25 Mar 2015

Hi Moutusi,
Q1: Like this? extract(year from STRT_DT) = 2015?
No, I don't think that partition elimination will work for this case.
 
Q2: No, definitely not. It's exactly the same.

Dieter

Moutusi 38 posts Joined 03/13
25 Mar 2015

Thanks Dieter for your response.
 
No I have join condition like below:
T1.STRT_DT>= CAST(T2.STRT_YR AS TIMESTAMP(0)))
T2 has structure like:
STRT_YR VARCHAR(100)
and T2.STRT_YR value can be 2013.
 
Thanks,
Moutusi

Thanks,
Moutusi

ravimans 54 posts Joined 02/14
26 Mar 2015

Hi Moutusi,

 

You can try the same way as Dieter mentioned above. Try the below menthod:

extract(year from T1.STRT_DT) >= CAST(T2.STRT_YR AS TIMESTAMP(0)))

Moutusi 38 posts Joined 03/13
26 Mar 2015

Hi Ravimans,
My question was whether using partition in such scenario will increase the performance or not.

Thanks,
Moutusi

Rmsranjith 4 posts Joined 03/09
26 Mar 2015

You won't find difference on time taken to insert. But you may find a difference on time taken to retrieve data from this table if your retrieving queries use PPI fields wisely and enable partition elimation.
 
 

ravimans 54 posts Joined 02/14
27 Mar 2015

It won't increase the performance.

teradatauser2 236 posts Joined 04/12
28 Mar 2015

Hi,

Why dont you cast 

 

STRT_DT TIMESTAMP(0)

 

to date and partition on it. Starting TD14, you can use the column in where predicate by casting to date and get partition elimination.

Please refer to below link for details about enhancements in partition elimination statrting TD14.

http://developer.teradata.com/blog/paulsinclair/2012/07/td-14-0-the-other-partitioning-enhancements

Hope this helps !

-Samir

Moutusi 38 posts Joined 03/13
01 Apr 2015

Hi Samir,
I can't change the datatype of STRT_DT as it is being used with timestamp values for other queries.

Thanks,
Moutusi

AndrewSchroter 14 posts Joined 11/06
06 Apr 2015

One solution is to consider adding a derived start_year column to the table and partition that column.  Queries using that column as a predicate qualifier should leverage the start_year column.

You must sign in to leave a comment.