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.
