Urgent : recommendation for partitions

Database
Enthusiast

Urgent : recommendation for partitions

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.
3 REPLIES
Enthusiast

Re: Urgent : recommendation for partitions

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
Enthusiast

Re: Urgent : recommendation for partitions

Relation is 1:M
There is no UPI only PI

Thanks for your response

Regards,
Mahesh
Enthusiast

Re: Urgent : recommendation for partitions

Thanks