Aller au contenu


Photo

Index, Vues, jointures internes et externes


  • Veuillez vous connecter pour répondre
9 réponses à ce sujet

#1 Florian SELVA

Florian SELVA

    Newbie

  • Étudiant
  • Pip
  • 4 messages
  • Cursus:A.Sc.1

Posté 13 novembre 2013 - 12:21

Bonsoir,
Je souhaiterai profiter de cette section du forum afin qu’on puisse m’éclaircir sur quelques points du programme 1ORC, puisque j'ai commencé cette semaine les cours de PL/SQL.
Ca me permettrait ainsi de repartir sur de bonnes bases. ;)
 
Premier point, j’ai un peu de mal à comprendre quelle est l’utilité des vues. Quand les utilise-t-on et pourquoi ?
Deuxième point, concernant les index : je ne comprends pas trop leur concept.
Dernier point, j'ai du mal à bien faire la différence entre une jointure interne et externe, quand utiliser une plutôt que l'autre ?
 
Merci à vous de votre aide, ça me sera très certainement utile pour le cours qui arrive !
 
Bien à vous,
Florian SELVA.


#2 Emerick DUVAL

Emerick DUVAL

    Member

  • Anciens
  • PipPip
  • 32 messages
  • LocationParis
  • Cursus:Alumni

Posté 13 novembre 2013 - 01:10

Bonsoir Florian,

 

Je vais tenter de répondre à tes question le plus clairement possible, en essayant de ne pas faire trop long. :)

 

INDEX

 

Un index permet simplement de récupérer plus rapidement des lignes d’une table en se basant sur des colonnes qui contiennent des valeurs très différentes les unes des autres.

Je prends souvent l’exemple du livre de recettes : tu cherches comment faire une tarte à la framboise, tu as deux solutions : soit tu traites les pages une par une pour tomber sur la bonne (long et fastidieux), soit tu te réfères directement au sommaire (index) qui t’indiquera à quelle page aller pour lire ta recette.

Ca marche exactement pareil avec l’index d’une table : imagine que tu mettes un index sur la colonne « email » de ta table, eh bien lors de ta requête :

SELECT nom_famille, prenom, age 
FROM auteurs 
WHERE email = ‘gerard@publi.com’

l’index créé sur cette colonne « email » a permis d’accéder plus rapidement à la ligne contenant les informations recherchées car elle était « enregistrée » à l’avance.

A l’inverse, tu ne mettras pas d’index sur une colonne où beaucoup de valeurs se ressemblent (par exemple : sexe d’une personne, soit masculin soit féminin), car cela impliquerait des allers-retours incessants avec l’index, ce qui ralentirait la requête plus qu’autre chose.

Il est d’ailleurs important de noter qu’un index est automatiquement créé lorsque tu appliques une contrainte de clé primaire ou d’unicité à ta colonne (et c’est d’ailleurs assez logique, car toutes les valeurs seront différentes les unes des autres !).

 

VUES

 

Elles te permettent principalement de rendre accessibles des données précises d’une table, sans pour autant donner un accès complet à cette table.
Imagine tout simplement une personne qui débarque temporairement dans l’entreprise, et qui doit avoir accès à certaines informations (nom, prénom, numéro de téléphone) de certains employés (tous les employés du département 110).
On ne va pas lui donner un accès (SELECT) complet à la table toute entière, on va dans ce cas créer une vue qui va se baser sur une requête SQL répondant aux besoins de la situation, qui serait ici :

SELECT last_name, first_name, phone_number
FROM employees
WHERE department_id = 110 ;

On créé donc une vue en se basant sur cette requête, ce qui donne :

CREATE VIEW ma_vue AS
   SELECT last_name, first_name, phone_number
   FROM employees
   WHERE department_id = 110 ;

