Левое внешнее соединение на двух столбцах проблема производительности
Я использую 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: Это на самом деле взгляды, к которым я присоединяюсь. К сожалению, они находятся в базе данных, которую я не могу контролировать, поэтому я не могу (легко) вносить какие-либо изменения в индексацию. Хотя я склонен согласиться, что это вопрос индексации. Я подожду немного, прежде чем принять ответ на случай, если есть какой-то волшебный способ настроить этот запрос, о котором я не знаю. В противном случае я приму один из текущих ответов и попытаюсь найти другой способ сделать то, что я хочу сделать. Спасибо всем за помощь так что далеко.
8 ответов:
Имейте в виду, что утверждения 2 и 3 отличаются от первого.
Как? Ну, вы делаете левое внешнее соединение, и Ваше предложение WHERE не принимает это во внимание (как и предложение ON). Как минимум, попробуйте:
SELECT col1, col2 FROM table1, table2 WHERE table1.person_uid = table2.person_uid (+) AND table1.period = table2.period (+)
И посмотрите, есть ли у вас такая же проблема с производительностью.
Какие индексы имеются в этих таблицах? Определяется ли эта связь ограничением внешнего ключа?
, Что вы, вероятно, нужен составной индекс на оба person_uid и время (по обе таблицы).
Я думаю, вам нужно понять, почему последние два запроса не совпадают с первым. Если вы выполняете левое соединение, а затем добавляете предложение 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.
Если ваш запрос недостаточно быстр, я бы посмотрел на вашу индексацию.
Все, что кто-либо говорит вам на основе предоставленной Вами информации, является предположением.
Посмотрите на план выполнения запроса. Если вы не видите причины для медлительности в плане, опубликуйте план здесь.
Http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009
Есть ли у вас покрывающие индексы на
Если нет, добавьте их и повторите попытку.person_uid
иperiod
для обеих таблиц?Взгляните на план выполнения и посмотрите, что на самом деле делает запрос.
Также: каковы типы данных полей? Являются ли они одинаковыми в обеих таблицах? Неявный бросок может действительно замедлить ход событий.
Есть ли в этих таблицах индексы для столбцов, к которым вы присоединяетесь? Установите бесплатный продукт Oracle SQLDeveloper и используйте его для выполнения "объяснения" этого запроса и посмотрите, выполняет ли он последовательное сканирование обеих таблиц.
В левом соединении вы будете сканировать таблицу 1 для каждой уникальной комбинации (person_uid, period), а затем искать таблицу 2 для всех соответствующих записей там. Если таблица 2 не имеет соответствующего индекса, это может включать в себя сканирование всей этой таблицы тоже.
Мое лучшее предположение, не видя плана выполнения, состоит в том, что первый запрос (единственный, который кажется правильным) должен table scan table2, а также table1.
Поскольку вы говорите, что не можете изменить индексы, вам нужно чтобы изменить запрос. Насколько я могу судить, существует только одна реальная альтернатива...
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. (В отличие от сканирования table1 и использования индексов на table2, чего я и ожидал от вашего запроса.)
Однако, если таблица 1 не имеет соответствующих индексов, вы вряд ли заметите какое-либо улучшение производительности...
Dems.
В одном из обновлений ОП заявляет, что он фактически запрашивает представления, а не таблицы. В этом случае производительность вполне может быть повышена путем прямого запроса таблиц, которые ему нужны, особенно если представления являются сложными и присоединяются ко многим другим таблицам, которые не содержат нужной ему информации или являются представлениями, вызывающими представления.
Синтаксис соединения ANSI обеспечивает очень четкое различие между условиями соединения и предикатами фильтра; это очень важно при написании внешних соединений. Используя таблицы emp/dept, посмотрите на результаты следующих двух внешних соединений
Q1
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 outer join, вероятно, написали бы запрос как
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.