Rechercher 100 mille personnes dans une base en contenant près de 19 millions

**DomIII** · 26/03/2024, 16h34

Bonjour,

Avant l'arrêt définitif d'une base Oracle 12g, laquelle sera migrée à la fin de l'année sur PostgreSQL, il m'est confié une dernière mission, celle de vérifier que près de 100 000 personnes soient ou non connues dans cette base. Cette base recense environ 19 millions de personnes.

Le problème est le suivant : je ne dispose que du nom et des prénoms de chaque personne... Pas d'autre information. Cet échantillon était une extraction datant de quelques années suite à une opération particulière.

La table des personnes, appelée CLIENT, est ainsi composée :
- la clé primaire
- le nom
- les prénoms
- la date de naissance
- le lieu de naissance
- le pays de naissance (non obligatoire)

Je ne m'imagine pas créer un script avec à chaque ligne une requête du type "SELECT * FROM CLIENT WHERE NOM LIKE '$nom' AND PRENOMS LIKE '$prenom';". Une rapide estimation du temps nécessaire est d'environ 4 semaines... L'objectif est de faire cette vérification d'ici fin juin prochain.

Y aurait-il une bien meilleure stratégie pour optimiser de telles recherches ? Je peux développer en Java/Hibernate un algo s'il le faut, cela ne me dérange pas.

Merci à toute personne pouvant m'orienter.

**vanagreg** · 26/03/2024, 16h44

Bonjour,

Les nom/prénoms de ces 100000 personnes sont stockés où? Il peut y avoir des différences dans la casse et d'accents?

**SQLpro** · 26/03/2024, 17h43

Créez une table, même temporaire dans laquelle vous mettez les noms et prénoms à raison d'un nom par colonne nom et des prénoms séparés par une virgule dans la colonnes prénoms.
Ensuite faites une jointure entre les deux en splittant la colonne prénoms avec la fonction Oracle adéquate ou une requête récursive

Quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH
split_prenom AS
(SELECT nom, CASE INSTR(prenoms, ',') = 0 THEN prenoms ELSE SUBSTR(prenoms,  1, INSTR(prenoms, ',') - 1) END AS prenom,
        CASE INSTR(prenoms, ',') = 0 THEN '' ELSE SUBSTR(prenoms,  INSTR(prenoms, ',') + 1, LENGTH(prenoms) - INSTR(prenoms, ',')) END AS prenoms
 FROM recherche
UNION ALL
SELECT nom, CASE INSTR(prenoms, ',') = 0 THEN prenoms ELSE SUBSTR(prenoms,  1, INSTR(prenoms, ',') - 1) END AS prenom,
        CASE INSTR(prenoms, ',') = 0 THEN '' ELSE SUBSTR(prenoms,  INSTR(prenoms, ',') + 1, LENGTH(prenoms) - INSTR(prenoms, ',')) END AS prenoms
 FROM split_prenom 
WHERE prenoms <> ''
)
 
SELECT *
FROM  CLIENTS AS C
          JOIN split_prenom AS R
          ON C.nom = R.nom AND C.prenom = R.prenom

Non testé

A +

**sevyc64** · 26/03/2024, 18h38

Même réponse que SQLPro, si tu peux avoir tes 100000 noms sous une forme qui peut être injectée en masse dans une table, créer une table, même temporaire, y injecter les données, et ensuite requête avec jointure.

A défaut, si jamais tu peux quand même scripter sur ces 100000 noms, tu peux toujours essayer de faire un script qui va te générer les 100000 requêtes pour éviter de les taper à la main.

Par contre, nom/prénom, c'est clairement pas suffisant pour identifier une personne. Tu vas potentiellement avoir une bonne quantité de doublons juste avec ces infos-là.

**DomIII** · 26/03/2024, 21h49

Envoyé par vanagreg

Bonjour,

Les nom/prénoms de ces 100000 personnes sont stockés où? Il peut y avoir des différences dans la casse et d'accents?

Bonjour,

Les noms et prénoms sont stockés dans un fichier texte, ils ont été extraits d'une autre base. C'est bien là tout mon problème !

La casse est une difficulté supplémentaire, toutes ces chaînes sont converties en majuscules. Enfin, les lettres accentuées sont peu nombreuses, cet aspect peut être négligé.

**DomIII** · 26/03/2024, 21h52

