[FAQ] Détecter les cardinalités

Version imprimable

25/10/2008, 16h04
nuke_y

[FAQ] Détecter les cardinalités

Bonjour à tous.

Dis donc ça faisait un bail que je n'étais pas venu tailler une bavette ici 8O

On reconnaît rien, vous avez même changé le papier peint non ?

Bon bref, alors je suis venu discuter un peu des méthodes pour détecter les cardinalités entre 2 tables. En effet je travaille sur Cognos en ce moment et c'est le genre d'outil qui ne pardonne pas les mauvaises cardinalités dans les jointures entre les tables.

J'ai donc écrit dans mon temps libre une fonction qui détecte les cardinalités et le résultat ne me semble pas trop mal alors je suis venu vous le présenter pour que
1) ça serve à d'autres
2) vous me disiez ce qui ne va pas / les améliorations à apporter
Par exemple, je fais certaines requêtes pour détecter les cardinalités, et je suis sûr qu'on pourrait faire la même chose en moins de requêtes (ou moins coûteuses).

Sur le principe de détermination, j'ai utilisé un algo qui travaille par détection :
- si 0 lignes dans au moins une table -> on ne peut se prononcer
- si au moins 1 clé de jointure d'une table n'existe pas dans l'autre table alors on a au moins une relation 0 - ?
- si au moins 1 clé de jointure existe en double dans une table (même si elle n'existe pas dans l'autre table) alors on a au moins une relation ? - n
- sinon on a une relation 1 - 1.

Pour le résultat, j'ai prévu plusieurs formats de court à détaillé, mais tous en modélisation Merise.

Merci de vos tests / retours / conseils / critiques.

PS : s'il y a des bêtises, c'est pas ma faute j'ai codé/testé ça avec ma fille sur les genoux :roll:

Create_Cardinalities_Discoverer.sql

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
CREATE OR REPLACE function F_find_cardinalities
(p_left_table IN varchar2, p_left_table_key IN varchar2, p_right_table IN varchar2, p_right_table_key IN varchar2, p_verbose IN varchar2 ) RETURN VARCHAR2 IS
v_left_table_count NUMBER(10);
v_left_table_key_count NUMBER(10);
v_innerjoin_left_tab_key_cnt NUMBER(10);
v_innerjoin_exist_n_right_val NUMBER(10);
v_right_table_count NUMBER(10);   
v_right_table_key_count NUMBER(10);
v_innerjoin_right_tab_key_cnt NUMBER(10);
v_innerjoin_exist_n_left_val NUMBER(10);
v_min_T1 CHAR(1);
v_max_T1 CHAR(1);
v_min_T2 CHAR(1);
v_max_T2 CHAR(1);
v_min_T1_detailed_merise VARCHAR2(4);
v_max_T1_detailed_merise VARCHAR2(4);
v_min_T2_detailed_merise VARCHAR2(4);
v_max_T2_detailed_merise VARCHAR2(4);
v_detailed_merise_report_T1 VARCHAR2(4000);
v_detailed_merise_report_T2 VARCHAR2(4000);
v_result varchar2(4000);
BEGIN
v_min_T1:='.';
v_max_T1:='.';
v_min_T2:='.';
v_max_T2:='.';
v_min_T1_detailed_merise:='n/a';
v_max_T1_detailed_merise:='n/a';
v_min_T2_detailed_merise:='n/a';
v_max_T2_detailed_merise:='n/a';
v_result:='';
EXECUTE IMMEDIATE 'select count(*), count(distinct T1.'||p_left_table_key||') from '||p_left_table||' T1' INTO v_left_table_count, v_left_table_key_count;
EXECUTE IMMEDIATE 'select count(*), count(distinct T2.'||p_right_table_key||') from '||p_right_table||' T2' INTO v_right_table_count, v_right_table_key_count;
 
EXECUTE IMMEDIATE 'select count(distinct T1.'||p_left_table_key||'), count(distinct T2.'||p_right_table_key||') from '||p_left_table||' T1 INNER JOIN '||p_right_table||' T2 ON T1.'||p_left_table_key||' = T2.'||p_right_table_key INTO v_innerjoin_left_tab_key_cnt, v_innerjoin_right_tab_key_cnt;
 
