SQL: Oracle: оптимизировать запрос левого внешнего соединения
У меня есть 1 локальная таблица (все имена столбцов отличаются от удаленных таблиц, кроме одной) и 2 удаленные таблицы (с одинаковыми именами столбцов), для которых мне нужно объединить данные.
Ниже приведен запрос, который я написал с использованием LEFT OUTER JOIN и UNION, но производительность низкая.
Может ли кто-нибудь помочь оптимизировать этот запрос?
Основная проблема, которую я вижу в вашем запросе, — это крайнее левое соединение между CTMAGENTAUDIT и подзапросом, который содержит объединение. Проблема с этим подзапросом в том, что, как написано, Oracle не может использовать какой-либо индекс для соединения. Это означает, что Oracle, вероятно, придется прибегнуть к более медленному методу при присоединении, возможно, к полному сканированию.
Один из подходов здесь — создать материализованное представление, содержащее запрос на объединение, а затем проиндексировать его:
С этим индексированным материализованным представлением я ожидаю, что ваш запрос теперь будет работать намного лучше:
Оптимизация вывода Oracle JOIN
Используя приведенный ниже запрос, я получаю некоторые детали, разбросанные по трем различным таблицам конкретной схемы базы данных оракула.
Текущий выход:
Здесь таблица USERDB содержит четыре столбца USERID, USERNAME, FULLNAME, DEPARTMENT. Таблица USERDB_TASKS содержит два столбца USERID, TASKSID. Таблица TASKS содержит два столбца TASKSID, TASKNAME.
Для конкретного пользователя USERID будет одинаковым во всех таблицах. Точно так же TASKSID для конкретной задачи будет одинаковым во всех таблицах.
Но на самом деле в таблице USERDB есть несколько идентификаторов USERID, которых нет в таблице USERDB_TASKS, т. е. у нескольких пользователей нет назначенных задач, поэтому очевидно, что их идентификаторы отсутствуют в таблице USERDB_TASKS.
Используя приведенный выше запрос, я получаю информацию только о тех пользователях, чей USERID присутствует как в таблицах USERDB, так и в таблицах USERDB_TASKS. Мой вопрос заключается в том, как я могу изменить свой запрос таким образом, чтобы для всех тех немногих USERID, которые присутствуют только в таблице USERDB, а не в таблице USERDB_TASKS, значение для столбца TASKNAME должно быть NONE, например, как показано ниже.
Повышение производительности: очень медленное соединение Oracle SQL
Я новичок в SQL-запросах, и я трачу 3 часа, чтобы получить полный результат объединения двух запросов. Я сосредоточился на использовании левых соединений и избегал использования подзапросов в операторе select после исследования. Однако это все еще очень медленно. У меня нет близких друзей, которые знают sql достаточно, чтобы объяснить, что не так или что мне следует предпринять. Я тоже здесь новичок, поэтому, если этот вопрос не разрешен, сообщите мне, и я немедленно удалю его.
Это структура запроса . Первый запрос получит детали участника. Второй запрос получит детали транзакции. Отношения таковы, что у одного продукта есть много подпланов, у которых много участников. Один продукт также имеет много транзакций, которые выполняются для каждого продукта. От меня требуется показать все транзакции и продублировать каждую строку для каждого участника. Я присоединился к запросам, используя первичный ключ продукта. Перед тем как присоединиться, я протестировал оба отдельных запроса, и они оказались в порядке. Всего 1-2 секунды и я получаю результат. Но присоединяясь к этим двум, я получаю 3 часа ожидания.
4 ответа
Используйте ROWNUM , чтобы преобразования оптимизатора не снижали производительность.
Вы сталкиваетесь с общей проблемой — два запроса выполняются быстро по отдельности, но медленно, когда их объединяют. Oracle не обязательно выполнять запросы в том порядке, в котором они написаны. Он может объединять представления, распространять предикаты и, как правило, полностью переписывать запрос для выполнения в другом порядке. Обычно это замечательно, потому что вам не нужно беспокоиться о том, в каком физическом порядке соединять таблицы. Но иногда Oracle применяет неправильные преобразования, и результаты плачевны.
Есть два способа решить эти проблемы.
- Посмотрите на структуры таблиц, операторы, планы выполнения, мониторинг или трассировку SQL, статистику и т. Д. Попытайтесь выяснить, какая операция медленная и почему (используйте количество элементов в качестве руководства), а затем попытайтесь исправить это. Этот процесс может занять часы, может быть, даже дни, но это лучший способ учиться.
- Остановите оптимизатор от объединения запросов с помощью простого трюка. Есть несколько способов сделать это, но, по моему опыту, самый простой способ — добавить псевдостолбец ROWNUM в любое встроенное представление, которое вы не хотите преобразовывать. ROWNUM — это специальный столбец, который сообщает Oracle, что «этот блок запроса должен быть возвращен определенным образом, не делайте с ним ничего».
Я считаю, что в вашем коде два встроенных представления, которые нужно изменить, — это самые внешние MPP и FF .
Кстати, я не согласен с некоторыми другими комментариями и ответами.
- CTE здесь не поможет, поскольку ни одна из таблиц не используется дважды.
- Вам не всегда нужно знать миллион деталей о запросе, чтобы настроить его. Если у вас нет времени и вы хотите улучшить свои навыки.
- Я считаю, что ваша общая структура запроса хороша. Вы находитесь на правильном пути к созданию отличных операторов SQL. Встроенные представления являются ключом к написанию SQL — создавайте небольшие блоки кода, объединяйте их в простые шаги и повторяйте. Объединение всех таблиц в одно массивное соединение — это рецепт спагетти-кода. Хотя я согласен с другими, что вам следует избегать старомодного синтаксиса соединения. И запрос действительно выиграет от некоторых комментариев и более значимых имен. И не бойтесь поместить все элементы списка выбора в одну строку. Линия из 500 столбцов не идеальна, но вы хотите сосредоточиться на объединениях, а не на простом списке столбцов.
Ваш запрос почти не читается из-за всей вложенности. И вы смешиваете соединения в стиле до 1992 года с текущим синтаксисом соединения. Не используйте устаревший синтаксис соединения, разделенный запятыми. Это склонно к ошибкам. Все ваши внешние соединения недействительны, потому что в какой-то момент у вас всегда будет критерий, который отклоняет записи с внешними соединениями, например, при внутреннем соединении table8 с элементомmb_dx таблицы с внешним соединением4.
Ваш запрос, кажется, переводится на
И может ты хочешь, чтобы это было
Вместо этого или что-то в этом роде. Избавьтесь от всех вложений и оставайтесь с ясным и легко читаемым предложением.
Одна вещь, о которой не упоминали другие, — это использование
Это применяет функцию EXTRACT к каждой строке, выбранной из TABLE3 (я считаю, что это то, что FF относится к этой точке запроса). Я предлагаю заменить приведенное выше на
Или что-то подобное. Для этого требуется только один вызов TO_DATE для создания константы даты, которая затем напрямую сравнивается с FF.EFF_BFX, который выглядит как столбец типа DATE.
Этот запрос также использует один и тот же псевдоним таблицы (например, FF , MPP и т. Д.) Несколько раз для разных сущностей в разных контекстах. На мой взгляд, это плохая практика, и я предлагаю вам переработать свой запрос, чтобы использовать уникальный псевдоним для каждой сущности, что упростит понимание запроса.
Как отмечали другие, избавление от объединений до 1992 года в предложении WHERE также помогло бы прояснить, что происходит, равно как и избавление от длинных списков столбцов. Также можно исключить пару подзапросов, что сделало бы запрос чище и понятнее.
Разобравшись со всем вышесказанным, я получаю следующее:
Я постарался сделать ваш запрос более читабельным:
Вы выбираете 8 разных таблиц, и единственное условие WHERE — EXTRACT( YEAR FROM FF.EFF_BFX) >= 2013
Если только таблицы не крошечные, то всегда потребуется время, чтобы запросить их все вместе.
Почему вы смешиваете синтаксис соединения ANSI и синтаксис соединения Oracle старого стиля?