On donne ensuite à l’utilisateur concerné le droit d’accéder à la vue, et il pourra ensuite requêter cette vue de la même façon qu’une table. Il ne pourra ainsi voir que les employés du département 110, et ne pourra accéder au plus qu’aux trois colonnes indiquées.

Par exemple :

SELECT last_name FROM ma_vue ;
SELECT * FROM ma_vue ;
SELECT first_name, phone_number FROM ma_vue ;

Il faut donc y voir une certaine façon de filtrer l’accès à certaines données à certains utilisateurs.

On peut également se servir de vues pour rendre des requêtes un peu plus « complexes » plus accessibles. Imaginons une requête SQL impliquant 4 jointures et plusieurs fonctions.
Il suffit simplement de créer une vue en partant de cette requête, et ainsi tous les utilisateurs autorisés à requêter cette vue auront accès à toutes les données offertes par cette requête sans avoir à se farcir le code fastidieux.
Un simple SELECT * FROM ma_vue leur permettre d’avoir accès à toutes les données dont ils ont besoin !

 

JOINTURES EXTERNES

 

La jointure externe permet de récupérer plus de lignes que la jointure interne, et je vais t’expliquer ici comment.

Prenons l’exemple du cours avec une jointure entre les tables employees et departments :

SELECT *
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id) ;

Cette requête fait appel à une jointure interne (le INNER est implicite quand il n’est pas indiqué).
La ligne du ON permet de définir l’égalité sur laquelle va se baser ce qu’on appelle le « matching ».
Le matching, c’est simplement dire
« si pour une valeur dans la colonne de la table de gauche (la première) je retrouve la même valeurr dans la colonne de la table de droite (la deuxième), alors il y matching (et vice versa) »

Autrement dit ici : « Si dans la colonne department_id de table employees je trouve une valeur, et que je retrouve cette même valeur dans la colonne department_id de la table departments, alors il y a matching »

Et qu’est-ce que ça veut dire au final s’il y a matching ?
Eh bien tout simplement que nous pourrons faire une jointure interne entre la table employees et departments pour cette ligne-ci.
Par exemple, si l’employé avec l’id 500 a un department_id de 120, et que le département avec l’id 120 existe bien dans la table departments, alors dans ce cas il y a matching.
A l’inverse, si l’employé 501 n’a pas de department_id (null donc), dans ce cas il n’y a pas de matching, car il n’existe pas de department à l’id null dans la table departements.

 

Ainsi, lors d’une jointure interne, on verra apparaitre l’employé 500 avec les informations liées à son département, alors que nous ne verrons aucunement l’employé 501 qui n’a pas de département associé (pas de matching = la jointure ne le prend pas en compte).

C’est gênant quand on veut tout même afficher les employés qui n’ont pas de département.
Dans ce cas, on fait appel à une jointure externe.

On utilisera ici une jointure externe par la gauche (LEFT OUTER JOIN) qui va donc récupérer en plus toutes les occurrences de la table de gauche (la première, donc ici employees), même si il n’y a pas de matching !

SELECT *
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Ainsi, nous verrons nos deux employés 500 et 501 (avec, pour l’employé 501, toutes les colonnes liées à la table departments vides).

Et ça marche également dans l’autre sens : il peut parfaitement exister des départements qui ne sont peuplés d’aucun employé pour le moment. Dans ce cas, lors d’une jointure interne, ces départements n’apparaitront pas, alors que lors d’une jointure externes, nous les verront tout même apparaitre (avec, pour tous les départements sans employés, les colonnes liées à la table employees vides).

Le FULL OUTER JOIN étant une combinaison des deux à la fois, qui permet finalement de faire apparaitre toutes les occurrences des deux tables sans exception (dans notre exemple donc, tous les employés, même ceux sans département, et tous les départements, même ceux inoccupés).

Ce qu'il faut retenir dans tout cela, c'est qu'une jointure interne ne va pas prendre en compte toutes les occurences d'une table, oubliant ainsi toutes celles où il n'y a pas de matching. Si l'on souhaite récupérer toutes les lignes d'une des tables, il faut par conséquent opter pour une jointure externe.

 

