Clé primaire de type GUID et type date

Version imprimable

Voir 40 message(s) de cette discussion en une page

26/05/2008, 15h56
randriano

Sacrée discussion dans ce thread !!

Donc, Oracle ne supporte l'utilisation de fonctions dans la création de table:
- valeur par défaut d'une clé primaire
- dans les contraintes CHECK
Est-ce vrai ? C'est vrai pour la clé primaire apparemment

Citation:

Envoyé par randriano

Sacrée discussion dans ce thread !!

Donc, Oracle ne supporte l'utilisation de fonctions dans la création de table:
- valeur par défaut d'une clé primaire
- dans les contraintes CHECK
Est-ce vrai ? C'est vrai pour la clé primaire apparemment

Il ne suffit pas de lire les discussions il faut lire la doc et/ou tester
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 Connecté à : Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production JServer Release 9.2.0.5.0 - Production SQL> drop table t_f; Table supprimée. SQL> create table t_f (col varchar2(40) default sys_guid(), txt varchar2(100)) / 2 Table créée. SQL> insert into t_f(txt) values ('TEST'); 1 ligne créée. SQL> commit; Validation effectuée. SQL> select * from t_f; COL ---------------------------------------- TXT -------------------------------------------------------------------------------- 4E23B81AC411A0A0E043AC102E2CA0A0 TEST

Bon, juste pour le fun, voici un petit bench entre l'utilisation de GUID / Number comme clé primaire.

Script :

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
 
SET TIMI OFF;
 
DROP TABLE TEST_PK_1;
DROP TABLE TEST_PK_2;
DROP SEQUENCE SEQ_TEST_PK_2;
 
CREATE TABLE TEST_PK_1
(
    ID    VARCHAR2(40) default sys_guid(), 
    VALUE NUMBER,
    PRIMARY KEY(ID)
);
 
CREATE SEQUENCE SEQ_TEST_PK_2;
 
CREATE TABLE TEST_PK_2
(
    ID    NUMBER, 
    VALUE NUMBER,
    PRIMARY KEY(ID)
);
 
 
CREATE OR REPLACE PROCEDURE TEST_INSERT_1
IS
BEGIN
    FOR i IN 1..100000 LOOP
 
        INSERT INTO TEST_PK_1(VALUE) VALUES(i);
 
        END LOOP
 
    COMMIT;
END;
/
 
 
CREATE OR REPLACE PROCEDURE TEST_INSERT_2
IS
BEGIN
    FOR i IN 1..100000 LOOP
 
        INSERT INTO TEST_PK_2
        (
            ID, VALUE
        ) 
        VALUES
        (
            SEQ_TEST_PK_2.NEXTVAL, i
        );
 
        END LOOP
 
    COMMIT;
END;
/
 
CREATE OR REPLACE PROCEDURE TEST_SELECT_1
IS
    V_ID VARCHAR2(40);
    V_VAL NUMBER;
    CURSOR V_CUR is SELECT ID FROM TEST_PK_1;
BEGIN
 
 
    OPEN V_CUR;
 
        LOOP
 
            FETCH V_CUR INTO V_ID;
            EXIT WHEN V_CUR%NOTFOUND;
 
            SELECT VALUE INTO V_VAL FROM TEST_PK_1 WHERE ID = V_ID;
 
 
        END LOOP;
 
        CLOSE V_CUR;
 
END;
/
 
CREATE OR REPLACE PROCEDURE TEST_SELECT_2
IS
    V_ID NUMBER;
    V_VAL NUMBER;
    CURSOR V_CUR is SELECT ID FROM TEST_PK_2;
BEGIN
 
 
    OPEN V_CUR;
 
        LOOP
 
            FETCH V_CUR INTO V_ID;
            EXIT WHEN V_CUR%NOTFOUND;
 
            SELECT VALUE INTO V_VAL FROM TEST_PK_2 WHERE ID = V_ID;
 
 
        END LOOP;
 
        CLOSE V_CUR;
 
END;
/
 
 
SET TIMI ON;
 
EXEC TEST_INSERT_1;
EXEC TEST_INSERT_2;
 
EXEC TEST_SELECT_1;
EXEC TEST_SELECT_2;

Résultat :

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
 
SQL> SET TIMI ON;
SQL>
SQL> EXEC TEST_INSERT_1;
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :34.04
SQL> EXEC TEST_INSERT_2;
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :24.02
SQL>
SQL> EXEC TEST_SELECT_1;
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :07.29
SQL> EXEC TEST_SELECT_2;
 
