for flooders
:: Главная :: Решения :: Статьи :: Сайт М. Дроздова :: Файловый архив :: Книга по VFP 9 :: Русский Help Online :: OFF-LINE Форум
   Л и с о в о д ы   в с е х   с т р а н,  о б ъ е д и н я й т е с ь !!!  

Список Форумов  :: Не фоксом единым
   :: Помощь сайту :: 

Oracle. Подзапросы в IN()
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 16.01.18 16:47:07ОтветитьЦитировать
Всем привет.

Делаю выборку с параметрами:
  
  with  
    branches as  
    (  
      select ...  
      from ...  
    )  
    select  
      contract_id  
    from contracts cn  
    where cn.contract_date between :p_startdate and :p_enddate  
      and (cn.branch_id in(select branch_id from branches) or :p_branch_id is null);

Конструкция "branch_id in(select branch_id from branches)" отрабатывает успешно независимо от того, возвращается 1 или несколько записей, но оптимизатор рисует страшные числа в плане выполнения.
Меняю условие фильтра на
  
    where cn.contract_date between :p_startdate and :p_enddate  
      and cn.branch_id in(case   
                            when :p_branch_id is null   
                              then cn.branch_id  
                            else (select branch_id from branches)  
                          end);
Стоимость в плане выполнения уменьшается более, чем в 4 раза, запрос "летает", но только до тех пор, пока в branches одна запись. Как только там становится записей более одной - вылетает в ORA-01427: подзапрос одиночной строки возвращает более одной строки. Но он же только что работал через "branch_id in(select branch_id from branches)" !

Пробую сформировать список id в одну строку:
  
    where cn.contract_date between :p_startdate and :p_enddate  
      and cn.branch_id in(case   
                            when :p_branch_id is null   
                              then cn.branch_id  
                            else (select distinct   
                                    listagg(branch_id, ', ') within group(order by branch_id)  
                                  from branches)  
                          end);
В исключения не вылетает, но и ничего не находит. WTF ? Что я делаю не так?
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
PaulWist

Сообщений: 12947
Дата: 16.01.18 18:15:36ОтветитьЦитировать
Перепиши:
with    
      branches as    
      (    
        select ...    
        from ...    
      )    
      select    
        contract_id    
      from contracts cn  
      inner join branches on cn.branch_id = branches.branch_id or :p_branch_id is null    
      where cn.contract_date between :p_startdate and :p_enddate


------------------
Есть многое на свете, друг Горацио...
Что и не снилось нашим мудрецам.
(В.Шекспир Гамлет)
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 16.01.18 19:36:08ОтветитьЦитировать
Один из первых испробованных вариантов. План выполнения точно такой же как в 1 варианте стартового поста. Оптимизатор неадекватно реагирует на "or :p_branch_id is null" в каком бы месте это условие ни стояло.

На всякий случай поясню, что ситуация упрощена. Даже если не принимать в расчет выборки в with, в финальном селекте 18 джойнов, 7 коррелирующих подзапросов, примерно столько же вызовов функций во внешнем пакете.



Исправлено: Pekpytep, 16.01.18 19:41
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Igor Korolyov

