Discussion :

[bambi98]

Bonjour à tous
c'est après avoir assez cherché que je me tourne vers vous.
je souhaite comprendre comment réaliser une requête SQL contenant les mots TOUS LES.
par exemple afficher les informations sur une personne possédant tous les chevaux.
Bien entendu j'ai deux table PERSONNE et CHEVAL ,et un cheval peut être possédé par plusieurs personne.
Également à l'inverse je souhaiterai comprendre comment écrire une requête contenant AUCUN comme par exemple une personne qui n'a AUCUN cheval.
Pour la réflexion sur le sujet,j'ai pensé à faire un produit cartésien puis une différence. Mais je coince.
Merci pour vos réactions

[dehorter olivier]

il faudrait ajouter la structure des tables, avec pourquoi pas un jeu de données et des exemples de sorties.


En première lecture, je pensais qu'il s'agissait de recherche textuelle.
Mais en relisant ce ne semble pas être le cas, et réalisable avec des jointures ou du EXISTS...

[StringBuilder]

Je pense qu'avec un bon gros having, on peut s'en sortir.

N'y aurait-il pas aussi une table de correspondance "proprietaire" avec un lien entre "cheval" et "personne" ?

[Invité]

D'après ce que je comprend je suppose une représentation tel que :
la table PERSONNE id_personne, nom personne, et autres informations utiles
la table CHEVAL id_cheval,#id_personne, nom du cheval et autres informations utiles.
Avec un SELECT id_personne FROM PERSONNE WHERE id_personne NOT IN (SELECT id_personne FROM CHEVAL) on doit obtenir la liste des personnes ne possédant aucun cheval
Avec un SELECT id_personne FROM CHEVAL GROUP BY id_personne HAVING COUNT(id_personne) = (SELECT COUNT(DISTINCT id_cheval) FROM CHEVAL)
on doit obtenir la liste des personnes possédant tous les chevaux.

Voir si ces propositions correspondent à ce qui est recherché

[StringBuilder]

Si un cheval peut être possédé par plusieurs personnes, alors il y a une table de correspondance entre cheval et personne (donc pas de personne_id dans la table cheval) ou alors il y a des doublons dans la table cheval.

[SQLpro]

Tous et aucun signifie en général que l'on va devoir faire une division relationnelle.

Lisez l'article que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/divrelationnelle/

A +

[CinePhil]

Bien entendu j'ai deux table PERSONNE et CHEVAL ,et un cheval peut être possédé par plusieurs personne.

Donc, comme l'ont dit mes prédecesseurs, il manque une table associative !

Règle de gestion :
Une personne peut posséder plusieurs chevaux et un cheval est possédé par une à plusieurs personnes.

MCD :
personne -0,n----posséder----1,n- cheval

Tables :
te_personne_prs (prs_id, prs_nom...)
te_cheval_chv (chv_id, chv_nom...)
tj_prs_posseder_chv_ppc (ppc_id_personne, ppc_id_cheval...)

D'une manière générale pour exprimer "TOUS LES", il faut grouper, compter et comparer au nombre total.
Quelles sont les personnes qui possèdent tous les chevaux :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT prs_id, prs_nom
FROM te_personne_prs p
INNER JOIN tj_prs_posseder_chv_ppc pc ON pc.ppc_id_personne = p.prs_id
	INNER JOIN te_cheval_chv c ON c.chv_id = pc.ppc_id_cheval
GROUP BY prs_id, prs_nom
HAVING COUNT(c.chv_id) =
(
	SELECT COUNT(*)
	FROM te_cheval_chv
)

Pour exprimer AUCUN, c'est une requête avec NOT EXISTS dans les restrictions.
Quelles sont les personnes qui ne possèdent aucun cheval ?

Code :

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

1
2
3
4
5
6
7
8
SELECT prs_id, prs_nom
FROM te_personne_prs p
WHERE NOT EXISTS
(
	SELECT 1
	FROM tj_prs_posseder_chv_ppc pc
	WHERE pc.ppc_id_personne = p.prs_id
)