If v_left_table_count = 0  OR v_right_table_count = 0 Then
	v_min_T1:='.';
	v_max_T1:='.';
	v_min_T2:='.';
	v_max_T2:='.';
	Else
	v_min_T1:='1';
	v_max_T1:='1';
	v_min_T2:='1';
	v_max_T2:='1';
	If v_innerjoin_right_tab_key_cnt = 0 Then	
		v_min_T1 := '0';
		v_max_T1 := '0';
	Else
		If v_innerjoin_right_tab_key_cnt < v_right_table_key_count Then
			v_min_T1 := '0';
		End If;
		If v_left_table_key_count < v_left_table_count Then
			v_max_T1 := 'n';
		End If;
	End If;	
 
	If v_innerjoin_left_tab_key_cnt = 0 Then	
		v_min_T2 := '0';
		v_max_T2 := '0';
	Else
		If v_innerjoin_left_tab_key_cnt < v_left_table_key_count Then
			v_min_T2 := '0';
		End If;
		If v_right_table_key_count < v_right_table_count Then
			v_max_T2 := 'n';
		End If;
	End If;	
End If;
 
If UPPER(p_verbose) is null or length(UPPER(p_verbose)) = 0 Then
	v_result:= v_min_T1||'..'||v_max_T1||' - '||v_min_T2||'..'||v_max_T2;
Elsif UPPER(p_verbose) = 'SHORT_MERISE' Then
	v_result:= v_min_T1||'..'||v_max_T1||' - '||v_min_T2||'..'||v_max_T2;
Elsif UPPER(p_verbose) = 'MERISE' Then
	v_result:= p_left_table||'.'||p_left_table_key||' ('||v_min_T1||'..'||v_max_T1||') - ('||v_min_T2||'..'||v_max_T2||') '||p_right_table||'.'||p_right_table_key;
Elsif UPPER(p_verbose) = 'DETAILED_MERISE' Then
	If v_min_T1 = '0' Then
		v_min_T1_detailed_merise:= 'zero';
	Elsif v_min_T1 = '1' Then
		v_min_T1_detailed_merise:= 'one';
	Elsif v_min_T1 = 'n' Then
		v_min_T1_detailed_merise:= 'n';
	Else
		v_min_T1_detailed_merise:= 'n/a';
	End If;
	If v_max_T1 = '0' Then
		v_max_T1_detailed_merise:= 'zero';
	Elsif v_max_T1 = '1' Then
		v_max_T1_detailed_merise:= 'one';
	Elsif v_max_T1 = 'n' Then
		v_max_T1_detailed_merise:= 'n';
	Else
		v_max_T1_detailed_merise:= 'n/a';
	End If;
	If v_min_T2 = '0' Then
		v_min_T2_detailed_merise:= 'zero';
	Elsif v_min_T2 = '1' Then
		v_min_T2_detailed_merise:= 'one';
	Elsif v_min_T2 = 'n' Then
		v_min_T2_detailed_merise:= 'n';
	Else
		v_min_T2_detailed_merise:= 'n/a';
	End If;
	If v_max_T2 = '0' Then
		v_max_T2_detailed_merise:= 'zero';
	Elsif v_max_T2 = '1' Then
		v_max_T2_detailed_merise:= 'one';
	Elsif v_max_T2 = 'n' Then
		v_max_T2_detailed_merise:= 'n';
	Else
		v_max_T2_detailed_merise:= 'n/a';
	End If;
	If v_min_T2 = v_max_T2 Then
		v_detailed_merise_report_T1:= 'For each '||p_left_table||' there is '||v_min_T2_detailed_merise||' '||p_right_table||'.';
	Else
		v_detailed_merise_report_T1:= 'For each '||p_left_table||' there is '||v_min_T2_detailed_merise||' or '||v_max_T2_detailed_merise||' '||p_right_table||'.';
	End If;
	If v_min_T1 = v_max_T1 Then
		v_detailed_merise_report_T2:= 'For each '||p_right_table||' there is '||v_min_T1_detailed_merise||' '||p_left_table;
	Else
		v_detailed_merise_report_T2:= 'For each '||p_right_table||' there is '||v_min_T1_detailed_merise||' or '||v_max_T1_detailed_merise||' '||p_left_table||'.';
	End If;
	v_result:= p_left_table||'.'||p_left_table_key||' ('||v_min_T1||'..'||v_max_T1||') - ('||v_min_T2||'..'||v_max_T2||') '||p_right_table||'.'||p_right_table_key||'. '||v_detailed_merise_report_T1||' '||v_detailed_merise_report_T2;
Else
	v_result:= 'Incorrect Verbose parameter specified : ['||p_verbose||']';