Сообщений: 32006
Дата: 16.01.18 21:03:16ОтветитьЦитировать
Да, в запросах где есть параметр "отключающий часть логики отбора" оптимизатор не справляется. По возможности стоит более простые запросы писать - т.е. один С условием (по идее там особо без разницы IN или JOIN это условие "применяет" и другой БЕЗ условия.

IN (CASE when ... END)
Это по сути IN (скалярное_выражение) а значит то же самое что и IN (одно_значение) - т.к. case это "выражение" (можно сказать оператор) возвращающий всегда строго одно значение. Естественно что ветви case тоже должны возвращать "одно значение", а не какой то там подзапрос - потому оно и ломается если подзапрос возвращает >1 записи. Теоретически, конечно, можно там что-то замутить с объектными коллекциями, но тогда обрабатывать такое дело придётся явно не через IN (список_значений).
Во втором случае получаем
поле IN ('1,2,3') что, естественно, совершенно не одно и то же что и поле IN (1,2,3). Не ругается т.к. весьма мягок в части автоматического приведения типов, но логика то совершенно не та будет.
Это типичная ошибка когда пытаются "параметризовать" IN - думают что если передать строку с циферками через запятую, то сервер волшебным образом станет понимать это как НАБОР значений. Но это, конечно же, совсем не так.

P.S. Тут Том показывает технику "динамических" запросов, но при том вполне себе параметризованных (т.е. безопасных с точки зрения той же SQL-инъекции)
asktom.oracle.com
Возможно это получится и для твоей задачи применить (если ref cursor, процедуры и контекст не "запрещены как класс" ) вместо контекста в принципе можно и временную таблицу использовать - с нужными полями-параметрами, но тогда, вероятно, потребуются хинты в запросе показывающие что в этой таблице ровно 1 запись находится.


------------------
WBR, Igor




Исправлено: Igor Korolyov, 16.01.18 21:35
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 16.01.18 21:32:25ОтветитьЦитировать
Igor Korolyov
По возможности стоит более простые запросы писать - т.е. один С условием и другой БЕЗ условия.

Не совсем понимаю. Это что-то типа
  
      select    
        contract_id    
      from contracts cn    
      where cn.contract_date between :p_startdate and :p_enddate    
        and cn.branch_id in(select branch_id from branches)  
        and :p_branch_id is not null  
      union all  
      select    
        contract_id    
      from contracts cn    
      where cn.contract_date between :p_startdate and :p_enddate    
        and :p_branch_id is null;
?
Ок. В примере всего 2 параметра, но что если у меня десяток различных параметров?
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Igor Korolyov

Сообщений: 32006
Дата: 16.01.18 21:36:47ОтветитьЦитировать
Нет, без union - исполнять либо один запрос либо другой. Динамика, хотя и ограниченная. Я PS выше добавил


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
pasha_usue

Сообщений: 2928
Откуда: Е-бург
Дата: 17.01.18 07:38:52ОтветитьЦитировать
Я бы поменял местами аргументы у OR. Нет, в компилированный запрос условие всё-равно будет включено, так как там параметр. Но на этапе исполнения конструкция выигрывает обычно:
and (:p_branch_id is null or cn.branch_id in(select branch_id from branches));
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 17.01.18 10:18:39ОтветитьЦитировать
pasha_usue
Я бы поменял местами аргументы у OR
Лежит на поверхности. Тоже один из первых попробованных вариантов. Как выше говорил,
Pekpytep
Оптимизатор неадекватно реагирует на "or :p_branch_id is null" в каком бы месте это условие ни стояло.
План запроса аналогичен.

Igor Korolyov
Том показывает технику "динамических" запросов
Посмотрел. Очень "так себе" вариант. Любая доработка превращается в "танцы с бубном в гамаке на лыжах вверх ногами". Сильно осложняется сопровождение даже для меня, а у нас, между прочим, в штате есть вчерашние студенты с отсутствием опыта на позициях младших разработчиков.

В общем, пришлось привлекать ведущего с опытом работы на этом проекте. Вынес обработку параметра в with, в branches выбираю либо значения из параметра, либо целиком все дерево филиалов. В финальном селекте делаю inner join договоров с филиалами. Также хинтами пришлось подшаманить план выполнения, что позволило partition hash all+range scan по дате в contracts заменить на partition hash iterator+range scan по (contract_date, branch_id).
Что-то типа
  
  with    
      branches as    
      (    
        select ...    
        from(select nvl(:p_branch_id, 'ROOT') from dual)  
        where ...  
      )    
      select /*+ leading(br) use_nl(br, cn) index(cn IDX_index_name) */  
        contract_id    
      from contracts cn   
      join branches br  
        on cn.branch_id = br.branch_id  
      where cn.contract_date between :p_startdate and :p_enddate;



Исправлено: Pekpytep, 17.01.18 12:17
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Igor Korolyov

Сообщений: 32006
Дата: 17.01.18 12:28:25ОтветитьЦитировать
Если таблица-фильтр не очень большая то в принципе, конечно, можно и так решать - т.е. вместо "отключения" условия "наличие в списке" просто подсовывать в такой список ВСЕ записи. Естественно это не будет оптимальным (т.к. и "ненужный" запрос и "ненужная" проверка никуда не денутся), но если никак не получается сделать таки два (или более) разных запроса - динамикой, либо тривиальным дублированием его "основного" текста с соответствующими изменениями - ну что ж, придётся терпеть неоптимальность... Часто упрощение сопровождения связано с ухудшением скорости работы и наоборот...


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Igor Korolyov

Сообщений: 32006
Дата: 17.01.18 12:44:27ОтветитьЦитировать
pasha_usue
Я бы поменял местами аргументы у OR.
Ораклу по барабану это (и это совершенно логично - логика запроса НЕ должна зависеть от порядка следования условий).
Условие с OR в лучшем случае может быть преобразовано в UNION-ALL (точнее CONCATENATION, но по сути это один чёрт) двух частей (каждая из которых более-менее оптимальна по своей сути).
Естественно что когда "по логике" нужна всегда только одна часть, это будет неоптимально. Ну, точнее, для варианта "выбрать всё" оно и так и так плохо будет, а вот для варианта "взять кусочек", потери от того что берём таки всё и всегда будут весьма значительны.
Иногда произвести "разложение на части" не получается, и тогда может происходить всякий ужас - включая картезианское произведение "соединяемых" таким "особо хитрым способом" таблиц, с последующим фильтром.


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Гулин Федор

Сообщений: 3940
Откуда: Минск
Дата: 19.01.18 17:24:00ОтветитьЦитировать
в финальном селекте 18 джойнов, 7 коррелирующих подзапросов, примерно столько же вызовов функций во внешнем пакете.

давно с ораклом не работаю с 2014 - счас с MS-SQL (чуть MySQL)
и что там гут что вот такой мегазапрос можно разбить на кучу запросов с TEmp таблицами создаваемых налету
если что вылезет так даже ошибку найти проще

когда у меня был оракл. запрос к-й тоже стал выходить за рамки читаемости
я тоже вводил Temp таблицу там - но ее надо было создавать заранее
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Igor Korolyov

Сообщений: 32006
Дата: 19.01.18 17:44:55ОтветитьЦитировать
Временная таблица может быть лишней сущностью для реальной работы (по сути это создание "почти настоящих" объектов-таблиц). В принципе оракл тоже может создать временную таблицу a-la MSSQL (т.е. "автоматически", без предварительного её описания в словаре данных) - называется это "материализация подзапроса" (тоже живёт во временном сегменте временного табличного пространства). Обычно оптимизатор сам решает, нужно ли это делать, но можно хинтом подсказать (для факторизованного запроса - т.е. того самого WITH ...). Разница в том, что область видимости такой временной таблицы всё равно ограничена телом этого запроса. Т.е. ни в отладке нельзя "по шагам" пройти и посмотреть чего же в такой временной таблице на каждом шаге будет, ни хитрые стратегии "совместного использования" данных такой временной таблицы не проходят (типа сделать 1 запрос во временную таблицу, а потом несколько разных запросов с её участием). Для таких вариантов приходится именно GLOBAL TEMPORARY TABLE применять - т.е. статически описывать структуру таблицы, и определять "время жизни" записей в ней - до первого коммита, или до конца сессии... Насколько это хуже/лучше "автосоздаваемой" временной таблицы - вопрос дискуссионный. Есть и плюсы и минусы.
В том же фоксе можно нагромоздить один мега-запрос, а можно разбить его на кучу "шагов" - но если оптимизатор не "тупит", то обычно один мега-запрос всё-же выигрывает у кучи отдельных (в "реальной работе", не в понимании логики и отладке). В фоксе оптимизатор очень простой, потому и "тупит" часто. В оракле и MSSQL гораздо более навороченный - потому в бОльшем числе случаев запрос-монстр всё же будет оптимальнее "разбитого на шаги". Особенно если разработчик ленится, и оставляет в промежуточных шагах кучу инфы полезной для отладки, но не нужной для работы (скажем нужно выбрать ID и SomeValue, но для отладки полезно ещё пару полей видеть - Name, CreationDate к примеру - и их зачастую "оставляют" в подзапросе-шаге и на продуктиве).


------------------
WBR, Igor
Ratings: 0 negative/1 positive

Re: Oracle. Подзапросы в IN()
Гулин Федор

Сообщений: 3940
Откуда: Минск
Дата: 22.01.18 12:59:38ОтветитьЦитировать
ключевой момент именно отладка и поиск багов особенно в чужом коде
здесь разделение по шагам сильно выигрывает

называется это "материализация подзапроса" (тоже живёт во временном сегменте временного табличного пространства). Обычно оптимизатор сам решает, нужно ли это делать, но можно хинтом подсказать (для факторизованного запроса - т.е. того самого WITH ...).
а что за хинт ?

ps кол-во хинтов в оракле на порядок больше чем в мс-скл
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Igor Korolyov

Сообщений: 32006
Дата: 22.01.18 13:20:18ОтветитьЦитировать
Гулин Федор
ключевой момент именно отладка и поиск багов особенно в чужом коде
здесь разделение по шагам сильно выигрывает
Ну в принципе порезать 17-этажный мега-запрос на части вполне себе можно. Да, иногда возникают "нюансы", и почти всегда возникает вопрос производительности. Даже банальное убирание/добавление "да почти ничего не значащего" условия и то может кардинально поменять весь план исполнения, и соответственно скорость, не говоря уж о делении сложного запроса на части.
Вся суть именно в том, что "простой для отладки" запрос в 99% случаев является одним из самых плохих для работы (по скорости). Да даже для обычного процедурного кода это часто будет справедливо... Тут важно соблюсти баланс - с одной стороны не скатиться в абсолютно непостижимую мега-простыню кода, а с другой не получить "понятный даже ребёнку", но работающий часами код.

Гулин Федор
а что за хинт?
/*+ materialize */


------------------
WBR, Igor
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Pekpytep
Автор

Сообщений: 697
Откуда: Луганск
Дата: 29.01.18 09:18:48ОтветитьЦитировать
Igor Korolyov
Иногда произвести "разложение на части" не получается, и тогда может происходить всякий ужас - включая картезианское произведение "соединяемых" таким "особо хитрым способом" таблиц, с последующим фильтром.
Самое для меня печальное, что нет вменяемых мануалов с действенными рецептами оптимизации запросов. Я имею в виду, что книжек по теории конечно более чем достаточно, но оптимизатор - это "черный ящик" и знание устройства b-tree индекса никак не поможет мне понять, почему тупит запрос и как это побороть. Уже который раз спотыкаюсь на том, что думаю о возможностях ораклового оптимизатора лучше, чем он есть на самом деле. Каждый запрос приходится методом научного тыка делать в нескольких десятках вариантов, смотреть и сравнивать планы выполнения. Приходится идти по полю, густо усеянному граблями. Странно, но я заметил, что у запросов с соединениями типа
select ... from t1, t2 where t1.id = t2.id and ...
зачастую план выполнения лучше, чем у точно такого же в ansi-стандарте.
Так же часто подзапрос в where работает быстрее join-а
select ... from t1 where id in(select id from t2)
А однажды я был очень удивлен разнице в плане и быстродействии запросов типа
select ... from tmp_pack, contracts where contracts.sn = tmp_pack.sn  
  и  
  select ... from contracts   
  where sn between (select min(sn) from tmp_pack) and (select max(sn) from tmp_pack)
Второй, кстати, был в несколько раз быстрее.
Создается впечатление, что старое "ядро" было написано вдумчиво и тщательно вылизано, новый функционал же штампуется индусами в угоду скорости разработки и в ущерб качеству.



Исправлено: Pekpytep, 29.01.18 09:31
Ratings: 0 negative/0 positive

Re: Oracle. Подзапросы в IN()
Igor Korolyov

Сообщений: 32006
Дата: 29.01.18 11:32:48ОтветитьЦитировать
Pekpytep
Я имею в виду, что книжек по теории конечно более чем достаточно, но оптимизатор - это "черный ящик" и знание устройства b-tree индекса никак не поможет мне понять, почему тупит запрос и как это побороть.
Отчего же? В принципе посмотрев на РЕАЛЬНЫЙ план исполнения (не тот который по EXPLAIN PLAN, а тот который из V$SQL_PLAN - для реально отработавших запросов) можно понять что именно было плохо (конечно если не смотреть, как многие новички, на cost ). К сожалению это не даёт прямых наводок на то как же его нужно "подкручивать".
Собственно говоря, многие сторонние программы - "тюнеры запросов" поступают до безобразия тупо - по некоторым, весьма несложным правилам корябают исходный запрос (ну "типичные" довески +0 к числовым полям в предикатах/условиях, несложные перестановки и т.п.), и просто смотрят какой из вариантов оказывается лучше (обычно просто "быстрее"). Встроенные средства оптимизации (не сам оптимизатор, а SQL Tuning Advisor) уже чуть по иному работают (по крайней мере автосоздаваемые "профили" для запросов это НЕ изменение текста запроса, а только сохранение наилучшего плана - чтобы оптимизатор в следующий раз именно его выбрал, несмотря на то что по его "быстрым" прикидкам этот план не самый лучший) - но тоже до полного "автоматизма" тут очень далеко.
Pekpytep
Уже который раз спотыкаюсь на том, что думаю о возможностях ораклового оптимизатора лучше, чем он есть на самом деле.
Это да Хочется на него переложить максимум работы, но увы - далеко ему ещё "по уму" до даже очень среднего DBA. Тем более что некоторые задачи вообще выходят за рамки "компетенции" оптимизатора - например изменение структуры БД в неудачных местах (в т.ч. намеренная денормализация), или учёт в логике запроса непрописанных явно в структуре БД бизнес-ограничений.
Pekpytep
Странно, но я заметил, что у запросов с соединениями типа
select ... from t1, t2 where t1.id = t2.id and ...
зачастую план выполнения лучше, чем у точно такого же в ansi-стандарте.
Да, есть такое парадоксальное поведение... К сожалению лично я не могу нормально пользоваться этими старинными (+). Ну совсем неочевиден в этих случаях для меня запрос...
Pekpytep
Создается впечатление, что старое "ядро" было написано вдумчиво и тщательно вылизано, новый функционал же штампуется индусами в угоду скорости разработки и в ущерб качеству.
И с этим трудно спорить... Число багов колоссально - многие тянутся из версии в версию. Очень быстро начинаешь понимать, что для более-менее продуктивной работы нужна активная подписка на саппорт и не менее активное пользование этим самым саппортом.


------------------
WBR, Igor
Ratings: 0 negative/0 positive



Извините, только зарегистрированные пользователи могут писать в этом форуме.

On-line: 39 Аспид Alsim  and Guests: 37


© 2000-2018 Fox Club 
Яндекс.Метрика