[StringBuilder]

J'aurais fait un truc du genre :

Avec en plus une colonne "id" dans la table "tj_prs_posseder_chv_ppc", sinon pour le distinct c'est chaud les marrons.

TOUS :

Code sql :

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

1
2
3
4
5
6
 
select p.prs_nom, count(distinct c.chv_id) nbchevaux, count(distinct r.id) nbpossede
from te_personne_prs p
cross join te_cheval_chv c
left outer join tj_prs_posseder_chv_ppc r on r.ppc_id_personne = p.prs_id and r.ppc_id_cheval = c.chv_id
group by p.prs_nom having count(distinct c.chv_id) = count(distinct r.id)

AUCUN :

Code sql :

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

1
2
3
4
5
6
 
select p.prs_nom, count(distinct c.chv_id) nbchevaux, count(dictinct r.id) nbpossede
from te_personne_prs p
cross join te_cheval_chv c
left outer join tj_prs_posseder_chv_ppc r on r.ppc_id_personne = p.prs_id and r.ppc_id_cheval = c.chv_id
group by p.prs_nom having count(dictinct r.id) = 0

A tester.

[CinePhil]

Avec en plus une colonne "id" dans la table "tj_prs_posseder_chv_ppc"

Inutile !
La clé primaire d'une table associative est composée des clés étrangères référençant les identifiants des tables impliquées dans l'association.

TOUS :

Code :

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

1
2
3
4
5
6
 
SELECT p.prs_nom, count(DISTINCT c.chv_id) nbchevaux, count(DISTINCT r.id) nbpossede
FROM te_personne_prs p
CROSS JOIN te_cheval_chv c
LEFT OUTER JOIN tj_prs_posseder_chv_ppc r ON r.ppc_id_personne = p.prs_id AND r.ppc_id_cheval = c.chv_id
GROUP BY p.prs_nom HAVING count(DISTINCT c.chv_id) = count(DISTINCT r.id)

Pourquoi tu te compliques la vie ?
Une personne ne peut pas posséder deux fois le même cheval, du fait justement de l'unicité de la clé primaire formée du couple {identifiant de la personne, identifiant du cheval}. En groupant par personne, chaque cheval ne pourra être compté qu'une seule fois pour une personne et le COUNT(DISTINCT) est inutile.

Ma requête peut cependant être simplifiée en supprimant une jointure :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT prs_id, prs_nom
FROM te_personne_prs p
INNER JOIN tj_prs_posseder_chv_ppc pc ON pc.ppc_id_personne = p.prs_id
GROUP BY prs_id, prs_nom
HAVING COUNT(pc.ppc_id_cheval) =
(
	SELECT COUNT(*)
	FROM te_cheval_chv
)

Quant à la seconde requête, pourquoi faire un groupage et un comptage quand il suffit de vérifier que la personne ne figure pas dans la table associative ?

[StringBuilder]

Selon les SGBD, ils n'aiment pas forcément les clés composites en clé primaires, d'autant que ça empêche de faire une clé clustered (sinon gare aux latences quand il faut décaler la moitié des lignes de la table suite à une insertion en masse)
Donc le fait de mettre une clé primaire mono-colonne auto-incrément sur une table de relation n'est pas forcément aussi inutile que ça (demande à SQLPro ce qu'il en pense, à l'origine je pensais comme toi, et il m'a convaincu).

Le clé basée sur les deux colonnes de liaison est alors une simple contrainte d'unicité, c'est à dire une clé alternative.