J’espère avoir été assez clair dans mes explications. :)

Je n’ai pas été trop dans le détail pour ne pas rendre ce pavé encore plus long qu’il ne l’est, mais si tu as d’autres questions, n’hésite pas !


  • Sébastien DHERINES, Adrien GIULIANI, Mathieu STEFANI et 5 autres aiment ceci

Intervenant sur les matières Oracle et Business Intelligence à SUPINFO

 

Diplômé de la promotion 2015 - SUPINFO Paris de 2010 à 2015


#3 Florian SELVA

Florian SELVA

    Newbie

  • Étudiant
  • Pip
  • 4 messages
  • Cursus:A.Sc.1

Posté 13 novembre 2013 - 10:08

Merci Emerick pour ces explications. Je pense qu'il était difficile de faire plus court et ça m'a bien aidé à finir d'intégrer les vue, indexes et la différence inner/outer join.

 

Finalement, une vue, c'est simplement une requête stockée qu'on garde en mémoire pour la rappeler plus tard de manière plus simple,

Un index, c'est une moyen d'accéder plus rapidement et plus facilement à certaines données,

et concernant inner/outer join, INNER ne prend que si il n'y a pas de valeurs nulles (autrement dit qu'il y aie correspondance), alors que OUTER prends aussi les cas où il y a des valeurs nulles. Ca permet notammet de pas passer à coté de certains résultats dont on aurai besoin, ça dépends de ce qu'on veut traiter avec notre requête.

 

Merci bien du coup de pouce, je peux attaquer plus serainement le cours de PL/SQL ;)



#4 Emerick DUVAL

Emerick DUVAL

    Member

  • Anciens
  • PipPip
  • 32 messages
  • LocationParis
  • Cursus:Alumni

Posté 13 novembre 2013 - 10:33

Merci Emerick pour ces explications. Je pense qu'il était difficile de faire plus court et ça m'a bien aidé à finir d'intégrer les vue, indexes et la différence inner/outer join.

 

Finalement, une vue, c'est simplement une requête stockée qu'on garde en mémoire pour la rappeler plus tard de manière plus simple simple,

Un index, c'est une moyen d'accéder plus rapidement et plus facilement à certaines données,

et concernant inner/outer join, INNER ne prend que si il n'y a pas de valeurs nulles (autrement dit qu'il y aie correspondance), alors que OUTER prends aussi les cas où il y a des valeurs nulles. Ca permet notammet de pas passer à coté de certains résultats dont on aurai besoin, ça dépends de ce qu'on veut traiter avec notre requête.

 

Merci bien du coup de pouce, je peux attaquer plus serainement le cours de PL/SQL ;)

 

 

Voilà, en très résumé, c'est ça. :)

A noter que concernant l'index, c'est surtout un moyen de récupérer plus rapidement des données en basant notre recherche (clause WHERE) sur certaines colonnes. ;)


Intervenant sur les matières Oracle et Business Intelligence à SUPINFO

 

Diplômé de la promotion 2015 - SUPINFO Paris de 2010 à 2015


#5 Loic DEMEULEMEESTER

Loic DEMEULEMEESTER

    Newbie

  • Étudiant
  • Pip
  • 7 messages
  • LocationTroyes

Posté 13 novembre 2013 - 10:40

Merci à Florian et à Emerick, pour cette question et sa réponse très précise, avec des mise en situations pratiques qui permettent de mieux comprendre ce monde parfois abstrait des bases de données !

 

Bonne journée à vous deux.

 

Cordialement,


Loic DEMEULEMEESTER

 

A.Sc2. - Troyes campus


#6 Adrien BRAULT

Adrien BRAULT

    Newbie

  • Étudiant
  • Pip
  • 7 messages

Posté 13 novembre 2013 - 09:29

Voilà, en très résumé, c'est ça. :)

A noter que concernant l'index, c'est surtout un moyen de récupérer plus rapidement des données en basant notre recherche (clause WHERE) sur certaines colonnes. ;)

 

https://wiki.postgre...ndex-only_scans



#7 Emerick DUVAL

Emerick DUVAL

    Member

  • Anciens
  • PipPip
  • 32 messages
  • LocationParis
  • Cursus:Alumni

Posté 13 novembre 2013 - 11:16

 

 

Pourrais-tu expliciter ta remarque en la commentant un peu ? Ca pourrait servir à tout le monde. :)


Intervenant sur les matières Oracle et Business Intelligence à SUPINFO

 

Diplômé de la promotion 2015 - SUPINFO Paris de 2010 à 2015


#8 Julien RIOU

Julien RIOU

    Newbie

  • Members
  • Pip
  • 2 messages
  • LocationBelgium

Posté 15 novembre 2013 - 09:57

Voilà, en très résumé, c'est ça. :)

A noter que concernant l'index, c'est surtout un moyen de récupérer plus rapidement des données en basant notre recherche (clause WHERE) sur certaines colonnes. ;)

 

Un index peut être créé sur une colonne avec une contrainte de type foreign key dans le but d'améliorer les jointures sur celle-ci. De plus, si ta contrainte a été définie avec les clauses "ON DELETE CASCADE" ou "ON UPDATE CASCADE", l'index aidera cette opération sur la valeur enfant.

 

Je pense qu'Emerick t'a parlé des indexes B-Tree qui sont les indexes "par défaut". Ils sont très utiles dans un workload de type OLTP (petites transactions mais nombreuses, et qui modifient souvent les données). Il existe des indexes BITMAP qui sont utiles pour d'autres cas d'usage (data warehouse le plus souvent).

 

Tu pourras découvrir ces notions plus approfondies dans les cours des années suivantes.

 

En résumé sur les index : ils sont là pour aider l'optimizer à trouver un plan d'exécution plus optimisé et, donc, d'améliorer les performances de certaines requêtes.


PROMOTION SUPINFO 2013


#9 Adrien BRAULT

Adrien BRAULT

    Newbie

  • Étudiant
  • Pip
  • 7 messages

Posté 16 novembre 2013 - 09:54

Pourrais-tu expliciter ta remarque en la commentant un peu ? Ca pourrait servir à tout le monde. :)

 

RTFM

 

La chose qui servira le plus à tout le monde



#10 Emerick DUVAL

Emerick DUVAL

    Member

  • Anciens
  • PipPip
  • 32 messages
  • LocationParis
  • Cursus:Alumni

Posté 16 novembre 2013 - 03:31

RTFM

 

La chose qui servira le plus à tout le monde

 

On n'est pas sur ce forum pour balancer de simples liens en guise de réponse. :)
Un lien peut servir à appuyer des propos, ou aller plus en détail. Mais ça ne remplace pas une bonne explication personnelle.
J'ai bien lu ton lien mais tant que tu n'indiques pas où tu veux en venir, je n'en vois pas l'intérêt, que ce soit pour moi ou pour les futurs étudiants qui passeront sur ce sujet.

Sinon Julien, effectivement je parlais des index "basiques" et non pas des bitmap (utilisés dans le cas où les valeurs sont très ressemblantes d'une ligne à l'autre).  ;) 
C'est ceux-là qui sont principalement vus lors du cours de 1ORC, je suis donc resté sur cette base.
 


  • Adrien GIULIANI et Simon ROUGER aiment ceci

Intervenant sur les matières Oracle et Business Intelligence à SUPINFO

 

Diplômé de la promotion 2015 - SUPINFO Paris de 2010 à 2015





0 utilisateur(s) li(sen)t ce sujet

0 membre(s), 0 invité(s), 0 utilisateur(s) anonyme(s)