End If;
 
RETURN v_result;
 
END;
/
 
Show errors;

ATTENTION : ça créé des tables Test1 et Test2 et ça les efface !!!

Test_Cardinalities_Discoverer.sql

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
CREATE TABLE Test1 (ID number);
CREATE TABLE Test2 (ID number);
 
select 'Case 1' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 2' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 3' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test2 VALUES (1);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 4' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (2);
Insert into Test2 VALUES (1);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 5' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (2);
Insert into Test2 VALUES (1);
Insert into Test2 VALUES (3);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 6' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (2);
Insert into Test1 VALUES (2);
Insert into Test2 VALUES (2);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 7' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (3);
Insert into Test2 VALUES (3);
Insert into Test2 VALUES (3);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 8' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (2);
Insert into Test1 VALUES (2);
Insert into Test1 VALUES (3);
Insert into Test2 VALUES (2);
Insert into Test2 VALUES (3);
Insert into Test2 VALUES (3);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 9' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (2);
Insert into Test2 VALUES (3);
Insert into Test2 VALUES (4);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 10' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (3);
Insert into Test2 VALUES (1);
Insert into Test2 VALUES (3);
Insert into Test2 VALUES (3);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;
 
select 'Case 11' "Test" FROM DUAL;
Delete from Test1;
Delete from Test2;
Insert into Test1 VALUES (1);
Insert into Test1 VALUES (2);
Insert into Test1 VALUES (2);
Insert into Test1 VALUES (3);
Insert into Test2 VALUES (2);
Insert into Test2 VALUES (3);
Insert into Test2 VALUES (3);
Insert into Test2 VALUES (4);
 
select F_find_cardinalities('test1','id','test2','id', null) from DUAL;
select F_find_cardinalities('test1','id','test2','id', '') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'MERISE') from DUAL;
select F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') from DUAL;

La même qui permet de ne pas créer la fonction (par exemple pour lancer sur la production... :roll:)

Run_Cardinalities_Discoverer.sql

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
Set serveroutput on
 
Declare
 
function F_find_cardinalities
(p_left_table IN varchar2, p_left_table_key IN varchar2, p_right_table IN varchar2, p_right_table_key IN varchar2, p_verbose IN varchar2 ) RETURN VARCHAR2 IS
v_left_table_count NUMBER(10);
v_left_table_key_count NUMBER(10);
v_innerjoin_left_tab_key_cnt NUMBER(10);
v_innerjoin_exist_n_right_val NUMBER(10);
v_right_table_count NUMBER(10);   
v_right_table_key_count NUMBER(10);
v_innerjoin_right_tab_key_cnt NUMBER(10);
v_innerjoin_exist_n_left_val NUMBER(10);
v_min_T1 CHAR(1);
v_max_T1 CHAR(1);
v_min_T2 CHAR(1);
v_max_T2 CHAR(1);
v_min_T1_detailed_merise VARCHAR2(4);
v_max_T1_detailed_merise VARCHAR2(4);
v_min_T2_detailed_merise VARCHAR2(4);
v_max_T2_detailed_merise VARCHAR2(4);
v_detailed_merise_report_T1 VARCHAR2(4000);
v_detailed_merise_report_T2 VARCHAR2(4000);
v_result varchar2(4000);
BEGIN
v_min_T1:='.';
v_max_T1:='.';
v_min_T2:='.';
v_max_T2:='.';
v_min_T1_detailed_merise:='n/a';
v_max_T1_detailed_merise:='n/a';
v_min_T2_detailed_merise:='n/a';
v_max_T2_detailed_merise:='n/a';
v_result:='';
EXECUTE IMMEDIATE 'select count(*), count(distinct T1.'||p_left_table_key||') from '||p_left_table||' T1' INTO v_left_table_count, v_left_table_key_count;
EXECUTE IMMEDIATE 'select count(*), count(distinct T2.'||p_right_table_key||') from '||p_right_table||' T2' INTO v_right_table_count, v_right_table_key_count;
 
EXECUTE IMMEDIATE 'select count(distinct T1.'||p_left_table_key||'), count(distinct T2.'||p_right_table_key||') from '||p_left_table||' T1 INNER JOIN '||p_right_table||' T2 ON T1.'||p_left_table_key||' = T2.'||p_right_table_key INTO v_innerjoin_left_tab_key_cnt, v_innerjoin_right_tab_key_cnt;
 