Le count(distinct) sur la table des chevaux est peut-être inutle (j'ai pas de base sous la main pour tester). Je l'ai mis à cause du cross join entre personnes et chevaux qui permet de compter l'ensemble des chevaux, je ne voudrais pas que ça fausse le comptage des propriétaires.

La seconde requête est la même que la première (à la clause having près) et c'est pour ça que je l'ai mise telle quelle.

Ces deux solutions permettent de ne pas passer par une sous-requête, ce que je trouve plus clean. Après, selon le SGBD je ne saurais dire si c'est effectivement plus rapide, plus lent ou strictement identique.

[bambi98]

merci pour vos réponses
Elles m'ont édifié sur la question.
Le cours de SQLPRO est tres explicite sur le sujet

[fsmrel]

Bonjour,

gare aux latences quand il faut décaler la moitié des lignes de la table suite à une insertion en masse)
Donc le fait de mettre une clé primaire mono-colonne auto-incrément sur une table de relation n'est pas forcément aussi inutile que ça.
Le clé basée sur les deux colonnes de liaison est alors une simple contrainte d'unicité, c'est à dire une clé alternative.

Disons que ce que vous appelez latence c’est SGBD dépendant... Par exemple, avec DB2 for z/OS, il n’y a pas de décalage, mais chargement des données dans de nouvelles pages des table spaces.

Si l’insert de masse est effectué pour des valeurs de la clé alternative comprises entre deux valeurs existantes, de toutes façons l’index que vous aurez défini pour cette clé en prendra un coup, et votre index primaire n’y pourra rien.

Le principe de définir systématiquement une colonne supplémentaire — qui soit le seul élément de la clé primaire de la table associative répondant au doux nom de tj_prs_posseder_chv_ppc — a pour conséquence la mise en œuvre d’un index de plus, alors que les index portant d’une part sur la colonne ppc_id_personne et d’autre part sur la colonne ppc_id_cheval sont nécessaires dès qu’il s’agit d’effectuer des jointures et autres opérations mettant en jeu ces colonnes. Maintenant, si j’en reste à la clé primaire initiale {ppc_id_cheval, ppc_id_personne} et à supposer par exemple que les traitements de masse portent essentiellement sur les chevaux, l’index X1 associé au couple {ppc_id_cheval, ppc_id_personne} peut devenir l’index cluster (en tout cas c’est ce que je ferai dans le cas de DB2). En tout, j’ai deux index, le premier servant pour la clé alternative, pour le clustering sur ppc_id_cheval et pour les jointures, les EXISTS, les IN, etc. impliquant la colonne ppc_id_cheval (index X1) et le second servant pour les jointures, les EXISTS, les IN, etc. impliquant la colonne ppc_id_personne (index X2). Donc pas de colonne supplémentaire conduisant à un index cluster se cantonnant à un rôle réduit (et qui de facto interdit tout autre clustering), à savoir éviter une latence qui reste à démontrer (en tout cas avec DB2 c’est tout vu).

L’identification absolue que vous mettez en oeuvre peut être un facteur d’inhibition de l’optimiseur du SGBD. Par référence à l’exemple 4 du paragraphe Dénormalisation vs amélioration (optimisation) de l’article Bases de données relationnelles et normalisation : de la première à la sixième forme normale, en identifiant systématiquement de façon absolue, on empêche l’optimiseur de DB2 de ramener une jointure de 6 tables à une jointure de 2 tables, alors qu’il sait parfaitement le faire.

Il y aurait aussi beaucoup à dire sur l’effet I/O bound.


J'ai peut-être raté quelque chose en ce qui concerne les SGBD autres que DB2 for z/OS, aussi j'aimerais connaître les contre-arguments à leur propos.

N.B. Quand les mises à jour de masse sont relativement copieuses, on peut parfois avoir intérêt à débrancher les index — sauf peut-être l'index cluster — avant d'effectuer les opérations, pour les rebrancher ensuite (en les réorganisant), voyez l'exemple des lots/remises de chèques du paragraphe Retour sur la dénormalisation a priori de l'article dont j'ai fait mention. En tout cas avec DB2 c'est efficace.

[StringBuilder]

Je ne parle pas de l'équilibrage de l'index, qui sera rééquilibré en tâche de fond, de façon transparente, mais de l'organisation des données sur le disque.
Je ne suis pas certain que DB2 s'en sorte mieux que les autres SGBD en utilisant de nouvelles pages sur le tablespace : si à chaque fois on se retrouve avec une ligne par page et que toutes les pages sont mélangées dans le tablespace, je ne vois plus trop l'intérêt d'avoir une clé clustered.