Procédure PL/SQL terminée avec succès.
 
Ecoulé : 00 :00 :05.88
SQL>

Pour l'insert, la solution guid est 42% plus lente
Pour le select, la solution guid est 24 % plus lente

Et encore c'est un exemple simplisme !

Alors, un GUID, toujours "ni plus lourd ni plus lent qu'un NUMBER" ?

27/05/2008, 08h22
Lucas Panny

Et oui vicenzo, ton exemple montre très bien qu'avec un PK GUID c'est plus lent mais comme j'avais dit je dois m'y faire !!

En fait, je veux utiliser un GUID pas comme celui produit par SYS_GUID() mais comme ceci : {50BE2AD1-8A73-4350-B830-4983BB99927F} d'où mon varchar2(38) donc j'ai créé une fonction à cet effet => NEWUUID()

Citation:

CREATE TABLE MYSCHEMA.WWWSYSGUID (ID varchar2(38) DEFAULT MYSCHEMA.NEWUUID(), txt varchar2(100), PRIMARY KEY(ID))
/
INSERT INTO MYSCHEMA.WWWSYSGUID(txt) VALUES ('ABCDE')
/
INSERT INTO MYSCHEMA.WWWSYSGUID(txt) VALUES ('AZERT')
/
INSERT INTO MYSCHEMA.WWWSYSGUID(txt) VALUES ('WXCVBN')

Mais
ORA-04044: procedure, function, package, or type is not allowed here
Donc que peux-je faire, encore de la séquence+trigger pour chaque table (il y en a 150) ??
27/05/2008, 09h32
orafrance

plutôt qu'une DEFAULT VALUE tu crées un trigger :)
27/05/2008, 10h35
Lucas Panny

Donc, un trigger pour toutes mes tables, je préfère utiliser ma fonction à chaque INSERT !!

Code:

INSERT INTO MYSCHEMA.WWWGUID(ID, NAME, INTVALUE) VALUES(MYSCHEMA.NEWUUID, 'Jean', 55)

Citation:

Envoyé par vicenzo

Et encore c'est un exemple simplisme !

Alors, un GUID, toujours "ni plus lourd ni plus lent qu'un NUMBER" ?

Il faut rélativer un peu ces résultats

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
 
DROP TABLE TEST_PK_1
/
DROP TABLE TEST_PK_2
/
DROP SEQUENCE SEQ_TEST_PK_2
/
 
CREATE TABLE TEST_PK_1
(
    ID    Raw(32) DEFAULT sys_guid(), 
    VALUE NUMBER,
    PRIMARY KEY(ID)
)
/ 
CREATE SEQUENCE SEQ_TEST_PK_2
/ 
CREATE TABLE TEST_PK_2
(
    ID    NUMBER, 
    VALUE NUMBER,
    PRIMARY KEY(ID)
)
/ 
CREATE OR REPLACE PROCEDURE TEST_INSERT_1
IS
BEGIN
    FOR i IN 1..100000 LOOP 
        INSERT INTO TEST_PK_1(VALUE) VALUES(i);
    END LOOP; 
    FOR i IN 1..100000 LOOP 
        INSERT INTO TEST_PK_2
        (
            ID, VALUE
        ) 
        VALUES
        (
            SEQ_TEST_PK_2.NEXTVAL, i
        );
        END LOOP;
    COMMIT;
END;

Et TKPROF dit

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
 
INSERT INTO TEST_PK_2 ( ID, VALUE )
VALUES
 ( SEQ_TEST_PK_2.NEXTVAL, :B1 )
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      9.18      22.81          0        622     313710      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001      9.18      22.81          0        622     313710      100000
 
INSERT INTO TEST_PK_1(VALUE)
VALUES
(:B1)
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      6.39      33.34          1       1113     412271      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001      6.39      33.34          1       1113     412271      100000

C'est qui le plus performante ?

27/05/2008, 15h42
Vincent Rogier

Citation:

Envoyé par mnitu

C'est qui le plus performante ?

pour moi le plus performant, c'est le temps total d'exécution...
Et je ne vois pas en quoi tes chiffres donnes le guid plus performant...

Citation:

Envoyé par vicenzo

pour moi le plus performant, c'est le temps total d'exécution...
Et je ne vois pas en quoi tes chiffres donnes le guid plus performant...

