Clé primaire de type GUID et type date

Bonjour,

Avant j'utilisais le type GUID comme clé primaire d'une table avec MS SQL où un type spécial "uniqueidentifier" existait. Mais je me creuse de la tête pour faire la même chose sous Oracle, donc j'utiliserais le type VARCHAR2(36) ou VARCHAR2(38) (avec les accolades du GUID) comme équivalence.
Comment faire alors pour faire ce champ qui se donne une valeur toute seule sans qu'on l'ajoute toujours dans la liste des VALUES lors des insertions, je me propose

Code :

ID char (36) DEFAULT  SYS_GUID() PRIMARY KEY

mais que contiendra la fonction SYS_GUID()

Et pour un champ de type date + heure, quel est le mieux: DATE ou TIMESTAMP ??

[Vincent Rogier]

Envoyé par Lucas Panny

Et pour un champ de type date + heure, quel est le mieux: DATE ou TIMESTAMP ??

Les types DATE et TIMESTAMP gèrent tous les deux date+heure.
La différence est que TIMESTAMP permet de gérer la partie fractionnaire des secondes et peut prendre en charge le timezone. De plus, il est aussi fortement couplé au type INTERVAL qui permet de gérer des intervalles entre TIMESTAMPs

Pour gérer date+heure seulement, DATE est le plus simple

Et pour le GUID en clé primaire ?

[Vincent Rogier]

Pourquoi passer par un guid varchar2(38) ???

Pourquoi pas passer par un simple ID numérique couplé à une séquence ?

Entre un ID de type NUMBER et un ID de type VARCHAR2(38), y a pas photo !

C'est vraiment un pré-requis qui t'es imposé d'avoir un GUID ?

Une clé primaire en UUID est très familier surtout pour les utilisateurs de BD Sql Server (qui bénéficient du type natif UNIQUEIDENTIFIER) et Access 2003. Et je voudrais tout simplement faire la même chose avec Oracle.
J'ai même trouvé sur le net une fonction pour créer un GUID (http://www.oracle-base.com/articles/9i/UUID9i.php):

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
CREATE OR REPLACE FUNCTION new_uuid RETURN VARCHAR2 AS
  l_seed        BINARY_INTEGER;
  l_random_num  NUMBER(5);
 
  l_date        VARCHAR2(25);
  l_random      VARCHAR2(4);
  l_ip_address  VARCHAR2(12);
BEGIN
  l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));
  DBMS_RANDOM.initialize (val => l_seed);
  l_random_num := TRUNC(DBMS_RANDOM.value(low => 1, high => 65535));
  DBMS_RANDOM.terminate;
 
  l_date       := conversion_api.to_hex(TO_NUMBER(TO_CHAR(SYSTIMESTAMP,'FFSSMIHH24DDMMYYYY')));
  l_random     := RPAD(conversion_api.to_hex(l_random_num), 4, '0');
  l_ip_address := conversion_api.to_hex(TO_NUMBER(REPLACE(NVL(SYS_CONTEXT('USERENV','IP_ADDRESS'), '123.123.123.123'), '.', '')));
 
  RETURN SUBSTR(l_date, 1, 8)                     || '-' ||
         SUBSTR(l_date, 9, 4)                     || '-' ||
         SUBSTR(l_date, 13, 4)                    || '-' ||
         RPAD(SUBSTR(l_date, 17), 4, '0')         || '-' ||
         RPAD(l_random || l_ip_address, 12, '0');
END;
/

Mais comment faire pour que ce champ s'auto-crée !!! Je veux faire:

Code :

ID varchar2(38) DEFAULT  new_uuid PRIMARY KEY

mais ça ne passe pas donc faut toujours insérer cette fonction à chaque insertion
Sous MSSQL, on peut faire [ID] [uniqueidentifier] PRIMARY KEY DEFAULT NEWID()

[Vincent Rogier]

Envoyé par Lucas Panny

Une clé primaire en UUID est très familier surtout pour les utilisateurs de BD Sql Server (qui bénéficient du type natif UNIQUEIDENTIFIER) et Access 2003. Et je voudrais tout simplement faire la même chose avec Oracle.

Ok, mais ma question (et remarque) ne portait sur le fait que MS Server le permet mais sur l'utilité même de gérer un Guid lourd à générer et à stocker et qui lent à indexer... Alors q'un simple NUMBER fait la même chose avec des perfs nettement meilleures !

Sinon, si tu tiens vraiment à utiliser ta fonction, tu peux l'appeler :

• dans le insert
• ou dans un trigger sur on insert

Envoyé par vicenzo

• ou dans un trigger sur on insert

Comment ?

Il faudrait donc un trigger pour chaque table, quelle poisse !!!

ID varchar2(38) DEFAULT new_uuid PRIMARY KEY

Même Oracle 11g ne peut-il pas faire ça ?

En effet, un GUID est difficile à indexer mais je dois l'utiliser !!!

[orafrance]

Envoyé par vicenzo

sur l'utilité même de gérer un Guid lourd à générer et à stocker et qui lent à indexer...

Si ni plus lourd ni plus lent qu'un NUMBER... en revanche ça évite les contentions sur les séquences et plus important, ça évite les trous

[mnitu]

Envoyé par orafrance

Si ni plus lourd ni plus lent qu'un NUMBER... en revanche ça évite les contentions sur les séquences et plus important, ça évite les trous

Ca mérite une explication parce que je ne le vois pas (mais pas du tout). C'est quoi un trous dans un GUID ? Et c'est quoi "les contentions" sur les séquences ? Bon sur les tables ça va, mais sur les séquences ?

[orafrance]

une séquence ne garantie pas la perte de valeur, notamment à cause du cache et souffre d'une grosse concurrence d'accès. Les contentions sont du même type que les library cache pin pour les procédures et packages.

[Vincent Rogier]

Envoyé par orafrance

Si ni plus lourd ni plus lent qu'un NUMBER... en revanche ça évite les contentions sur les séquences et plus important, ça évite les trous

Tu es en train de me dire que gérer une clé primaire via un varchar2(40) n'est pas plus pénalisant en terme de stockage, indexation et recherche qu'un Number ??

Alors la, je fais mon Saint Thomas et je demande à voir !!

[mnitu]

Envoyé par orafrance

une séquence ne garantie pas la perte de valeur ...

vrai

Envoyé par orafrance

... notamment à cause du cache et souffre d'une grosse concurrence d'accès.

faux :ce n'est pas seulement le cache. Ni une séquence avec le caché à zéro ne peut garantir la non perte des valeurs.

--------------------------------------------------------------------------------
Caution:
If accountability for all sequence numbers is required, that is, if your application can never lose sequence numbers, then you cannot use Oracle sequences and you may choose to store sequence numbers in database tables

faux: une séquence ne souffre pas du tout d'une grosse concurrence d'accès. Elles permet aux applications d'être évolutive du ce point de vue.

Sequences eliminate serialization and improve the concurrency of your application

Envoyé par orafrance

...Les contentions sont du même type que les library cache pin pour les procédures et packages.

J'ai de doute

Quel débat !!!

Pour nous les novices, on s'y perd un peu mais je me demande: les séquences sont-elles des trucs d'Oracle seulement ou SQL Server l'utilise aussi ?

TIMESTAMP ou DATE ? Que choisir à la fin ?
La fonction CURRENT_DATE peut-elle retourner un timestamp ? par ex un champ

"DATEVALID" TIMESTAMP DEFAULT CURRENT_DATE,

Est-ce que le type timestamp peut être une valeur de retour d'une fonction ou d'une procédure stockées ?

[Vincent Rogier]

Si tu n'a pas besoin la partie fractionnaire des secondes, utilise DATE.

PS : Pour retourner le timestamp courant, c'est CURRENT_TIMESTAMP()

[orafrance]

Envoyé par mnitu

faux :ce n'est pas seulement le cache. Ni une séquence avec le caché à zéro ne peut garantir la non perte des valeurs.

notamment = c'est d'autant plus flagrant quand il y a un cache

Envoyé par mnitu

faux: une séquence ne souffre pas du tout d'une grosse concurrence d'accès. Elles permet aux applications d'être évolutive du ce point de vue.

J'ai de doute

Pourtant, je peux t'assurer que la contention sur les séquences existent bel et bien.

Code :

Sequences eliminate serialization AND improve the concurrency of your application

cela signifie que c'est moins sujet à blocage qu'une séquence géré dans une table... ça ne garantie pas la suppression totale des séquences. En tout cas, je l'ai déjà observer et tu sais comme moi que les docs Oracle parlent rarement des défauts du SGBD

Hey o ! CURRENT_DATE ne retourne que la date mais pas l'heure !
Juste par curiosité, quelle est l'équivalence de TIMESTAMP sous Sql Server puisque DATETIME n'est que date+heure, pas de fraction de secondes.

[Vincent Rogier]

CURRENT_DATE() renvoi date+heure...

Par contre le format dans lequel c'est affiché dépend de l'outil, des options par défaut, ...

[mnitu]

Envoyé par orafrance

...
Pourtant, je peux t'assurer que la contention sur les séquences existent bel et bien.

Code :

Sequences eliminate serialization AND improve the concurrency of your application

cela signifie que c'est moins sujet à blocage qu'une séquence géré dans une table... ça ne garantie pas la suppression totale des séquences. En tout cas, je l'ai déjà observer et tu sais comme moi que les docs Oracle parlent rarement des défauts du SGBD

Bref, il y a une partie de vérité dans tes affirmations. La contention n’est pas à l’utilisation directe de la séquence (SELECT … SEQUENCE.NEXTVAL …FROM DUAL) mais dans la suite donnée : la valeur ainsi obtenu est inséré comme un identifient unique dans une clé primaire non- fonctionnelle et donc indexée. Et comme les valeurs de la séquence sont ordonnées ça se traduit par le fait que tout le monde est en train d’écrire dans le même block de données de l’index en question. Une des solutions à ce problème sont les indexes inversés mais cette solution pose parfois un autre problème que j’ai l’impression qu’il reste entièrement valable pour les GUID.
Et le pire c’est que le plus souvent les gens ne retient que le fait qu’une séquence pose des problèmes et donc il s’en sorte avec leur propre gestion des séquences stockées dans les tables ce qui pose encore bien plus des problèmes.

[orafrance]

Envoyé par mnitu

Et le pire c’est que le plus souvent les gens ne retient que le fait qu’une séquence pose des problèmes et donc il s’en sorte avec leur propre gestion des séquences stockées dans les tables ce qui pose encore bien plus des problèmes.

c'est clair que la séquence reste le meilleur moyen d'éviter les contentions. Pour la petite histoire j'ai également eu des contentions sur DUAL... et c'est pas évident de s'en passer

[mnitu]

Envoyé par orafrance

...Pour la petite histoire j'ai également eu des contentions sur DUAL... et c'est pas évident de s'en passer

C'est déjà réglé en 10g il me semble