Aussi, si la table de référence doit être elle-même référencée (cas d'une relation porteuse de propriétés en 0,n par exemple) alors il faudra de toute façon, pour des raisons évidentes de performances, avoir une clé mono-colonne sur cette table.

Je ne vois donc pas en soit ce qui est choquant de créer une telle colonne clé primaire, même si "pour le moment" son utilité est discutable.

Je me base sur diverses interventions de SQLPro, qui mettait plutôt en avant le fonctionnement de SQL Server. Peut-être que j'ai mal compris ses explications, ou qu'elles sont vraiment propres à SQL Server, mais je n'en suis pas sûr.

PS : Je ne préconise pas la création d'une telle colonne, je dénonce juste le fait que sa création soit traitée d'hérésie.

[fsmrel]

Je ne parle pas de l'équilibrage de l'index

Moi non plus. Je dis simplement que les index peuvent être désorganisés par des mises à jour de masse, quel que soit le scénario retenu, c'est tout.

Je ne suis pas certain que DB2 s'en sorte mieux que les autres SGBD en utilisant de nouvelles pages sur le tablespace : si à chaque fois on se retrouve avec une ligne par page et que toutes les pages sont mélangées dans le tablespace, je ne vois plus trop l'intérêt d'avoir une clé clustered.

Vous avez une perception pour le moins confuse des choses. Pour vous, qu’est-ce qu’un table space DB2 ? Comment cela fonctionne-t-il ? Ne vous inquiétez pas pour DB2. Comme je l’ai dit, il suffit au besoin de ne pas supprimer l’index cluster et tout est bon. Au DBA de définir sa propre stratégie après avoir expérimenté les différents scénarios qu'il a en tête.

Aussi, si la table de référence doit être elle-même référencée (cas d'une relation porteuse de propriétés en 0,n par exemple) alors il faudra de toute façon, pour des raisons évidentes de performances, avoir une clé mono-colonne sur cette table.

Au contraire. Prenez le cas le cas des tables des commandes, lignes de commande, engagements etc. dont je parle dans les exemples auxquels je vous ai renvoyé. Avec DB2, si l’index cluster est sur CliId de A à Z, les inserts progressent de façon synchrone, sans I/O bound. Des nuits de prototypage des performances m’ont enseigné cela. Quant à vos raisons évidentes, merci de les préciser, d’apporter des preuves irréfutables, on ne bâtit pas une thèse sur l’« évidence ». Pour ma part seuls les résultats des prototypages de performance peuvent confirmer/infirmer les intuitions. Quant à SQL Server et autres, à moins de tout gober, prototyper n’est pas non plus interdit.

Je ne vois donc pas en soit ce qui est choquant de créer une telle colonne clé primaire, même si "pour le moment" son utilité est discutable.

Faites-le, mais à tout le moins c'est un cautère sur une jambe de bois et, comme je l’ai déjà écrit, dans le cas de DB2 c’est le meilleur moyen d’inhiber l'optimiseur.

Je me base sur diverses interventions de SQLPro, qui mettait plutôt en avant le fonctionnement de SQL Server.

Donc limitez-vous à SQL Server sans chercher à généraliser et en plus, prototypez, prouvez.

Je ne préconise pas la création d'une telle colonne, je dénonce juste le fait que sa création soit traitée d'hérésie.

Qui a parlé d’hérésie ? Tout au plus ai-je parlé d’inefficacité dans le cas général. Pour ma part, je me contente d’en rester aux faits, aux constats sur plus de 25 ans d’observation de DB2 dans les tréfonds de la soute (et quarante ans si je prends en compte mon expérience des SGBD non relationnels), sur ce qu’il est convenu d’appeler les « Very Large Databases » dans la presse du cœur informatique et à petite distance des petits-fours et du champagne après les présentations par les gens du marketing.