Plans Composites

COMMENTSInstructions de procédure SQL (PSQL)

Plans composites

Si vous établissez une connexion, vous pouvez spécifier l’index à utiliser pour le mappage. Vous devez également utiliser la directive JOIN pour les deux threads du plan :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s NATURAL, c INDEX (pk_classes))

Plan sous forme d’EXPLAIN :

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

Même connexion, triée par colonne indexée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s ORDER pk_students, c INDEX (pk_classes))
ORDER BY s.id

Plan sous forme d’EXPLAIN :

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Access By ID
          -> Index "PK_STUDENTS" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

Et une connexion triée par une colonne non indexée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN SORT (JOIN (S NATURAL, c INDEX (pk_classes))))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 152, key length: 12)
      ->  Nested Loop Join (inner)
          -> Table "STUDENTS" as "S" Full Scan
          -> Filter
              -> Table "CLASSES" as "C" Access By ID
                  -> Bitmap
                      -> Index "PK_CLASSES" Unique Scan

Connexion avec une condition de recherche ajoutée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (s INDEX (fk_student_class), c INDEX (pk_classes)))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 152, key length: 12)
     ->  Nested Loop Join (inner)
         -> Filter
            -> Table "STUDENTS" as "S" Access By ID
               -> Bitmap
                  -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
         -> Filter
            -> Table "CLASSES" as "C" Access By ID
               -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

La même chose, mais en utilisant la connexion externe de gauche :

SELECT s.id, s.name, s.class, c.mentor
FROM classes c
LEFT JOIN students s ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (c NATURAL, s INDEX (fk_student_class)))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 192, key length: 56)
      -> Filter
         ->  Nested Loop Join (outer)
             -> Table "CLASSES" as "C" Full Scan
             -> Filter
                -> Table "STUDENTS" as "S" Access By ID
                   -> Bitmap
                      -> Index "FK_STUDENT_CLASS" Range Scan (full match)

Si aucun index n’est disponible pour la condition de jointure (ou si vous ne voulez pas en utiliser un), il est possible de joindre des fils en utilisant la méthode HASH ou MERGE.

Pour une jointure HASH, le plan utilise la directive HASH au lieu de la directive JOIN. Dans ce cas, le plus petit thread (esclave) est lu complètement dans le tampon interne. Pendant le processus de lecture, une fonction de hachage est appliquée à chaque clé de lien et la paire {hash, pointeur dans le tampon} est écrite dans la table de hachage. Le thread maître est ensuite lu et sa clé de lien est validée dans la table de hachage.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN HASH (c NATURAL, s NATURAL)

Plan sous forme d’EXPLAIN :

Select Expression
    -> Filter
        -> Hash Join (inner)
            -> Table "STUDENTS" as "S" Full Scan
            -> Record Buffer (record length: 145)
                -> Table "CLASSES" as "C" Full Scan

Lors d’une jointure MERGE, le plan doit d’abord trier les deux threads par les colonnes à joindre, puis effectuer la fusion. Ceci est réalisé avec la directive SORT (que vous avez déjà vue) et MERGE utilisée à la place de JOIN.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN MERGE (SORT (c NATURAL), SORT (s NATURAL))

L’ajout de la clause "ORDER BY" signifie que le résultat de la fusion doit également être trié :

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN SORT (MERGE (SORT (c NATURAL), SORT (s NATURAL)))
ORDER BY c.name, s.id

Enfin, nous ajoutons une condition de recherche sur les deux colonnes indexées de la table STUDENTS :

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
WHERE s.id < 10 AND s.class <= '2'
PLAN SORT (MERGE (SORT (c NATURAL),
                  SORT (s INDEX (pk_students, fk_student_class))))
ORDER BY c.name, s.id

Comme le suggère la définition formelle de la syntaxe, JOIN et MERGE peuvent combiner plus de deux threads dans un plan. De plus, chaque expression de plan peut être utilisée comme élément d’un plan englobant. Cela signifie que les plans de certaines requêtes complexes peuvent avoir différents niveaux d’imbrication.

Enfin, vous pouvez écrire SORT MERGE au lieu de MERGE. Comme cela n’a absolument aucune signification et peut créer une confusion avec la directive SORT (qui a une signification), il est probablement préférable de s’en tenir à la simple directive MERGE.

En plus du plan pour la requête principale, vous pouvez spécifier un plan pour chaque sous-requête. Par exemple, la requête suivante avec les plans spécifiés serait absolument correcte.

SELECT *
FROM COLOR
WHERE EXISTS(
             SELECT *
             FROM HORSE
             WHERE HORSE.CODE_COLOR = COLOR.CODE_COLOR
             PLAN (HORSE INDEX (FK_HORSE_COLOR)))
PLAN(COLOR NATURAL)

`UNION`

La clause UNION joint deux ou plusieurs ensembles de données, augmentant ainsi le nombre total de lignes mais pas de colonnes. Les ensembles de données participant à l’union doivent avoir le même nombre de colonnes. Cependant, les colonnes dans les positions respectives ne doivent pas nécessairement être du même type de données, elles peuvent être complètement indépendantes.

Par défaut, la fusion supprime les lignes en double.UNION ALL affiche toutes les chaînes de caractères, y compris les doublons. Le mot-clé optionnel DISTINCT rend le comportement par défaut explicite.

Syntaxe

<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [<rows-clause> | {[<result-offset-clause>] [<fetch-first-clause>]}]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body>
      [<order-by-clause>]
      [<result-offset-clause>] [<fetch-first-clause>]
    )

<query-specification> ::=
  SELECT
    <limit-clause>
    [{ ALL | DISTINCT }] <select-list>
  FROM <table-reference> [, <table-reference> ...]
  [WHERE <search-condition>]
  [GROUP BY <value-expression> [, <value-expression> ...]]
  [HAVING <search-condition>]
  [WINDOW <window-definition> [, <window-definition> ...]]
  [PLAN <plan-expression>]

Les jointures récupèrent les noms de colonnes de la première requête de sélection.Si vous voulez aliaser les colonnes à fusionner, faites-le pour la liste des colonnes dans la requête la plus haute de la sélection.Les alias dans d’autres échantillons participants sont autorisés, et peuvent même être utiles, mais ils ne se propageront pas au niveau de la fusion.

Si l’union a une clause ORDER BY, les seuls éléments de tri possibles sont des littéraux entiers pointant sur les positions des colonnes, pas nécessairement accompagnés des directives ASC | DESC et/ou NULLS {FIRST | LAST}.Cela signifie également que vous ne pouvez pas ordonner l’union par un élément qui n’est pas une colonne d’union.(Vous pouvez toutefois l’envelopper dans une table dérivée, qui vous donnera tous les paramètres de tri habituels).

Les fusions sont autorisées dans les sous-requêtes de tout type, et peuvent contenir des sous-requêtes à part entière.Ils peuvent également contenir des jointures, et peuvent prendre part à des jointures s’ils sont enveloppés dans une table dérivée.

Exemples

Cette requête présente des informations provenant de différentes collections de musique dans un seul ensemble de données en utilisant des associations :

SELECT id, title, artist, len, 'CD' AS medium
FROM cds
UNION
SELECT id, title, artist, len, 'LP'
FROM records
UNION
SELECT id, title, artist, len, 'MC'
FROM cassettes
ORDER BY 3, 2 -- artist, title

Si id, title, artist et length sont les seuls champs de toutes les tables participantes, la requête peut être écrite comme suit

SELECT c.*, 'CD' AS medium
FROM cds c
UNION
SELECT r.*, 'LP'
FROM records r
UNION
SELECT c.*, 'MC'
FROM cassettes c
ORDER BY 3, 2 -- artist, title

La clarification des " * " ( astérisque ) est nécessaire ici car elles ne sont pas le seul élément de la liste des colonnes. Notez que les alias "c" dans le premier et le troisième échantillon ne se mordent pas l’un l’autre. Ils n’ont pas de contexte d’association, mais s’appliquent uniquement aux requêtes individuelles de l’échantillon.

La requête suivante permet d’obtenir les noms et les numéros de téléphone des traducteurs et des correcteurs. Les traducteurs qui travaillent également comme correcteurs ne seront affichés qu’une seule fois dans le jeu résultant si leurs numéros de téléphone sont les mêmes dans les deux tables. Le même résultat peut être obtenu sans le mot-clé DISTINCT. Si le mot-clé ALL est spécifié au lieu de DISTINCT, ces personnes seront affichées deux fois.

SELECT name, phone
FROM translators
UNION DISTINCT
SELECT name, telephone
FROM proofreaders

Un exemple de l’utilisation de UNION dans une sous-requête :

SELECT name, phone, hourly_rate
FROM clowns
WHERE hourly_rate < ALL
  (SELECT hourly_rate FROM jugglers
   UNION
   SELECT hourly_rate FROM acrobats)
ORDER BY hourly_rate

L’utilisation d’expressions de requête entre parenthèses permet d’afficher les salariés les mieux payés et les moins bien payés :

(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);