Bonjour,
 
On a actuellement dans un de nos projets une base qui utilise des clés techniques et fonctionnelles.
Les jointures se font sur les clés techniques dont la valeur n'a aucune signification.
Or pour des problèmes de synchro / performance l'utilisation actuelle n'est plus viable. (on a besoin de connaitre à "l'avance" la clé)
Je voulais donc généré ma clés technique en faisant un CRC32 de ma clé fonctionnelle, or en me renseignant j'ai cette information
CRC32 can return the same output for different input strings
 
Ma question est, est-ce un cas plausible sur 2 strings de même longueur.
Car si on dit que 'non' je me dis que CRC32(clé_fonctionnelle)||length(clé_fonctionnelle) devrait suffire à me sortir de ce cas non ?
 
Ma clé technique est un numérique, je peux donc pas utiliser du MD5 par exemple.
 
Merci d'avance

La clef technique doit elle forcément être déduite à partir de l'ancienne clef/clef fonctionnelle (contrainte forte liée au projet ?)  ? ( Comment doit on comprendre le "on a besoin de connaitre à "l'avance" la clé" ? )

Ca va pas etre très facile d'expliquer le contexte, mais je vais essayer.
 
On a un référentiel très volumineux sur lequel on a un systeme de temps réel.
il nous permet d'alimenter un projet qui n'a besoin que de certaines informations du referentiel principal.
Les 2 bases étant très sollicitées et avec bcp de volume on doit donc lire au minimum les informations
 
Une table du référentiel va se présenter de la forme
Cle_foncti | Data
 
Dans notre projet la meme table va avoir cette forme
Cle_technique | Cle_foncti | Data
 
Imaginons maintenant le cas suivant
Referentiel
 
FR | FRANCE
GR | GERMANY
 
Et chez nous
 
1 | FR | FRANCE
(on a pas la deuxieme)
 
Une mise à jour dans le référentiel arrive pour ces 2 champs
 
Notre outil n'a qu'un type de format en sortie (du a des besoins de temps réel) et donc il mappe toutes les colonnes
Donc il va d'abord tenté une mise à jour de la ligne et si elle existe pas la créer
Le soucis est donc la :
Si je génère une clé au hasard, la mise à jour va changer ma clé technique, ou plutôt va se crasher car les contraintes référentielles vont l'en empêcher
Donc l'idée était qu'avec la clé fonctionnelle on créé une clé technique qui sera donc connue et dont on pourra mettre à jour
 
Exemple
FR me genere 33
GR me genere 56
 
j'aurais :
update pays set cle_tch = 33, libelle = data where cle_fonct = 'FR'
et  
insert into pays values (56, GR, data)
 
Donc j'avais l'idée du CRC pour alimenter ma clé technique (chez moi c'est un numérique de 19 donc 2^64bits)
Or le CRC est en 2^32 et je viens de faire un test avec toutes les combinaisons lettre_chiffre sur 5 caractères (36^5)
Sur les 60 Millions de lignes j'ai +2% de redondance, donc gros risques, donc impossible
 
Il aurait fallut avoir une sorte de CRC 64 bits pour regler je pense mon soucis.
 
Alors bien entendu le modèle serait peut etre à revoir, mais on s'en est apercu trop tard et plus possible de revenir en arrière, il nous faut donc une autre solution.
 
Je viens de tenter de mettre dans ma clé_technique : CRC32(cle_fonc)||code_ascii(premier_caractère)||code_ascii(3iem_caractère) et je n'ai plus aucun doublon.
Je vais verifier demain la taille de mes plus longues clés fonctionnelles mais ca me semble une piste serieuses à envisager la.
 

La logique de mise à jour, elle est codée au niveau client (client p/r à la base qui sert de référentiel ) ou directement centralisée dans la base ( via un/des triggers/proc stockées ) ?

Sinon, là tu as créé une autre fonction de hachage, mais quant aux collisions éventuelles, c'est pas simplement en la regardant comme ça qu'on peut dire s'il y en aura ( bcp ou peu ) ou pas du tout.
 
C'est une des voies possibles

Pas sur d'avoir bien compris ta question sur la logique de mise à jour.
Tout se fait au niveau referentiel, et on utilise l'archive log pour repéré toutes modifications
Et toutes les 3 heures on met à jour notre base cliente avec le delta.
 
Et oui je me dis que je vais peut etre me faire une sorte de fonction de hachage, le soucis étant que je suis limité dans les fonctions (la plus part des fonctions basiques, mais pas le droit de faire un vrai algo)

Le référentiel a des codes de 30 caractères ...
C'est totalement mort quoi la, je pourrais jamais assurer 0 collisions sur 36^30 combinaisons possibles (et donc potentiellement, même ma clé technique pourrait pas suffire sur la théorie)

C'est ce que je voulais savoir, cad comment vous propagez les maj du référentiel vers la nouvelle base cliente

Par contre, j'ai qq doutes sur ce que j'ai compris ...
C'est bien un SGBDR derrière ? (je pense que oui, mais y'a un truc qui me fait douter)

