Составные Планы

COMMENTSОператоры процедурного SQL (PSQL)

Составные планы

Если вы делаете соединение, то вы можете указать индекс, который будет использоваться для сопоставления.Кроме того, вы должны использовать директиву JOIN для двух потоков в плане:

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))

План в 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

То же самое соединение, отсортированное по индексированному столбцу:

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

План в 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

И соединение, отсортированное не по индексированному столбцу:

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

План в 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

Соединение с добавленным условием поиска:

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

План в 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

То же самое, но используется левое внешнее соединение:

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

План в 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)

Если нет доступных индексов для условия соединения (или вы не хотите его использовать), то возможно соединение потоков с помощью метода HASH или MERGE.

Для соединения методом HASH в плане вместо директивы JOIN используется директива HASH.В этом случае меньший (ведомый) поток целиком вычитывается во внутренний буфер.В процессе чтения к каждому ключу связи применяется хеш-функция и пара {хеш, указатель в буфере} записывается в хеш-таблицу.После чего читается ведущий поток и его ключ связи апробируется в хеш-таблице.

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

План в EXPLAIN форме:

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

При выполнении соединения методом MERGE план должен сначала отсортировать оба потока по соединяемым столбцам и затем произвести слияние.Это достигается с помощью директив SORT (которую вы уже встречали) и MERGE используемую вместо JOIN.

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

Добавление предложения ORDER BY означает, что результат слияния также должен быть отсортирован:

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

И наконец, мы добавляем условие поиска на двух индексированных столбцах таблицы 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

Как следует из формального определения синтаксиса, JOIN и MERGE могут объединять в плане более двух потоков.Кроме того, каждое выражение плана может использоваться в качестве элемента в охватывающем плане.Это означает, что планы некоторых сложных запросов могут иметь различные уровни вложенности.

Наконец, вместо MERGE вы можете писать SORT MERGE.Поскольку это не имеет абсолютно никакого значения и может создать путаницу с “настоящей” директивой SORT (которая действительно имеет значение), то вероятно лучше придерживаться простой директивы MERGE.

Помимо плана для основного запроса вы можете указать план для каждого подзапроса.Например, следующий запрос с указанием планов будет абсолютно правильным.

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`

Предложение UNION объединяет два и более набора данных, тем самым увеличивая общее количество строк, но не столбцов.Наборы данных, принимающие участие в UNION, должны иметь одинаковое количество столбцов.Однако столбцы в соответствующих позициях не обязаны иметь один и тот же тип данных, они могут быть абсолютно не связанными.

По умолчанию, объединение подавляет дубликаты строк.UNION ALL отображает все строки, включая дубликаты.Необязательное ключевое слово DISTINCT делает поведение по умолчанию явным.

Синтаксис

<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>]

Объединения получают имена столбцов из первого запроса на выборку.Если вы хотите дать псевдонимы объединяемым столбцам, то делайте это для списка столбцов в самом верхнем запросе на выборку.Псевдонимы в других участвующих в объединении выборках разрешены, и могут быть даже полезными, но они не будут распространяться на уровне объединения.

Если объединение имеет предложение ORDER BY, то единственно возможными элементами сортировки являются целочисленные литералы, указывающие на позиции столбцов, необязательно сопровождаемые ASC | DESC и/или NULLS {FIRST | LAST} директивами.Это так же означает, что вы не можете упорядочить объединение ничем, что не является столбцом объединения.(Однако вы можете завернуть его в производную таблицу, которая даст вам все обычные параметры сортировки.)

Объединения позволены в подзапросах любого вида и могут самостоятельно содержать подзапросы.Они также могут содержать соединения (joins), и могут принимать участие в соединениях, если завёрнуты в производную таблицу.

Примеры

Этот запрос представляет информацию из различных музыкальных коллекций в одном наборе данных с помощью объединений:

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

Если id, title, artist и length – единственные поля во всех участвующих таблицах, то запрос может быть записан так:

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

Уточнение “звёзд” необходимо здесь, потому что они не являются единственным элементом в списке столбцов.Заметьте, что псевдонимы “c” в первой и третьей выборке не кусают друг друга.Они не имеют контекста объединения, а лишь применяются к отдельным запросам на выборку.

Следующий запрос получает имена и телефонные номера переводчиков и корректоров.Те переводчики, которые также работают корректорами, будут отображены только один раз в результирующем наборе, если номера их телефонов одинаковые в обеих таблицах.Тот же результат может быть получен без ключевого слова DISTINCT.Если вместо ключевого слова DISTINCT, будет указано ключевое слово ALL, эти люди будут отображены дважды.

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

Пример использования UNION в подзапросе:

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

Использование выражений запроса в скобках для отображения сотрудников с самой высокой и самой низкой зарплатой:

(
  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
);