Et c'est quoi le temps total d'exécution? C'est set timi on ?

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
 
CREATE OR REPLACE PROCEDURE TEST_INSERT_1
IS
BEGIN
    FOR i IN 1..100000 LOOP 
        INSERT INTO TEST_PK_1(VALUE) VALUES(i);
    END LOOP; 
    commit;
END;
/
CREATE OR REPLACE PROCEDURE TEST_INSERT_2
IS
BEGIN
    FOR i IN 1..100000 LOOP 
        INSERT INTO TEST_PK_2
        (
            ID, VALUE
        ) 
        VALUES
        (
            SEQ_TEST_PK_2.NEXTVAL, i
        );
        END LOOP;
    COMMIT;
END;

Ensuite

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
 
exec runstats_pkg.rs_start
 
Procédure PL/SQL terminée avec succès.
 
SQL> exec TEST_INSERT_1
 
Procédure PL/SQL terminée avec succès.
 
SQL> exec runstats_pkg.rs_middle
 
Procédure PL/SQL terminée avec succès.
 
SQL> exec TEST_INSERT_2
 
Procédure PL/SQL terminée avec succès.
 
SQL> exec runstats_pkg.rs_stop
 
Run1 ran in 4285 hsecs
Run2 ran in 4191 hsecs
run 1 ran in 102.24% of the time
 
Name                                  Run1        Run2        Diff
...
LATCH.redo allocation              206,289     213,987       7,698
STAT...session pga memory            8,600           0      -8,600
LATCH.row cache enqueue latch        1,428      11,010       9,582
LATCH.session allocation               420      10,192       9,772
LATCH.dml lock allocation              186      10,221      10,035
STAT...db block gets               313,812     324,691      10,879
LATCH.undo global data               2,064      16,224      14,160
STAT...calls to get snapshot s     101,436     115,931      14,495
STAT...enqueue releases                720      15,495      14,775
STAT...enqueue requests                719      15,495      14,776
STAT...session logical reads       315,838     331,124      15,286
STAT...db block changes            411,034     427,299      16,265
LATCH.library cache pin alloca       2,991      31,747      28,756
LATCH.enqueue hash chains            1,638      31,120      29,482
LATCH.row cache objects              1,812      31,414      29,602
LATCH.cache buffers chains       1,363,378   1,412,120      48,742
STAT...recursive calls             102,189     172,055      69,866
LATCH.shared pool                  104,360     237,565     133,205
LATCH.library cache pin            205,791     453,001     247,210
LATCH.sequence cache                     6     310,023     310,017
LATCH.library cache                208,810     584,827     376,017
STAT...redo size                55,117,152  52,694,292  -2,422,860
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
2,132,435   3,396,919   1,264,484     62.78%

C'est toujours mieux avec une séquence ?

27/05/2008, 19h57
Vincent Rogier
Citation:

Envoyé par mnitu

Et c'est quoi le temps total d'exécution? C'est set timi on ?

Pour moi le temps total d'exécution, c'est le temps consommé pour l'exécution d'une tâche du point de vue client et application.

Pour un client, le temps applicatif consommé par la procédure test_pk_1 (guid) est plus long que pour test_pk_2 (sequence).

Je me suis fait rapidement l'équivalent du test en C/OCI et j'obtiens les mêmes résulats que sous sql*plus :
Code:

1 2 3 4 5 Starting test.. TEST_INSERT_1 : elasped time (t1) 32.768000 secs TEST_INSERT_2 : elasped time (t2) 23.751000 secs DIFF (t1-t2) = 9.017000 secs
tu pourras beau fournir et tourner dans tous les sens stats (temps cpu, accès disques, analyse PGA, latches, ...), le client qui utilise une application s'en fout et voit le temps de traitement de son application...

Et puis, selon toi comment expliques tu que le temps de traitement total soit plus court avec la procédure test_pk_2 de 40 % alors que tu dis que test_pk_1 est plus performant ?

Enfin, je vois pas en quoi tes stats montre que la solution guid est plus performante et plus rapide....
28/05/2008, 12h08
mnitu

Citation:

Envoyé par vicenzo

...

Je me suis fait rapidement l'équivalent du test en C/OCI et j'obtiens les mêmes résulats que sous sql*plus :

Bref si tu peut poster ton test de telle façon qu'on pourrait le reproduire ça serra sympa (il me semble que je l'ai fait).

Citation:

Envoyé par vicenzo

...

tu pourras beau fournir et tourner dans tous les sens stats (temps cpu, accès disques, analyse PGA, latches, ...), le client qui utilise une application s'en fout et voit le temps de traitement de son application...

Ton test est mono-utilisateur. Les latchs sont en prendre en compte en multi-utilisateur plus précisement accès concurent de la même resource. Il se peut trés bien que le comportement de l'aplication change dans ces conditions.

Citation:

Envoyé par vicenzo

...
Et puis, selon toi comment expliques tu que le temps de traitement total soit plus court avec la procédure test_pk_2 de 40 % alors que tu dis que test_pk_1 est plus performant ?

Enfin, je vois pas en quoi tes stats montre que la solution guid est plus performante et plus rapide....

Pour que soit claire mon point de vue est le suivant :
a) si l'application a besoin d'un identifiant unique il faut utiliser sys_guid ou équivalent
b) si non une séquence fera l'affaire et sys-guid est à proscrire.

Mais parce que on compare les deux solutions je ne me déclare pas satisfait du test proposé en sqlplus avec set timi on.
Le test que j'ai proposé, si j'interprète correctement les résultats, montre une différence de temps d'exécution que je le trouve pas assez importante mais une différence dans les latchs assez importante.
La question n'est pas qui gagne mais que est-ce que les tests nous enseigne.

A propos, est-ce que t'a noté que j'ai changé le type de la colonne de la table TEST_PK_1 ?

Citation:

Envoyé par mnitu

Bref si tu peut poster ton test de telle façon qu'on pourrait le reproduire ça serra sympa (il me semble que je l'ai fait).

Ok, voila le code C (avec OCILIB : 30 lignes de codes au total :D) :
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 #include <ocilib.h> #include <time.h> #define TO_SEC(t) ((((double) (t)) * (double) CLOCKS_PER_SEC) * 1e-6) int main(int argc, char* argv[]) { clock_t t, t1, t2; OCI_Connection *cn; OCI_Statement *s1; OCI_Statement *s2; OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT); cn = OCI_ConnectionCreate("winrest", "winrest", "fsi", OCI_SESSION_DEFAULT); s1 = OCI_StatementCreate(cn); s2 = OCI_StatementCreate(cn); printf("Starting test..\n"); t = clock(); OCI_ExecuteStmt(s1, "BEGIN TEST_INSERT_1; END;"); t1 = clock() - t; printf("TEST_INSERT_1 : elasped time (t1) %f secs\n", TO_SEC(t1)); t = clock(); OCI_ExecuteStmt(s2, "BEGIN TEST_INSERT_2; END;"); t2 = clock() - t; printf("TEST_INSERT_2 : elasped time (t2) %f secs\n", TO_SEC(t2)); printf("\nDIFF (t1-t2) = %f secs", TO_SEC(t1-t2)); OCI_Cleanup(); RETURN EXIT_SUCCESS; }
Citation:

Envoyé par mnitu

Ton test est mono-utilisateur. Les latchs sont en prendre en compte en multi-utilisateur plus précisement accès concurent de la même resource. Il se peut trés bien que le comportement de l'aplication change dans ces conditions.

Oui, mais pour générer un guid, ton OS génère lui aussi des latchs qui eux ne sont pas comptabilisés par Oracle ! Ca tu ne le précises pas !

Citation:

Envoyé par mnitu

Le test que j'ai proposé, si j'interprète correctement les résultats, montre une différence de temps d'exécution que je le trouve pas assez importante mais une différence dans les latchs assez importante.
La question n'est pas qui gagne mais que est-ce que les tests nous enseigne.

Je suis entièrement d'accord...
Mais encore une fois, la différence temps d'éxécution vue par l'application cliente est importante !

28/05/2008, 14h04
mnitu

Citation:

Envoyé par vicenzo

Oui, mais pour générer un guid, ton OS génère lui aussi des latchs qui eux ne sont pas comptabilisés par Oracle ! Ca tu ne le précises pas !

Bah, il sont forcement visible, peut être d'une manière indirecte, mais sur ça il n'y a pas des doutes! Et la différence de temps de vue que j'ai noté dans mon test c'est ce que tu appele "vue par l'application cliente".

Des que j'aurais le temps je vais analyserer/exécuter ton test aussi.
28/05/2008, 14h56
Lucas Panny

