I have a table having below structure:
CREATE MULTISET GLOBAL TEMPORARY TABLE T1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
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?
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.
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:
and T2.STRT_YR value can be 2013.
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.
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.