If v_left_table_count = 0  OR v_right_table_count = 0 Then
	v_min_T1:='.';
	v_max_T1:='.';
	v_min_T2:='.';
	v_max_T2:='.';
	Else
	v_min_T1:='1';
	v_max_T1:='1';
	v_min_T2:='1';
	v_max_T2:='1';
	If v_innerjoin_right_tab_key_cnt = 0 Then	
		v_min_T1 := '0';
		v_max_T1 := '0';
	Else
		If v_innerjoin_right_tab_key_cnt < v_right_table_key_count Then
			v_min_T1 := '0';
		End If;
		If v_left_table_key_count < v_left_table_count Then
			v_max_T1 := 'n';
		End If;
	End If;	
 
	If v_innerjoin_left_tab_key_cnt = 0 Then	
		v_min_T2 := '0';
		v_max_T2 := '0';
	Else
		If v_innerjoin_left_tab_key_cnt < v_left_table_key_count Then
			v_min_T2 := '0';
		End If;
		If v_right_table_key_count < v_right_table_count Then
			v_max_T2 := 'n';
		End If;
	End If;	
End If;
 
If UPPER(p_verbose) is null or length(UPPER(p_verbose)) = 0 Then
	v_result:= v_min_T1||'..'||v_max_T1||' - '||v_min_T2||'..'||v_max_T2;
Elsif UPPER(p_verbose) = 'SHORT_MERISE' Then
	v_result:= v_min_T1||'..'||v_max_T1||' - '||v_min_T2||'..'||v_max_T2;
Elsif UPPER(p_verbose) = 'MERISE' Then
	v_result:= p_left_table||'.'||p_left_table_key||' ('||v_min_T1||'..'||v_max_T1||') - ('||v_min_T2||'..'||v_max_T2||') '||p_right_table||'.'||p_right_table_key;
Elsif UPPER(p_verbose) = 'DETAILED_MERISE' Then
	If v_min_T1 = '0' Then
		v_min_T1_detailed_merise:= 'zero';
	Elsif v_min_T1 = '1' Then
		v_min_T1_detailed_merise:= 'one';
	Elsif v_min_T1 = 'n' Then
		v_min_T1_detailed_merise:= 'n';
	Else
		v_min_T1_detailed_merise:= 'n/a';
	End If;
	If v_max_T1 = '0' Then
		v_max_T1_detailed_merise:= 'zero';
	Elsif v_max_T1 = '1' Then
		v_max_T1_detailed_merise:= 'one';
	Elsif v_max_T1 = 'n' Then
		v_max_T1_detailed_merise:= 'n';
	Else
		v_max_T1_detailed_merise:= 'n/a';
	End If;
	If v_min_T2 = '0' Then
		v_min_T2_detailed_merise:= 'zero';
	Elsif v_min_T2 = '1' Then
		v_min_T2_detailed_merise:= 'one';
	Elsif v_min_T2 = 'n' Then
		v_min_T2_detailed_merise:= 'n';
	Else
		v_min_T2_detailed_merise:= 'n/a';
	End If;
	If v_max_T2 = '0' Then
		v_max_T2_detailed_merise:= 'zero';
	Elsif v_max_T2 = '1' Then
		v_max_T2_detailed_merise:= 'one';
	Elsif v_max_T2 = 'n' Then
		v_max_T2_detailed_merise:= 'n';
	Else
		v_max_T2_detailed_merise:= 'n/a';
	End If;
	If v_min_T2 = v_max_T2 Then
		v_detailed_merise_report_T1:= 'For each '||p_left_table||' there is '||v_min_T2_detailed_merise||' '||p_right_table||'.';
	Else
		v_detailed_merise_report_T1:= 'For each '||p_left_table||' there is '||v_min_T2_detailed_merise||' or '||v_max_T2_detailed_merise||' '||p_right_table||'.';
	End If;
	If v_min_T1 = v_max_T1 Then
		v_detailed_merise_report_T2:= 'For each '||p_right_table||' there is '||v_min_T1_detailed_merise||' '||p_left_table;
	Else
		v_detailed_merise_report_T2:= 'For each '||p_right_table||' there is '||v_min_T1_detailed_merise||' or '||v_max_T1_detailed_merise||' '||p_left_table||'.';
	End If;
	v_result:= p_left_table||'.'||p_left_table_key||' ('||v_min_T1||'..'||v_max_T1||') - ('||v_min_T2||'..'||v_max_T2||') '||p_right_table||'.'||p_right_table_key||'. '||v_detailed_merise_report_T1||' '||v_detailed_merise_report_T2;
