Urgent : recommendation for partitions

Visit Teradata.com

Home

Guidelines

Member List

Welcome Guest ( Login | Register )

This online forum is for user-to-user discussions of Teradata products, and is not an official customer support channel for Teradata. If you require direct assistance, please contact Teradata support.

Urgent : recommendation for partitions

Rate Topic

Display Mode

Topic Options

Author

Message

Mahesh

Posted 7/25/2008 4:47:41 PM

Junior Member

Group: Forum Members
Last Login: 8/22/2008 11:05:51 PM
Posts: 15, Visits: 31

Hi Experts,

I need recommendation for following partitions

1) PRIMARY INDEX TABLE1 ( P_KEY )
PARTITION BY RANGE_N(P_YYMM_DT BETWEEN 1001 AND 1002 EACH 1 ,
1011 AND 1012 EACH 1 ,
1021 AND 1022 EACH 1 ,
1031 AND 1032 EACH 1 ,
1041 AND 1042 EACH 1 ,
1051 AND 1052 EACH 1 ,
1061 AND 1062 EACH 1 ,
1071 AND 1072 EACH 1 ,
1081 AND 1082 EACH 1 ,
1091 AND 1092 EACH 1 )

PRIMARY INDEX TABLE2 ( P_KEY )
PARTITION BY RANGE_N(P_YYMM_DT BETWEEN 1001 AND 1002 EACH 1 ,
1011 AND 1012 EACH 1 ,
1021 AND 1022 EACH 1 ,
1031 AND 1032 EACH 1 ,
1041 AND 1042 EACH 1 ,
1051 AND 1052 EACH 1 ,
1061 AND 1062 EACH 1 ,
1071 AND 1072 EACH 1 ,
1081 AND 1082 EACH 1 ,
1091 AND 1092 EACH 1 )

2) PRIMARY INDEX TABLE1 (P_YYMM_DT ,P_KEY )
PARTITION BY RANGE_N(P_YYMM_DT BETWEEN 1001 AND 1002 EACH 1 ,
1011 AND 1012 EACH 1 ,
1021 AND 1022 EACH 1 ,
1031 AND 1032 EACH 1 ,
1041 AND 1042 EACH 1 ,
1051 AND 1052 EACH 1 ,
1061 AND 1062 EACH 1 ,
1071 AND 1072 EACH 1 ,
1081 AND 1082 EACH 1 ,
1091 AND 1092 EACH 1 )

PRIMARY INDEX TABLE2 (P_YYMM_DT ,P_KEY )
PARTITION BY RANGE_N(P_YYMM_DT BETWEEN 1001 AND 1002 EACH 1 ,
1011 AND 1012 EACH 1 ,
1021 AND 1022 EACH 1 ,
1031 AND 1032 EACH 1 ,
1041 AND 1042 EACH 1 ,
1051 AND 1052 EACH 1 ,
1061 AND 1062 EACH 1 ,
1071 AND 1072 EACH 1 ,
1081 AND 1082 EACH 1 ,
1091 AND 1092 EACH 1 )

Which partition is recommend for below quires either 1 or 2

SEL *
FROM
TABLE1A 1A, TABLE1B 1B
WHERE
1A.P_KEY = 1B.P_KEY
AND
1A.P_YYMM_DT = 1B.P_YYMM_DT

SEL *
FROM
TABLE1A 1A, TABLE1B 1B
WHERE
1A.P_KEY = 1B.P_KEY
AND
1A.P_YYMM_DT = 1086
AND
1B.P_YYMM_DT = 1086

Thanks in advance,
Mahesh.

Post #12252

gander_ss

Posted 7/31/2008 8:01:20 AM

Forum Member

Group: Forum Members
Last Login: Today @ 8:42:44 AM
Posts: 41, Visits: 93

Hi mahesh,

whta is the relation in b/w P_KEY and P_YYMM_DT for both the tables.

i.e. it's 1:m ,m:1,1:1 or m:m...............

Also in table what is the UPI??????

Regards,
Subhash

Regards,
Subhash

Post #12397