Oracle. Подзапросы в IN() | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Всем привет.
Делаю выборку с параметрами:
Конструкция "branch_id in(select branch_id from branches)" отрабатывает успешно независимо от того, возвращается 1 или несколько записей, но оптимизатор рисует страшные числа в плане выполнения. Меняю условие фильтра на
Пробую сформировать список id в одну строку:
|
Re: Oracle. Подзапросы в IN() | |
---|---|
PaulWist Сообщений: 14601 Дата регистрации: 01.04.2004 |
Перепиши:
------------------ Есть многое на свете, друг Горацио... Что и не снилось нашим мудрецам. (В.Шекспир Гамлет) |
Re: Oracle. Подзапросы в IN() | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Один из первых испробованных вариантов. План выполнения точно такой же как в 1 варианте стартового поста. Оптимизатор неадекватно реагирует на "or :p_branch_id is null" в каком бы месте это условие ни стояло.
На всякий случай поясню, что ситуация упрощена. Даже если не принимать в расчет выборки в with, в финальном селекте 18 джойнов, 7 коррелирующих подзапросов, примерно столько же вызовов функций во внешнем пакете. Исправлено 1 раз(а). Последнее : Pekpytep, 16.01.18 20:41 |
Re: Oracle. Подзапросы в IN() | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Да, в запросах где есть параметр "отключающий часть логики отбора" оптимизатор не справляется. По возможности стоит более простые запросы писать - т.е. один С условием (по идее там особо без разницы IN или JOIN это условие "применяет" и другой БЕЗ условия.
Во втором случае получаем поле IN ('1,2,3') что, естественно, совершенно не одно и то же что и поле IN (1,2,3). Не ругается т.к. весьма мягок в части автоматического приведения типов, но логика то совершенно не та будет. Это типичная ошибка когда пытаются "параметризовать" IN - думают что если передать строку с циферками через запятую, то сервер волшебным образом станет понимать это как НАБОР значений. Но это, конечно же, совсем не так. P.S. Тут Том показывает технику "динамических" запросов, но при том вполне себе параметризованных (т.е. безопасных с точки зрения той же SQL-инъекции) asktom.oracle.com Возможно это получится и для твоей задачи применить (если ref cursor, процедуры и контекст не "запрещены как класс" ) вместо контекста в принципе можно и временную таблицу использовать - с нужными полями-параметрами, но тогда, вероятно, потребуются хинты в запросе показывающие что в этой таблице ровно 1 запись находится. ------------------ WBR, Igor Исправлено 1 раз(а). Последнее : Igor Korolyov, 16.01.18 22:35 |
Re: Oracle. Подзапросы в IN() | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Не совсем понимаю. Это что-то типа
Ок. В примере всего 2 параметра, но что если у меня десяток различных параметров? |
Re: Oracle. Подзапросы в IN() | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Нет, без union - исполнять либо один запрос либо другой. Динамика, хотя и ограниченная. Я PS выше добавил
------------------ WBR, Igor |
Re: Oracle. Подзапросы в IN() | |
---|---|
pasha_usue Сообщений: 3647 Откуда: Е-бург Дата регистрации: 06.10.2006 |
Я бы поменял местами аргументы у OR. Нет, в компилированный запрос условие всё-равно будет включено, так как там параметр. Но на этапе исполнения конструкция выигрывает обычно:
|
Re: Oracle. Подзапросы в IN() | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Лежит на поверхности. Тоже один из первых попробованных вариантов. Как выше говорил, План запроса аналогичен. Посмотрел. Очень "так себе" вариант. Любая доработка превращается в "танцы с бубном в гамаке на лыжах вверх ногами". Сильно осложняется сопровождение даже для меня, а у нас, между прочим, в штате есть вчерашние студенты с отсутствием опыта на позициях младших разработчиков. В общем, пришлось привлекать ведущего с опытом работы на этом проекте. Вынес обработку параметра в with, в branches выбираю либо значения из параметра, либо целиком все дерево филиалов. В финальном селекте делаю inner join договоров с филиалами. Также хинтами пришлось подшаманить план выполнения, что позволило partition hash all+range scan по дате в contracts заменить на partition hash iterator+range scan по (contract_date, branch_id). Что-то типа
Исправлено 3 раз(а). Последнее : Pekpytep, 17.01.18 13:17 |
Re: Oracle. Подзапросы в IN() | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Если таблица-фильтр не очень большая то в принципе, конечно, можно и так решать - т.е. вместо "отключения" условия "наличие в списке" просто подсовывать в такой список ВСЕ записи. Естественно это не будет оптимальным (т.к. и "ненужный" запрос и "ненужная" проверка никуда не денутся), но если никак не получается сделать таки два (или более) разных запроса - динамикой, либо тривиальным дублированием его "основного" текста с соответствующими изменениями - ну что ж, придётся терпеть неоптимальность... Часто упрощение сопровождения связано с ухудшением скорости работы и наоборот...
------------------ WBR, Igor |
Re: Oracle. Подзапросы в IN() | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Ораклу по барабану это (и это совершенно логично - логика запроса НЕ должна зависеть от порядка следования условий). Условие с OR в лучшем случае может быть преобразовано в UNION-ALL (точнее CONCATENATION, но по сути это один чёрт) двух частей (каждая из которых более-менее оптимальна по своей сути). Естественно что когда "по логике" нужна всегда только одна часть, это будет неоптимально. Ну, точнее, для варианта "выбрать всё" оно и так и так плохо будет, а вот для варианта "взять кусочек", потери от того что берём таки всё и всегда будут весьма значительны. Иногда произвести "разложение на части" не получается, и тогда может происходить всякий ужас - включая картезианское произведение "соединяемых" таким "особо хитрым способом" таблиц, с последующим фильтром. ------------------ WBR, Igor |
Re: Oracle. Подзапросы в IN() | |
---|---|
Гулин Федор Сообщений: 4633 Откуда: Минск Дата регистрации: 24.10.2002 |
в финальном селекте 18 джойнов, 7 коррелирующих подзапросов, примерно столько же вызовов функций во внешнем пакете.
давно с ораклом не работаю с 2014 - счас с MS-SQL (чуть MySQL) и что там гут что вот такой мегазапрос можно разбить на кучу запросов с TEmp таблицами создаваемых налету если что вылезет так даже ошибку найти проще когда у меня был оракл. запрос к-й тоже стал выходить за рамки читаемости я тоже вводил Temp таблицу там - но ее надо было создавать заранее |
Re: Oracle. Подзапросы в IN() | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Временная таблица может быть лишней сущностью для реальной работы (по сути это создание "почти настоящих" объектов-таблиц). В принципе оракл тоже может создать временную таблицу a-la MSSQL (т.е. "автоматически", без предварительного её описания в словаре данных) - называется это "материализация подзапроса" (тоже живёт во временном сегменте временного табличного пространства). Обычно оптимизатор сам решает, нужно ли это делать, но можно хинтом подсказать (для факторизованного запроса - т.е. того самого WITH ...). Разница в том, что область видимости такой временной таблицы всё равно ограничена телом этого запроса. Т.е. ни в отладке нельзя "по шагам" пройти и посмотреть чего же в такой временной таблице на каждом шаге будет, ни хитрые стратегии "совместного использования" данных такой временной таблицы не проходят (типа сделать 1 запрос во временную таблицу, а потом несколько разных запросов с её участием). Для таких вариантов приходится именно GLOBAL TEMPORARY TABLE применять - т.е. статически описывать структуру таблицы, и определять "время жизни" записей в ней - до первого коммита, или до конца сессии... Насколько это хуже/лучше "автосоздаваемой" временной таблицы - вопрос дискуссионный. Есть и плюсы и минусы.
В том же фоксе можно нагромоздить один мега-запрос, а можно разбить его на кучу "шагов" - но если оптимизатор не "тупит", то обычно один мега-запрос всё-же выигрывает у кучи отдельных (в "реальной работе", не в понимании логики и отладке). В фоксе оптимизатор очень простой, потому и "тупит" часто. В оракле и MSSQL гораздо более навороченный - потому в бОльшем числе случаев запрос-монстр всё же будет оптимальнее "разбитого на шаги". Особенно если разработчик ленится, и оставляет в промежуточных шагах кучу инфы полезной для отладки, но не нужной для работы (скажем нужно выбрать ID и SomeValue, но для отладки полезно ещё пару полей видеть - Name, CreationDate к примеру - и их зачастую "оставляют" в подзапросе-шаге и на продуктиве). ------------------ WBR, Igor |
Re: Oracle. Подзапросы в IN() | |
---|---|
Гулин Федор Сообщений: 4633 Откуда: Минск Дата регистрации: 24.10.2002 |
ключевой момент именно отладка и поиск багов особенно в чужом коде
здесь разделение по шагам сильно выигрывает называется это "материализация подзапроса" (тоже живёт во временном сегменте временного табличного пространства). Обычно оптимизатор сам решает, нужно ли это делать, но можно хинтом подсказать (для факторизованного запроса - т.е. того самого WITH ...). а что за хинт ? ps кол-во хинтов в оракле на порядок больше чем в мс-скл |
Re: Oracle. Подзапросы в IN() | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Ну в принципе порезать 17-этажный мега-запрос на части вполне себе можно. Да, иногда возникают "нюансы", и почти всегда возникает вопрос производительности. Даже банальное убирание/добавление "да почти ничего не значащего" условия и то может кардинально поменять весь план исполнения, и соответственно скорость, не говоря уж о делении сложного запроса на части. Вся суть именно в том, что "простой для отладки" запрос в 99% случаев является одним из самых плохих для работы (по скорости). Да даже для обычного процедурного кода это часто будет справедливо... Тут важно соблюсти баланс - с одной стороны не скатиться в абсолютно непостижимую мега-простыню кода, а с другой не получить "понятный даже ребёнку", но работающий часами код.
------------------ WBR, Igor |
Re: Oracle. Подзапросы в IN() | |
---|---|
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010 |
Самое для меня печальное, что нет вменяемых мануалов с действенными рецептами оптимизации запросов. Я имею в виду, что книжек по теории конечно более чем достаточно, но оптимизатор - это "черный ящик" и знание устройства b-tree индекса никак не поможет мне понять, почему тупит запрос и как это побороть. Уже который раз спотыкаюсь на том, что думаю о возможностях ораклового оптимизатора лучше, чем он есть на самом деле. Каждый запрос приходится методом научного тыка делать в нескольких десятках вариантов, смотреть и сравнивать планы выполнения. Приходится идти по полю, густо усеянному граблями. Странно, но я заметил, что у запросов с соединениями типа
Так же часто подзапрос в where работает быстрее join-а
Создается впечатление, что старое "ядро" было написано вдумчиво и тщательно вылизано, новый функционал же штампуется индусами в угоду скорости разработки и в ущерб качеству. Исправлено 1 раз(а). Последнее : Pekpytep, 29.01.18 10:31 |
Re: Oracle. Подзапросы в IN() | |
---|---|
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002 |
Отчего же? В принципе посмотрев на РЕАЛЬНЫЙ план исполнения (не тот который по EXPLAIN PLAN, а тот который из V$SQL_PLAN - для реально отработавших запросов) можно понять что именно было плохо (конечно если не смотреть, как многие новички, на cost ). К сожалению это не даёт прямых наводок на то как же его нужно "подкручивать". Собственно говоря, многие сторонние программы - "тюнеры запросов" поступают до безобразия тупо - по некоторым, весьма несложным правилам корябают исходный запрос (ну "типичные" довески +0 к числовым полям в предикатах/условиях, несложные перестановки и т.п.), и просто смотрят какой из вариантов оказывается лучше (обычно просто "быстрее"). Встроенные средства оптимизации (не сам оптимизатор, а SQL Tuning Advisor) уже чуть по иному работают (по крайней мере автосоздаваемые "профили" для запросов это НЕ изменение текста запроса, а только сохранение наилучшего плана - чтобы оптимизатор в следующий раз именно его выбрал, несмотря на то что по его "быстрым" прикидкам этот план не самый лучший) - но тоже до полного "автоматизма" тут очень далеко. Это да Хочется на него переложить максимум работы, но увы - далеко ему ещё "по уму" до даже очень среднего DBA. Тем более что некоторые задачи вообще выходят за рамки "компетенции" оптимизатора - например изменение структуры БД в неудачных местах (в т.ч. намеренная денормализация), или учёт в логике запроса непрописанных явно в структуре БД бизнес-ограничений. Да, есть такое парадоксальное поведение... К сожалению лично я не могу нормально пользоваться этими старинными (+). Ну совсем неочевиден в этих случаях для меня запрос... И с этим трудно спорить... Число багов колоссально - многие тянутся из версии в версию. Очень быстро начинаешь понимать, что для более-менее продуктивной работы нужна активная подписка на саппорт и не менее активное пользование этим самым саппортом. ------------------ WBR, Igor |
© 2000-2024 Fox Club  |