Левое внешнее соединение для двух столбцов

Я использую SQL-запрос, похожий на следующую форму:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
AND table1.period = table2.period

И это либо слишком медленно, либо что-то заторможенное, потому что для возвращения требуется не менее 4 минут. Если бы я изменил его на это:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
WHERE table1.period = table2.period

тогда он отлично работает (хотя и не возвращает правильное количество столбцов). Есть ли способ ускорить это?

UPDATE: он делает то же самое, если я переключу последние две строки последнего запроса:

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.period = table2.period
WHERE table1.person_uid = table2.person_uid

ОБНОВЛЕНИЕ 2: На самом деле это те представления, к которым я присоединяюсь. К сожалению, они находятся в базе данных, в которой я не контролирую, поэтому я не могу (легко) внести какие-либо изменения в индексирование. Я склонен согласиться с тем, что это проблема индексации. Я подожду немного, прежде чем принимать ответ, если есть какой-то волшебный способ настроить этот запрос, о котором я не знаю. В противном случае я приму один из текущих ответов и попытаюсь найти другой способ сделать то, что я хочу сделать. Спасибо всем за помощь до сих пор.

Ответ 1

Имейте в виду, что утверждения 2 и 3 отличаются от первого.

Как? Ну, вы делаете левое внешнее соединение, и ваше предложение WHERE не учитывает это (например, предложение ON). Как минимум, попробуйте:

SELECT col1, col2
FROM table1, table2
WHERE table1.person_uid = table2.person_uid (+)
AND table1.period = table2.period (+)

и посмотрите, не получите ли вы такую же производительность.

Какие индексы у вас есть в этих таблицах? Является ли это отношение определяемым с помощью ограничения внешнего ключа?

Возможно, вам нужен составной индекс как для person_uid, так и для периода (в обеих таблицах).

Ответ 2

Я думаю, вам нужно понять, почему последние два не являются тем же самым запросом, что и первый. Если вы выполните левое соединение, а затем добавьте предложение where, ссылающееся на поле в таблице справа от соединения (то, которое может не всегда содержать запись, соответствующую первой таблице), вы фактически изменили соединение на внутреннее соединение. Есть одно исключение из этого, и если вы ссылаетесь на что-то вроде

SELECT col1, col2
FROM table1
LEFT OUTER JOIN table2
ON table1.person_uid = table2.person_uid
WHERE table2.person_uid is null

В этом случае вы запрашиваете запись, которая не имеет записи во второй таблице. Но кроме этого особого случая вы меняете левое соединение на внутреннее соединение, если вы переопределяете поле в таблице2 в предложении where.

Если ваш запрос не достаточно быстрый, я бы посмотрел на вашу индексацию.

Ответ 3

Все, что кто-либо говорит вам, основываясь на предоставленной вами информации, - это предположение.

Посмотрите план выполнения запроса. Если вы не видите причины медленности в плане, разместите здесь план.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

Ответ 4

У вас есть индексы покрытия на person_uid и period для обеих таблиц?

Если нет, добавьте их и повторите попытку.

Взгляните на план выполнения и посмотрите, что на самом деле делает запрос.

Также: Каковы типы данных полей? Они одинаковы в обеих таблицах? Неявный бросок может действительно замедлить работу.

Ответ 5

Имеются ли в этих таблицах индексы для столбцов, к которым вы присоединяетесь? Установите Oracle бесплатно продукт SQLDeveloper и используйте его для "объяснения" этого запроса и посмотрите, выполняет ли он последовательное сканирование обеих таблиц.

Ответ 6

В левом соединении вы будете сканировать таблицу1 для каждой уникальной комбинации (person_uid, period), а затем искать таблицу2 для всех соответствующих записей. Если таблица2 не имеет соответствующего индекса, это может включать также сканирование всей этой таблицы.

Мое лучшее предположение, не видя плана выполнения, заключается в том, что первый запрос (единственный, который кажется правильным) имеет таблицу table table table table table table table table table table table table table table, а также таблицу.

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

SELECT
   col1, col2
FROM
   table2
FULL OUTER JOIN
   table1
      ON table1.person_uid = table2.person_uid
      AND table1.period = table2.period
WHERE
   table1.person_uid IS NOT NULL

Надеемся, что вы сканируете таблицу2 для каждой уникальной комбинации (person_uid, period), но используйте индексы в таблице1. (В отличие от таблицы сканирования1 и использования индексов на таблице2, чего я ожидал от вашего запроса.)

Если таблица1 не имеет соответствующих индексов, однако, вы вряд ли увидите какое-либо улучшение производительности...

Демс.

Ответ 7

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

Ответ 8

Синтаксис соединения ANSI обеспечивает очень четкое различие между условиями JOIN и предикатами FILTER; это очень важно при написании внешних объединений. Используя таблицы emp/dept, просмотрите результаты из следующих двух внешних соединений

Q 1

SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
LEFT OUTER JOIN emp e
on  d.deptno = e.deptno
and loc in ('NEW YORK','BOSTON' )
;

DNAME              DEPTNO ENAME             MGR LOC
-------------- ---------- ---------- ---------- -------------
ACCOUNTING             10 CLARK            7839 NEW YORK
ACCOUNTING             10 KING                  NEW YORK
ACCOUNTING             10 MILLER           7782 NEW YORK
RESEARCH               20                       DALLAS
SALES                  30                       CHICAGO
OPERATIONS             40                       BOSTON

====

Q2
SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
LEFT OUTER JOIN emp e
on  d.deptno = e.deptno
where loc in ('NEW YORK','BOSTON' )
;

DNAME              DEPTNO ENAME             MGR LOC
-------------- ---------- ---------- ---------- -------------
ACCOUNTING             10 CLARK            7839 NEW YORK
ACCOUNTING             10 KING                  NEW YORK
ACCOUNTING             10 MILLER           7782 NEW YORK
OPERATIONS             40                       BOSTON

Первый пример: Q1 - пример "присоединения к константе". По сути, условие фильтра применяется до выполнения внешнего соединения. Таким образом, вы удаляете строки, которые впоследствии добавляются как часть внешнего соединения. Это не обязательно неправильно, но это тот запрос, который вы действительно просили? Часто требуются результаты, показанные в Q2, где фильтр применяется после (внешнего) соединения.

Кроме того, для больших наборов данных также подразумевается производительность. Во многих случаях объединение в константу должно быть разрешено внутренне оптимизатором, создавая боковое представление, которое обычно можно оптимизировать только через вложенное соединение цикла, а не с хеш-соединением

Для разработчиков, знакомых с синтаксисом внешнего соединения Oracle, запрос, вероятно, был бы записан как

SELECT dname, d.deptno, e.ename, e.mgr, d.loc
FROM dept d
        ,emp e
where  d.deptno = e.deptno(+)
and loc in ('NEW YORK','BOSTON' )

Этот запрос семантически эквивалентен Q2 выше.

Итак, очень важно, чтобы вы понимали разницу между предложением JOIN и предложением WHERE при написании внешних соединений ANSI.