On discute et discute de qui est le plus rapide entre clé primaire GUID et clé primaire NUMBER. Vu les "elapsed time" donnés par Vicenzo, il semble que l'utilisation d'entier gagne en vitesse.
Mais l'utilisation de GUID a l'avantage de créer une valeur unique sans risque d'égalité avec une valeur existante (à l'aide d'un générateur de UUID) lorsque c'est un logiciel qui insère au champ de la clé primaire.

Oui, avec mes tables au nombre de 150, toutes avec primary key varchar2(38) pour GUID, ma base de données est lente !! Mais c'est l'exigence de mes supérieurs, donc je fais sans discuter!! Ma solution est donc de trouver un moyen pour accélérer en utilisant encore varchar2(38) mais avec les SEQUENCES que je ne connais pas encore très bien

En fait c'est quoi latch ??
Verdict: c'est quoi le mieux ma table avec PK guid simple ou utilisant une séquence pour l'autogénération
28/05/2008, 23h14
Vincent Rogier

Citation:

Envoyé par Lucas Panny

Verdict: c'est quoi le mieux ma table avec PK guid simple ou utilisant une séquence pour l'autogénération

Moi je vote séquence... Mais si le guid t'es imposé, alors fais en guid....
29/05/2008, 09h26
Lucas Panny

Une petite question avant de clore le sujet:
que choisir entre INT et NUMBER ?

Citation:

Envoyé par Lucas Panny

En fait, c'est quoi les latch ?

réponse : Les latch, rien avoir avec les SGBD, c'est du système d'exploitation
29/05/2008, 09h41
Vincent Rogier

Citation:

Envoyé par Lucas Panny

Une petite question avant de clore le sujet:
que choisir entre INT et NUMBER ?

Question stockage, NUMBER et INT c'est la même chose (Grosso modo, Oracle gère en interne un tableau de 22 bytes pour représenter tous les number possibles)

La seule différence, c'est les checks effectués (range, décimales, ...)
29/05/2008, 10h08
Lucas Panny

Ok, merci Vicenzo
Je vais donc utiliser INT dans ce cas.
Pour la date+heure, je vais utiliser DATE, je n'ai pas besoin des fractions de secondes et de plus, TIMESTAMP n'est pas supporté par l'OleDb Provider
Pour le GUID, je maintiens varchar2(38) comme PK car c'est une exigence pour moi
29/05/2008, 10h21
mnitu

Citation:

Envoyé par Lucas Panny

...
réponse : Les latch, rien avoir avec les SGBD, c'est du système d'exploitation

L'article en question parle des SGA, transaction, Oracle, verrou, et en conclusion ça n'a rien à voir avec le SGBD. Bizarre conclusion ...

J’ai testé en pro*c (dommage pour l'instant je ne peut pas installer le OCILIB) et les résultats sont les suivantes: (Oracle 9)

Code:

1
2
3
4
5
6
7
 
tg mni/mni
Starting test..
TEST_INSERT_1 : elasped time (t1)  2526 secs
TEST_INSERT_2 : elasped time (t2) 3394 secs
 
DIFF (t1-t2) = -868 secs

Voici le programme de test Pro*C. Je reste ouverte à la critique

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
 
#include <time.h>
 
EXEC SQL INCLUDE SQLCA.H;
 
int main(int argc, char* argv[])
{
   char    *username = "MNI";
   char    *password = "MNI";
   int     t, t1, t2;
 
   EXEC SQL CONNECT :username IDENTIFIED BY :password;
 
   printf("Starting test..\n");
 
   EXEC SQL EXECUTE
     BEGIN
       :t := dbms_utility.get_time;
       TEST_INSERT_1;
       :t1 := dbms_utility.get_time - :t;
     END;
   END-EXEC;
 
   printf("TEST_INSERT_1 : elasped time (t1)  %d secs\n", t1);
   EXEC SQL EXECUTE
     BEGIN
       :t := dbms_utility.get_time;
       TEST_INSERT_2;
       :t2 := dbms_utility.get_time - :t;
     END;
   END-EXEC;
 
   printf("TEST_INSERT_2 : elasped time (t2) %d secs\n", t2);
 
   printf("\nDIFF (t1-t2) = %d secs", (t1-t2));
 
   EXEC SQL COMMIT WORK RELEASE;
   exit(0);
}

Voir 40 message(s) de cette discussion en une page