Else
	v_result:= 'Incorrect Verbose parameter specified : ['||p_verbose||']';
End If;
 
RETURN v_result;
 
END F_find_cardinalities;
 
Begin
 
dbms_output.put_line(F_find_cardinalities('test1','id','test2','id', null));
dbms_output.put_line(F_find_cardinalities('test1','id','test2','id', ''));
dbms_output.put_line(F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE'));
dbms_output.put_line(F_find_cardinalities('test1','id','test2','id', 'MERISE'));
dbms_output.put_line(F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE'));
 
End;
/

28/10/2008, 11h18
orafrance

j'ai pas compris l'utilité :oops:, pourrais-tu nous donner un exemple d'utilisation STP ? :king:

Ca permet de découvrir les cardinalités entre 2 tables d'une base de données, pour savoir si c'est du 1..1 - 1..1 ou du 0..1 - 0..n, etc.

Par exemple avec une partie du script d'exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
SQL> CREATE TABLE Test1 (ID number);
 
Table créée.
 
SQL> CREATE TABLE Test2 (ID number);
 
Table créée.
 
SQL>
SQL> DELETE FROM Test1;
 
0 ligne(s) supprimée(s).
 
SQL> DELETE FROM Test2;
 
0 ligne(s) supprimée(s).
 
SQL> INSERT INTO Test1 VALUES (1);
 
1 ligne créée.
 
SQL> INSERT INTO Test2 VALUES (1);
 
1 ligne créée.
 
SQL>
SQL> SELECT F_find_cardinalities('test1','id','test2','id', NULL) FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID',NULL)
--------------------------------------------------------------------------------
1..1 - 1..1
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', '') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','')
--------------------------------------------------------------------------------
1..1 - 1..1
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','SHORT_MERISE')
--------------------------------------------------------------------------------
1..1 - 1..1
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', 'MERISE') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','MERISE')
--------------------------------------------------------------------------------
test1.id (1..1) - (1..1) test2.id
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','DETAILED_MERISE')
--------------------------------------------------------------------------------
test1.id (1..1) - (1..1) test2.id. For each test1 there is one test2. For each test2 there is one test1
 
 
SQL>
SQL> DELETE FROM Test1;
 
1 ligne supprimée.
 
SQL> DELETE FROM Test2;
 
1 ligne supprimée.
 
SQL> INSERT INTO Test1 VALUES (1);
 
1 ligne créée.
 
SQL> INSERT INTO Test1 VALUES (3);
 
1 ligne créée.
 
SQL> INSERT INTO Test2 VALUES (3);
 
1 ligne créée.
 
SQL> INSERT INTO Test2 VALUES (3);
 
1 ligne créée.
 
SQL>
SQL> SELECT F_find_cardinalities('test1','id','test2','id', NULL) FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID',NULL)
--------------------------------------------------------------------------------
1..1 - 0..n
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', '') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','')
--------------------------------------------------------------------------------
1..1 - 0..n
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', 'SHORT_MERISE') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','SHORT_MERISE')
--------------------------------------------------------------------------------
1..1 - 0..n
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', 'MERISE') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','MERISE')
--------------------------------------------------------------------------------
test1.id (1..1) - (0..n) test2.id
 
SQL> SELECT F_find_cardinalities('test1','id','test2','id', 'DETAILED_MERISE') FROM DUAL;
 
F_FIND_CARDINALITIES('TEST1','ID','TEST2','ID','DETAILED_MERISE')
--------------------------------------------------------------------------------
test1.id (1..1) - (0..n) test2.id. For each test1 there is zero or n test2. For each test2 there is one test1

En gros si j'ai une relation 1..1 - 1..1 entre les données de ma table Test1 et celles de ma table Test2, sur une jointure entre Test1.id = Test2.id, j'obtiendrais les résultats suivants :

Citation:

Envoyé par Par défaut

1..1 - 1..1

Citation:

Envoyé par SHORT_MERISE

1..1 - 1..1

Citation:

Envoyé par MERISE

test1.id (1..1) - (1..1) test2.id

Citation:

Envoyé par DETAILED_MERISE

test1.id (1..1) - (1..1) test2.id. For each test1 there is one test2. For each test2 there is one test1

28/10/2008, 13h47
orafrance

ha OK, au niveau modélisation... très bien :mrgreen:

:merci: