Оператор JOIN в Phoenix

Владимир Адаменков

Содержание

Обзор
Типы JOIN
Исключение части INNER из OUTER JOIN
Использование индексов
Hints
- Алгоритмы hash и sort-merge
- Оптимизация star join

Обзор

Phoenix полностью поддерживает стандартный SQL-синтаксис оператора JOIN. Он позволяет совмещать записи из разных таблиц при наличии общих значений.

В этой статье для примеров используются три таблицы со следующими столбцами.

Таблицы для примеров
Таблица	Столбец	Описание
states	name	Название штата
	abbr	Сокращение названия штата
	capitol	Столица штата
	largest	Крупнейший город штата
enterprises	name	Название предприятия
	country	Название страны, в которой расположено предприятие
	state	Сокращение названия штата (в США) или название региона, в котором расположено предприятие
	town	Название города, в котором расположено предприятие
people	name	Имя человека
	age	Возраст человека
	town	Город проживания человека
	state	Сокращение названия штата (в США) или название региона проживания человека
	country	Страна проживания человека

Чтобы иметь возможность выполнять примеры, приведенные в этой статье, нужно создать эти таблицы в вашем окружении Phoenix. Выполните следующее:

Загрузите CSV-файлы с тестовыми данными: people.csv, enterprises.csv и states.csv.
Поместите загруженные файлы в файловую систему хоста, на котором установлен компонент Phoenix Query Server, в любой каталог на ваше усмотрение.
Подключитесь к этому хосту и загрузите командную оболочку Phoenix, выполнив следующую команду:
```
$ /usr/lib/phoenix/bin/sqlline.py
```

Создайте пустые таблицы, выполнив следующие команды:

CREATE TABLE enterprises (
name VARCHAR(50) NOT NULL,
country VARCHAR(50),
state VARCHAR(50),
town VARCHAR(50)
CONSTRAINT pk PRIMARY KEY (name));

CREATE TABLE people (
name VARCHAR(50) NOT NULL,
age SMALLINT,
town VARCHAR(50),
state VARCHAR(50),
country VARCHAR(50)
CONSTRAINT pk PRIMARY KEY (name));

CREATE TABLE states (
name VARCHAR(50) NOT NULL,
abbr VARCHAR(50),
capitol VARCHAR(50),
largest VARCHAR(50)
CONSTRAINT pk PRIMARY KEY (name));

Выйдите из командной оболочки, нажав Ctrl+D, и загрузите данные из CSV-файлов в таблицы, выполнив следующие команды:
```
$ /usr/lib/phoenix/bin/psql.py /<csv_dir>/people.csv
$ /usr/lib/phoenix/bin/psql.py /<csv_dir>/enterprises.csv
$ /usr/lib/phoenix/bin/psql.py /<csv_dir>/states.csv
```

где <csv_dir> — путь к каталогу, в котором расположены загруженные CSV-файлы.

Типы JOIN

INNER JOIN

INNER JOIN берет каждую строку в первой таблице и ищет строку во второй таблице, для которой выполняется заданное условие. Если такая строка найдена, INNER JOIN составляет строку из значений столбцов, заданных в запросе. Эта строка добавляется к результату, и INNER JOIN продолжает работу.

Этот режим объединения используется по умолчанию, поэтому модификатор INNER в тексте запроса можно не писать.

Например, чтобы вывести на экран все предприятия из США вместе с городами их размещения и столицами соответствующих штатов, нужно выполнить следующий запрос:

SELECT e.name, e.town, s.capitol
FROM states AS s
INNER JOIN enterprises AS e
ON s.abbr=e.state;

Результат:

+------------------+---------------+-------------+
|      E.NAME      |    E.TOWN     |  S.CAPITOL  |
+------------------+---------------+-------------+
| Rockstar         | New York City | Albany      |
| Bethesda         | Rockville     | Annapolis   |
| DigitalAnvil     | Austin        | Austin      |
| Shaw Group       | Baton Rouge   | Baton Rouge |
| Microsoft        | Redmond       | Olympia     |
| Avnet            | Phoenix       | Phoenix     |
| Apple            | Cupertino     | Sacramento  |
| Obsidian         | Irvine        | Sacramento  |
| Sierra           | Los Angeles   | Sacramento  |
| Intel            | Sacramento    | Sacramento  |
| Northrop Grumman | Albuquerque   | Santa Fe    |
+------------------+---------------+-------------+

LEFT/RIGHT OUTER JOIN

LEFT OUTER JOIN работает как INNER JOIN, но также добавляет данные из первой таблицы в любом случае, независимо от выполнения заданного условия.

Например, чтобы вывести на экран все предприятия вместе с городами их размещения, а также столицы штатов для тех из них, которые располагаются в США, нужно выполнить следующий запрос:

SELECT e.name, e.town, s.capitol
FROM enterprises AS e
LEFT OUTER JOIN states AS s
ON e.state=s.abbr;

Результат:

+------------------+---------------+-------------+
|      E.NAME      |    E.TOWN     |  S.CAPITOL  |
+------------------+---------------+-------------+
| Northrop Grumman | Albuquerque   | Santa Fe    |
| DigitalAnvil     | Austin        | Austin      |
| Shaw Group       | Baton Rouge   | Baton Rouge |
| Apple            | Cupertino     | Sacramento  |
| Obsidian         | Irvine        | Sacramento  |
| Eidos            | London        |             |
| Sierra           | Los Angeles   | Sacramento  |
| Alluxi           | Monterrey     |             |
| Rockstar         | New York City | Albany      |
| Empresa          | Nogales       |             |
| Avnet            | Phoenix       | Phoenix     |
| Microsoft        | Redmond       | Olympia     |
| Bethesda         | Rockville     | Annapolis   |
| Intel            | Sacramento    | Sacramento  |
| Ubisoft          | Saint-Mande   |             |
+------------------+---------------+-------------+

RIGHT OUTER JOIN делает то же самое, но в отношении второй таблицы, а не первой.

Например, чтобы вывести на экран все штаты США вместе с их столицами, а также предприятия вместе с городами их размещения, которые могут находиться в некоторых из этих штатов, нужно выполнить следующий запрос:

SELECT s.name, s.capitol, e.name, e.town
FROM enterprises AS e
RIGHT OUTER JOIN states AS s
ON e.state=s.abbr
ORDER BY s.name
LIMIT 20;

Результат:

+-------------+--------------+----------+-------------+
|   S.NAME    |  S.CAPITOL   |  E.NAME  |   E.TOWN    |
+-------------+--------------+----------+-------------+
| Alabama     | Montgomery   |          |             |
| Alaska      | Juneau       |          |             |
| Arizona     | Phoenix      | Avnet    | Phoenix     |
| Arkansas    | Little Rock  |          |             |
| California  | Sacramento   | Apple    | Cupertino   |
| California  | Sacramento   | Obsidian | Irvine      |
| California  | Sacramento   | Sierra   | Los Angeles |
| California  | Sacramento   | Intel    | Sacramento  |
| Colorado    | Denver       |          |             |
| Connecticut | Hartford     |          |             |
| Delaware    | Dover        |          |             |
| Florida     | Tallahassee  |          |             |
| Georgia     | Atlanta      |          |             |
| Hawaii      | Honolulu     |          |             |
| Idaho       | Boise        |          |             |
| Illinois    | Springfield  |          |             |
| Indiana     | Indianapolis |          |             |
| Iowa        | Des Moines   |          |             |
| Kansas      | Topeka       |          |             |
| Kentucky    | Frankfort    |          |             |
+-------------+--------------+----------+-------------+

Модификатор OUTER подразумевается при использовании модификаторов LEFT и RIGHT, поэтому его можно опустить.

FULL OUTER JOIN

FULL OUTER JOIN объединяет LEFT JOIN и RIGHT JOIN: он соединяет строки, удовлетворяющие заданному условию, со всеми остальными строками из обеих таблиц.

Пример:

SELECT e.name, e.town, s.capitol
FROM enterprises AS e
FULL OUTER JOIN states AS s
ON e.state=s.abbr
LIMIT 20;

Результат:

+------------------+---------------+----------------+
|      E.NAME      |    E.TOWN     |   S.CAPITOL    |
+------------------+---------------+----------------+
|                  |               | Juneau         |
|                  |               | Montgomery     |
|                  |               | Little Rock    |
| Avnet            | Phoenix       | Phoenix        |
| Apple            | Cupertino     | Sacramento     |
| Obsidian         | Irvine        | Sacramento     |
| Intel            | Sacramento    | Sacramento     |
| Sierra           | Los Angeles   | Sacramento     |
|                  |               | Denver         |
|                  |               | Hartford       |
|                  |               | Dover          |
| Eidos            | London        |                |
|                  |               | Tallahassee    |
|                  |               | Atlanta        |
|                  |               | Honolulu       |
|                  |               | Des Moines     |
|                  |               | Boise          |
|                  |               | Springfield    |
|                  |               | Indianapolis   |
| Ubisoft          | Saint-Mande   |                |
+------------------+---------------+----------------+

Модификатор OUTER подразумевается при использовании модификатора FULL, поэтому его можно опустить.

SELF JOIN

Таблицу можно объединить саму с собой (что может быть использовано для фильтрации). Например, чтобы вывести на экран столицы штатов, которые также являются крупнейшими городами, нужно выполнить следующий запрос:

SELECT s.name, s.capitol
FROM states AS s
JOIN states AS ss
ON s.capitol=ss.largest;

Результат:

+---------------+----------------+
|    S.NAME     |   S.CAPITOL    |
+---------------+----------------+
| Arizona       | Phoenix        |
| Arkansas      | Little Rock    |
| Colorado      | Denver         |
| Georgia       | Atlanta        |
| Hawaii        | Honolulu       |
| Idaho         | Boise          |
| Indiana       | Indianapolis   |
| Iowa          | Des Moines     |
| Massachusetts | Boston         |
| Mississippi   | Jackson        |
| Ohio          | Columbus       |
| Oklahoma      | Oklahoma City  |
| Rhode Island  | Providence     |
| Tennessee     | Nashville      |
| Utah          | Salt Lake City |
| West Virginia | Charleston     |
| West Virginia | Charleston     |
| Wyoming       | Cheyenne       |
+---------------+----------------+

CROSS JOIN

В большинстве случаев в Phoenix не применяется CROSS JOIN, так как таблицы обычно очень большие. По этой причине Phoenix игнорирует модификатор CROSS. Тем не менее CROSS JOIN возможен в Phoenix и использует следующий синтаксис:

SELECT <col_1>, <col_2>, ... <col_n>
FROM t1, t2;

GROUP JOIN

Phoenix поддерживает составной синтаксис оператора JOIN. Результат объединения двух таблиц можно сгруппировать с другой таблицей при помощи скобок. Также вместо любой таблицы в выражении JOIN можно использовать подзапрос (производную таблицу), который в свою очередь может содержать собственное выражение JOIN.

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

SELECT p.name, e.name, s.capitol
FROM people AS p
JOIN
    (enterprises AS e
    JOIN states AS s
    ON e.town=s.capitol)
ON p.town=e.town
LIMIT 20;

Результат:

+------------------+--------------+-------------+
|      P.NAME      |    E.NAME    |  S.CAPITOL  |
+------------------+--------------+-------------+
| Abbott Howard    | Shaw Group   | Baton Rouge |
| Alvarado Melvin  | DigitalAnvil | Austin      |
| Baker Daniel     | Shaw Group   | Baton Rouge |
| Barnes Mollie    | Shaw Group   | Baton Rouge |
| Becker Samuel    | Shaw Group   | Baton Rouge |
| Bush Cameron     | Shaw Group   | Baton Rouge |
| Carpenter Carrie | Avnet        | Phoenix     |
| Clarke Joe       | Avnet        | Phoenix     |
| Cox Dennis       | Intel        | Sacramento  |
| Cox Shawn        | Avnet        | Phoenix     |
| Daniel Ada       | DigitalAnvil | Austin      |
| Day Hattie       | Intel        | Sacramento  |
| Erickson Mayme   | Intel        | Sacramento  |
| Evans Sophie     | Avnet        | Phoenix     |
| Farmer Dean      | Shaw Group   | Baton Rouge |
| Fisher Gordon    | Intel        | Sacramento  |
| Fletcher Julia   | Avnet        | Phoenix     |
| Garner Juan      | Shaw Group   | Baton Rouge |
| Gill Josephine   | Shaw Group   | Baton Rouge |
| Gonzales Edna    | Shaw Group   | Baton Rouge |
+------------------+--------------+-------------+

Исключение части INNER из OUTER JOIN

Иногда бывает нужно исключить строки, удовлетворяющие заданному условию из результата JOIN. Для этого в запрос нужно добавить выражение WHERE.

Например, чтобы вывести на экран все предприятия не из США, нужно выполнить следующий запрос:

SELECT e.name, e.country, e.state, e.town
FROM enterprises AS e
LEFT JOIN states AS s
ON e.state=s.abbr
WHERE s.abbr IS NULL;

Результат:

+---------+-----------+---------------+-------------+
| E.NAME  | E.COUNTRY |    E.STATE    |   E.TOWN    |
+---------+-----------+---------------+-------------+
| Alluxi  | Mexico    | Nuevo Leon    | Monterrey   |
| Eidos   | UK        | England       | London      |
| Empresa | Mexico    | Sonora        | Nogales     |
| Ubisoft | France    | Ile-de-France | Saint-Mande |
+---------+-----------+---------------+-------------+

Использование индексов

Можно создать вторичные индексы для таблиц, которые будут автоматически использоваться для запросов с оператором JOIN. Пример:

CREATE INDEX ipeople ON people (town) INCLUDE (state);
CREATE INDEX istates ON states (capitol) INCLUDE (name, abbr);
CREATE INDEX ienterprises ON enterprises (town) INCLUDE (name, state);