Envoyé par SQLpro

Créez une table, même temporaire dans laquelle vous mettez les noms et prénoms à raison d'un nom par colonne nom et des prénoms séparés par une virgule dans la colonnes prénoms.
Ensuite faites une jointure entre les deux en splittant la colonne prénoms avec la fonction Oracle adéquate ou une requête récursive

Quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH
split_prenom AS
(SELECT nom, CASE INSTR(prenoms, ',') = 0 THEN prenoms ELSE SUBSTR(prenoms,  1, INSTR(prenoms, ',') - 1) END AS prenom,
        CASE INSTR(prenoms, ',') = 0 THEN '' ELSE SUBSTR(prenoms,  INSTR(prenoms, ',') + 1, LENGTH(prenoms) - INSTR(prenoms, ',')) END AS prenoms
 FROM recherche
UNION ALL
SELECT nom, CASE INSTR(prenoms, ',') = 0 THEN prenoms ELSE SUBSTR(prenoms,  1, INSTR(prenoms, ',') - 1) END AS prenom,
        CASE INSTR(prenoms, ',') = 0 THEN '' ELSE SUBSTR(prenoms,  INSTR(prenoms, ',') + 1, LENGTH(prenoms) - INSTR(prenoms, ',')) END AS prenoms
 FROM split_prenom 
WHERE prenoms <> ''
)
 
SELECT *
FROM  CLIENTS AS C
          JOIN split_prenom AS R
          ON C.nom = R.nom AND C.prenom = R.prenom

Non testé

A +

Bonjour,

Merci pour cette piste intéressante ! Je tâche de faire cela dès demain. Il va me falloir faire une petite mécanique pour cumuler les prénoms dans une même colonne.

J'espère vite répondre.

Merci !

**DomIII** · 26/03/2024, 21h54

Envoyé par sevyc64

Même réponse que SQLPro, si tu peux avoir tes 100000 noms sous une forme qui peut être injectée en masse dans une table, créer une table, même temporaire, y injecter les données, et ensuite requête avec jointure.

A défaut, si jamais tu peux quand même scripter sur ces 100000 noms, tu peux toujours essayer de faire un script qui va te générer les 100000 requêtes pour éviter de les taper à la main.

Par contre, nom/prénom, c'est clairement pas suffisant pour identifier une personne. Tu vas potentiellement avoir une bonne quantité de doublons juste avec ces infos-là.

Tout à fait : je m'attends à des doublons... il me faudra les transmettre pour justement les régler.

**vanagreg** · 27/03/2024, 09h17

Envoyé par DomIII

Bonjour,

Les noms et prénoms sont stockés dans un fichier texte, ils ont été extraits d'une autre base. C'est bien là tout mon problème !

La casse est une difficulté supplémentaire, toutes ces chaînes sont converties en majuscules. Enfin, les lettres accentuées sont peu nombreuses, cet aspect peut être négligé.

Ok dans ce cas tu peux créer une table externe qui pointe sur ton fichier. C'est une table qui te permet de faire un mapping direct entre ton fichier et une table :
https://oracle.developpez.com/guide/...age=Chap1#L1-5

Tu peux alors utiliser cette table pour la joindre à la table CLIENT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select * from table_ext e
where exists (select 1 from client c
                  where c.nom = e.nom and c.prenom = e.prenom
                 );

**tbc92** · 27/03/2024, 15h21

Quels sont les droits dont tu disposes ? Si tu poses cette question, j'ai le sentiment que tu as des autorisations très limitées ?

Si tu as les droits pour faire uniquement des SELECT, ça va être très long. Demande de l'aide en interne, à quelqu'un qui a plus de droits.
Si tu as les droits pour créer des tables, c'est mieux.
Créer une table externe qui pointe sur ton fichier, comme proposé par vanagreg, ça peut être encore plus simple. Mais pour ça, il faut que tu aies les droits pour déposer ton fichier sur le serveur de ta machine oracle.

Selon les droits que tu as, ta question est soit comment trouver une *%$#!&*ç de solution, soit quelle solution choisir parmi toutes les possibilités.

Et peut-être que le problème principal que tu vas rencontrer, c'est un truc avec les prénoms multiples, ou avec les accents, ou avec les doublons. Plein de petits pièges possibles dès qu'on veut faire des traitements sur des chaines de caractères pas forcément normalisées.

**DomIII** · 28/03/2024, 09h02

Envoyé par tbc92

Quels sont les droits dont tu disposes ? Si tu poses cette question, j'ai le sentiment que tu as des autorisations très limitées ?

Si tu as les droits pour faire uniquement des SELECT, ça va être très long. Demande de l'aide en interne, à quelqu'un qui a plus de droits.
Si tu as les droits pour créer des tables, c'est mieux.
Créer une table externe qui pointe sur ton fichier, comme proposé par vanagreg, ça peut être encore plus simple. Mais pour ça, il faut que tu aies les droits pour déposer ton fichier sur le serveur de ta machine oracle.

Selon les droits que tu as, ta question est soit comment trouver une *%$#!&*ç de solution, soit quelle solution choisir parmi toutes les possibilités.

Et peut-être que le problème principal que tu vas rencontrer, c'est un truc avec les prénoms multiples, ou avec les accents, ou avec les doublons. Plein de petits pièges possibles dès qu'on veut faire des traitements sur des chaines de caractères pas forcément normalisées.

Bonjour,

Je ne dispose que de droits de lecture et ne peux accéder la base Oracle qu'à distance. Je me suis résolu à développer une petite application Java/Hibernate qui cherche les personnes. Il tourne depuis hier matin, j'ai calculé par analogie ce matin que 6 semaines seront nécessaires... en admettant que rien n'arrive. J'ai demandé RV à la responsable de projet pour obtenir le droit de faire une table temporaire comme cela m'a été conseillé ici.

**DomIII** · 28/03/2024, 09h02

Envoyé par vanagreg

Ok dans ce cas tu peux créer une table externe qui pointe sur ton fichier. C'est une table qui te permet de faire un mapping direct entre ton fichier et une table :
https://oracle.developpez.com/guide/...age=Chap1#L1-5

Tu peux alors utiliser cette table pour la joindre à la table CLIENT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select * from table_ext e
where exists (select 1 from client c
                  where c.nom = e.nom and c.prenom = e.prenom
                 );

Un immense merci pour cette suggestion. Je fais le forcing pour avoir accès au serveur.

**DomIII** · 10/04/2024, 10h32

Bonjour,

Le problème est en cours de résolution, après quelques jours de travail.

J'ai fini par créer une nouvelle base MySQL en y important les tables nécessaires de la base initiale. J'ai donc appliqué les conseils ci-dessus, à savoir générer une table contenant pour chaque instance le nom de famille et tous les prénoms (ainsi, plusieurs personnes de même nom sont représentés par une seule et unique instance). A partir de la requête proposée par SQLpro, j'en ai créé une permettant des croisements. Je l'ai programmée en Java/Hibernate 6, cela tourne depuis 7 jours, j'en suis à environ 35% du processus total. Je prévois la fin entre les 22 et 23 avril prochains. Cette décision de prendre Java me permet de tout tracer dans des fichiers différents (merci log4J), en fonction de l'initiale du nom. J'ai donc 26 fichiers distincts, ce qui me facilite l'exploitation.

A l'heure actuelle, près de 80% des personnes cherchées ont été trouvées.

Je reviendrai ici dès que ce sera terminé.

Encore merci pour votre aide.

**tbc92** · 10/04/2024, 18h06

Tout ça me paraît bien long.

Tu as créé une base MySQL. Bien. Donc sur cette base, tu as tous les droits.
Tu peux créer une table avec tes 19Millions d'enregistrements. Tu peux créer une autre table avec tes 100 000 enregistrements. Admettons que tout ça prenne une journée.
Ensuite, tu peux faire une requête qui croise ces 2 tables. Et tu devrais avoir ta réponse en 1 heure au grand maximum. Disons quelques heures si tu fais ça sur un PC de base un peu fatigué.

**escartefigue** · 11/04/2024, 09h13

Dans une table de 100 000 personnes, il est très probable qu'avec seulement le nom et le prénom, il y a de nombreux homonymes.

Ce faisant, quelle que soit la méthode utilisée, les résultats ne seront pas fiables.

Et effectivement, rapprocher 100 000 de 19 000 000 devrait durer tout au plus 30 minutes sur un ordinateur peu performant...

Rechercher 100 mille personnes dans une base en contenant près de 19 millions

Oracle

Discussions similaires

Partager

Partager