Des Plans Simples

COMMENTSInstructions de procédure SQL (PSQL)

Des plans simples

Les plans les plus simples se composent uniquement d’un nom de table et de la méthode d’extraction suivante. Par exemple, pour une sélection non triée à partir d’une seule table sans clause WHERE:

SELECT * FROM students
PLAN (students NATURAL)

Plan sous forme EXPLAIN:

Select Expression
  -> Table "STUDENTS" Full Scan

S’il y a une clause `WHERE', vous pouvez spécifier l’index à utiliser pour trouver des correspondances :

SELECT *
FROM students
WHERE class = '3C'
PLAN (students INDEX (ix_stud_class))

Plan sous forme EXPLAIN:

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Bitmap
              -> Index "IX_STUD_CLASS" Range Scan (full match)

La directive INDEX peut également être utilisée pour les conditions de connexion (qui seront discutées plus tard). Il contient une liste d’index séparés par des virgules.

La directive ORDER spécifie l’index qui est utilisé pour trier le jeu de données si les clause ORDER BY ou GROUP BY sont présentes :

SELECT *
FROM students
PLAN (students ORDER pk_students)
ORDER BY id

Plan sous forme EXPLAIN:

Select Expression
  -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan

Les instructions ORDER et INDEX peuvent être combinées :

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER pk_students INDEX (ix_stud_class))
ORDER BY id

Plan sous forme d’EXPLAIN :Plan sous forme d’EXPLAIN :

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "PK_STUDENTS" Full Scan
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Les instructions ORDER et INDEX sont autorisées à spécifier le même index :

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER ix_stud_class INDEX (ix_stud_class))
ORDER BY class

Plan sous forme d’EXPLAIN :

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Pour trier des ensembles de données lorsqu’un index ne peut pas être utilisé (ou que vous souhaitez supprimer son utilisation), supprimez l’instruction ORDER et faites précéder l’expression du plan de l’instruction SORT:

SELECT *
FROM students
PLAN SORT (students NATURAL)
ORDER BY name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 128, key length: 56)
      -> Table "STUDENTS" Full Scan

Ou lorsque l’index est utilisé pour la recherche :

SELECT *
FROM students
WHERE class >= '3'
PLAN SORT (students INDEX (ix_stud_class))
ORDER BY name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 136, key length: 56)
      -> Filter
          -> Table "STUDENTS" Access By ID
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Notez que l’instruction SORT, contrairement à ORDER, est en dehors des parenthèses, ce qui reflète le fait que les lignes de données sont récupérées sans être triées et triées plus tard.

Lors d’une extraction à partir d’une vue, la vue elle-même et la table participante sont spécifiées. Par exemple, si vous avez une vue FRESHMEN qui ne sélectionne que les étudiants de première année :

SELECT *
FROM freshmen
PLAN (freshmen students NATURAL)

Plan sous forme d’EXPLAIN:

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Ou, par exemple.:

SELECT *
FROM freshmen
WHERE id > 10
PLAN SORT (freshmen students INDEX (pk_students))
ORDER BY name DESC

Plan sous forme d’EXPLAIN:

Select Expression
  -> Sort (record length: 144, key length: 24)
      -> Filter
          -> Table "STUDENTS" as "FRESHMEN" Access By ID
              -> Bitmap
                  -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Remarque : si vous avez attribué un alias à une table ou à une vue, vous devez utiliser l’alias, et non le nom original, dans la clause PLAN.

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)