Эти индексы будут иметь приоритет при выполнении определенных запросов. Пример:

EXPLAIN SELECT p.name, e.name, s.capitol
FROM people AS p
JOIN
    (enterprises AS e
    JOIN states AS s
    ON e.town=s.capitol)
ON p.town=e.town;

Результат:

+----------------------------------------------------------------------------------+----------------+---------------+-------------+
|                                       PLAN                                       | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+----------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER IPEOPLE                 | null           | null          | null        |
|     SERVER FILTER BY FIRST KEY ONLY                                              | null           | null          | null        |
|     PARALLEL INNER-JOIN TABLE 0                                                  | null           | null          | null        |
|         CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER IENTERPRISES    | null           | null          | null        |
|             SERVER FILTER BY FIRST KEY ONLY                                      | null           | null          | null        |
|             PARALLEL INNER-JOIN TABLE 0                                          | null           | null          | null        |
|                 CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ISTATES | null           | null          | null        |
|                     SERVER FILTER BY FIRST KEY ONLY                              | null           | null          | null        |
|     DYNAMIC SERVER FILTER BY "P.0:TOWN" IN ("E.LOCATION:TOWN")                   | null           | null          | null        |
+----------------------------------------------------------------------------------+----------------+---------------+-------------+

Как видно, для данного запроса Phoenix использует все три ранее созданных индекса вместо оригинальных таблиц.

Hints

Phoenix использует указания (hints) для переключения между различными способами работы JOIN.

Алгоритмы hash и sort-merge

Phoenix использует два типа алгоритмов JOIN: hash и sort-merge. Hash — самый быстрый алгоритм, поэтому он используется по умолчанию. Однако если общий размер таблиц превосходит размер доступной памяти, этот алгоритм не может быть использован. В этом случае следует использовать алгоритм sort-merge с помощью указания /*+ USE_SORT_MERGE_JOIN */ в запросе:

SELECT /*+ USE_SORT_MERGE_JOIN */ <values>
FROM <table>
JOIN <clause_condition>

Оптимизация star join

Phoenix использует оптимизацию star join для запросов, содержащих несколько выражений JOIN. Например, запрос имеет следующую структуру:

SELECT p.name, s.abbr, e.name
FROM people AS p
JOIN states AS s
ON p.state=s.abbr
JOIN enterprises AS e
ON p.state=e.state;

При обработке такого запроса Phoenix загрузит все указанные таблицы в оперативную память и будет выполнять оба выражения JOIN одновременно. Это можно увидеть в плане запроса, который можно получить с помощью оператора EXPLAIN:

EXPLAIN SELECT p.name, s.abbr, e.name
FROM people AS p
JOIN states AS s
ON p.state=s.abbr
JOIN enterprises AS e
ON p.state=e.state;

Результат:

+----------------------------------------------------------------------------------------------------------+----------------+---------------+--------+
|                                                   PLAN                                                   | EST_BYTES_READ | EST_ROWS_READ | EST_IN |
+----------------------------------------------------------------------------------------------------------+----------------+---------------+--------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER PEOPLE                                          | null           | null          | null   |
|     PARALLEL INNER-JOIN TABLE 0                                                                          | null           | null          | null   |
|         CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER STATES                                  | null           | null          | null   |
|     PARALLEL INNER-JOIN TABLE 1                                                                          | null           | null          | null   |
|         CLIENT 1-CHUNK 976934 ROWS 314572800 BYTES PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ENTERPRISES | null           | null          | null   |
+----------------------------------------------------------------------------------------------------------+----------------+---------------+--------+

Иногда таблицы могут быть слишком велики для того, чтобы поместиться в оперативной памяти все вместе. В таких случаях нужно отключить оптимизацию star join с помощью указания /+ NO_STAR_JOIN/ в запросе:

EXPLAIN SELECT /*+ NO_STAR_JOIN*/ p.name, s.abbr, e.name
FROM people AS p
JOIN states AS s
ON p.state=s.abbr
JOIN enterprises AS e
ON p.state=e.state;

Результат:

+--------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
|                                               PLAN                                               | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+--------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK 976934 ROWS 314572800 BYTES PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ENTERPRISES | null           | null          | null        |
|     PARALLEL INNER-JOIN TABLE 0                                                                  | null           | null          | null        |
|         CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER PEOPLE                          | null           | null          | null        |
|             PARALLEL INNER-JOIN TABLE 0                                                          | null           | null          | null        |
|                 CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER STATES                  | null           | null          | null        |
+--------------------------------------------------------------------------------------------------+----------------+---------------+-------------+

Как видно, в этом случае операции JOIN выполняются по очереди.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней