Performance with pertitioned table

Database
Enthusiast

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

Tags (1)
9 REPLIES
Junior Contributor

Re: Performance with pertitioned table

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.

Enthusiast

Re: Performance with pertitioned table

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

Enthusiast

Re: Performance with pertitioned table

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)))

Enthusiast

Re: Performance with pertitioned table

Hi Ravimans,

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

Re: Performance with pertitioned table

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.

Enthusiast

Re: Performance with pertitioned table

It won't increase the performance.

Enthusiast

Re: Performance with pertitioned table

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.


Hope this helps !

-Samir

Enthusiast

Re: Performance with pertitioned table

Hi Samir,

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

Enthusiast

Re: Performance with pertitioned table

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.