Pas moyen d'utiliser un ou deux triggers bien "sentis" plutot que votre mécanisme actuel ?

C'est vraiment nécessaire d'utiliser une clé primaire généré par hachage ?  elle peut pas être générée de façon indépendante (et surtout unique) lors de la création des enregistrements dans la nouvelle table ?

Ca me parait compliqué cette histoire de clé primaire "technique"/étrangère générée en hachant une "clé fonctionnelle" existante, alors que la génération/gestion auto d'une clé primaire sur une table, c'est une des fonctionnalités de base de tout SGBDR qui se respecte

Ou alors il doit me manquer des contraintes dues à l'existant/projet non expliquées ici.

EDIT : en y réfléchissant bien, y'a même pas besoin de trigger en fait

EDIT2 : "référentiel volumineux" => ça veut dire quoi ?  (juste un ordre d'idée en millions/centaine de millions de lignes)

Alors la politique de la boite c'est : les triggers c'est niet, la base cible c'est du oracle, donc pas d'autoidentity comme chez mssql, donc l'outil doit geré lui meme les sequences.
 
Si on veut schématiser au plus large.
* Je n'ai pas moyen de connaitre les données en cible
* J'ai n'ai droit qu'à 1 ordre SQL bien précis
update table_cible set clé_technique = XXXX, data = YYYY where cle_fonctionnelle = ZZZZ
 
Comment connaitre la clé technique sachant que je suis obligé de la renseigner
 
Le volume c'est de l'ordre de quelques millions pour les tables les plus importantes, mais avec une utilisation métier H24 (international) donc les applications 'tiers' doivent les solliciter le moins possible, surtout une application comme celle ci qui va 'synchroniser' plusieurs fois par jour la cible

Je n'ai rien compris. Qu'est-ce qui t'empeche d'utiliser une sequence oracle pour ta table a partir du moment ou la clef que tu utilise localement n'as pas a etre repliquee sur ta base centrale ?

Ca devient de moins en moins clair là ... j'ai l'impression qu'on donne pas le même sens aux mots
 
Je reprend, ce que j'ai compris :
 
Base en prod H24, qui est le référentiel : BDD Réf.
 
Cette base a une clé primaire existante, qui est en fait une clé définie au niveau fonctionnel (d'ailleurs elle est générée comment ? manuellement par une appli cliente, qui fait le controle d'ingrité ? )
 
Seulement le choix technique (type données correspondant dans la base) pose problème, niveau performances
 
Vous décidez donc de créer une autre clé primaire, qui n'a aucun sens particulier au niveau fonctionnel, mais qui vous permettre d'améliorer sensiblement toutes les jointures faites lors des interrogations/reuetes (que soit plus pour du décisionnel ou autre)
 
Nouvelle base, cible : BDD cible
 
Toutes les 3 heures, on récupère le delta BDD Réf./BDD Cible : BDD Réf =>  extraction/récupération du delta => BDD cible
 
C'est bien ça ?

Doit manquer qqchose dans l'énoncé du problème, parce que ce serait clairement la solution la plus simple
 
Générer la "clé primaire technique" lors de l'insertion de nouvelles lignes dans la base/table cible, puis les propager dans la base/table source (référentiel) avec un update SQL, comme clé étrangère ...
 
Ce qui me semble une façon logique de faire, mais je dois pas tout avoir compris peut être, niveau contraintes actuelles sur la base référence/H24  
 
http://www.techonthenet.com/oracle/sequences.php

Alors Zzozo => ta recap est celle la même.

J'ai une contrainte particulière.

L'insert et l'update se font forcement sur les mêmes champs avec mon outil

1/ j'update / insert tous les champs.
2/ j'update / insert tous les champs sauf la clé technique.

Pour le cas 2 (une des solutions soumise aux DBA) si lors de l'insert mon outil n'indique pas de clé technique qui est la PK ca sera rejetée, il faut donc un trigger + sequence oracle => refusé par les DBA

Pour le cas 1 : N'ayant pas connaissance du contenu de ma table (on ne veut pas aller verifier ce qui existe déjà ou pas) je suis obligé de fournir la clé technique via sequence.

Ca va ressembler à ca graphiquement

Le fait d'avoir le trait entre KEY et ID_CITY signifie que quoiqu'il y a dans KEY je le mets à la place de l'existant.

Pour l'insert pas de problème.
Pour l'update : rejected, car ma sequence va vouloir changer la clé technique déjà en place.

Bah là, je vois déjà une solution relativement simple à mettre en place :

Lors du transfert des infos de BDD Réf. , il sufit de faire le tri entre ce qui doit être simplement mis à jour (simple UPDATE dans BDD Cible) dans la BDD Cible, et ce qui doit être créé dans BDD Cible ( INSERT dans BDD Cible avec récupération de la nouvelle clé technique, utilisant une séquence, puis UPDATE dans BDD Réf. de la clé technique nouvellement créée et correspondant à la clé fonctionnelle)

Concrètement, ça pourrait se traduire comme ça (une façon de faire parmi d'autres) :

1/ Extraction des lignes en delta en provenance de BDD Réf., dans une table temporaire (elle a tous les champs à maj/créer en provenance de BDD Réf. )

2/ Traitement des simples mises à jour  (les lignes existent déjà dans BDD Cible)
Un simple UPDATE (de tous les champs sauf de clé technique) de table/BDD Cible avec une jointure sur le champ clé fonctionnelle de la table temporaire et le boulot est fait
Puis un DELETE sur la table temporaire des lignes qu'on vient de mettre à jour (là aussi il suffit de faire une jointure sur le champ clé fonctionnelle mais de la table/BDD Cible cette fois ci)

Il ne reste plus, à ce moment là,  dans la table temporaire que les nouvelles à créer dans BDD Cible

C'est l'étape suivante

3/ Traitement des nouvelles lignes ( elles n'existent pas dans BDD Cible )
- D'abord créer les nouvelles lignes dans BDD Cible en faisant un INSERT SELECT (le insert se fait dans table/BDD cible et le SELECT depuis la table temporaire). La clause SELECT contient tous les champs de table temporaire, plus la référence à la séquence pour créer une nouvelle clé technique

- Ensuite mettre à jour les lignes concernées dans BDD Réf. avec les nouvelles clés techniques qu'on vient de créer
il suffit de faire un UPDATE du champ clé technique dans BDD Réf. avec une double jointure (sur le champ clé fonctionnelle de la table temporaire et le champ clé fonctionnelle de la table/BDD Cible, histoire de mettre à jour dans BDD Réf. que les lignes qu'on vient de créer, pas tout le contenu de table/BDD Cible)

On peut dropper la table temporaire, c'est terminé pour cette fois

Voilà les grandes lignes

Question qui me turlupine un peu ... vous effacez jamais de lignes dans la BDD Réf. ?

Et voila tu viens de pointer un autre soucis (dont on a pas de solutions actuellements) on efface.
Et ta solution qui a été réfléchis n'est pas possible dans notre cas.
Vu que c'est un archive log toutes les informations sont présentes, on doit donc traiter les informations dans l'ordre
Je peux avoir
INSERT FR ALLEMAGNE
puis
UPDATE FR FRANCE
 
Et la ca marchera pas ta solution, et meme si on met tout en table de staging on a des limites de lecture sur les tables cibles (on ne veut pas aller lire toutes les 3 les 30 tables de références sur une clé fonctionnelle qui peut faire 30 caractères).
 
En fait on a retourné le problème dans tous les sens et à part modifier le modèle on tourne en rond, le hashage de la clé fonctionnelle était une des solutions envisagées pour gérer les données insert / update (mais pas à 100% les delete)
Mais à 3 semaines de la mise en production j'ai bien l'impression que ca va exploser tout ca.

Pour l'ordre, c'est pas un problème, suffit d'inverser et de traiter les INSERT avant les UPDATE, ça gèrera ce cas là aussi.
Faudra pas oublier de faire le delete sur la table tempo, après avoir fais les INSERT, c'est tout

EDIT : et pour gérer les updates dans le bon ordre, faut ajouter une colonne timestamp ou un truc dans le genre dans la table temporaire, pour être sur de les faire dans l'ordre lors des différents SELECT (y'aura juste à rajouter une clause ORDER BY )

??? doit y'avoir grand maximum trois ou quatre SELECT dans ce que je propose, et y'a pas besoin de lire 30 tables dans la base cible, il faut juste faire la jointure sur la/les tables à mettre à jour

Le truc que j'ai proposé se fait en un bloc, c'est pas un curseur.
Si vous avez un modèle physique de données correct, vous avez des types de données adaptés à votre problématique et surtout les index qui vont bien (qui ont été choisis avec pertinence) pour éviter des horreurs du genre table scan à gogo

Si c'est pas le cas, vous avez un pb plus grave sur les bras, vous n'avez personne dans votre équipe qui s'y connaisse vraiment en SQL mais surtout en SGBDR / modélisation de S.I./BDD .
Et là, je ne saurais que trop vous recommander de mettre la main sur ce genre de compétences et les intégrer à votre équipe rapidement

En attendant, vous pouvez pas aller voir les DBA/l'équipe de prod pour discuter de votre MPD et voir s'ils peuvent vous aider à corriger les erreurs qu'ils voient ?

Parce que là, plus ça avance, plus je découvre que votre MPD est bancal

Ce sont les fondations.
Si y'a des erreurs à ce niveau là, ca va faire mal, et vous allez passer tout votre temps à courrir derrière ces erreurs de conception, une fois mis en prod, et à corriger les données en mode manuel ... c'est vraiment pas bon du tout

Désolé, je suis un peu rude là, mais je pense pas qu'un bricolage qconque (l'histoire du calcul de la clé à partir du hashage d'une autre clef qui ne vous parait pas bonne, c'est pas sain je pense, mais surtout, ce qui me fait tiquer le plus, c'est que vous vouliez faire le boulot du SGBDR pour la gestion/propagation des clés primaires/clés étrangères. Et vouloir le faire dans le sens clé étrangère -> clé primaire  c'est un non sens, sémantiquement parlant) vous sauve la mise, dans votre situation

Voilà, ça confirme ce que je pensais depuis qq posts

La prochaine fois ils consulteront l'équipe décisionnelle avant de nous demander des choses irréalisables avec leur modèle physique completement bancal.
Le problème c'est qu'on leur a vendu notre outil sans vraiment connaitre l'ampleur du problème. (Pas sensé d'avoir de delete physique par exemple)
Quand aux DBA sans remettre en doute leurs competences, j'ai l'impression que sur ce coup la ils ont pris le projet un peu à la légère (et quand on voit qu'ils se mouillent pas du tout lors des réunions pour se soucis et qu'ils ne cherchent pas vraiment à trouver des solutions)
 
Enfin j'ai aussi un autre projet ou ils ont autorisé des nom de colonnes commençant par un chiffre, donc je sais plus quoi penser.