Друзья, всех с прекрасным зимним выходным!
Небольшой соцопрос коллективного разума на тему оптимизации.
То ли я недостаточно хорошо думаю, то ли действительно нет универсального рецепта...
Однако, мыслей - хаотичный рой, никак не упорядочу.

Дано: есть некая основная таблица (схема "факты - измерения" ту неприменима) и несколько с ней связанных. Некоторые - справочники, некоторые - довольно динамично обновляются. Есть отношения как один ко многим, там и многие к одному от основной к связанным. Ни в одной из связанных наличие соответствий не обязательно.
И есть 3 десятка параметров, по которым нужно осуществлять выборку из всего этого безобразия, причем могут быть указаны как все 30, так и ни одного.

В табличках - по 2-4 млн записей, и это уже вызывает проблемы производительности.

Как в мире принято такое реализовывать? Честно гуглил - рецептов не нашёл (может плохо искал, хоть и долго)
Пусть технология такова, например: передать 30 параметров в функцию, вернуть курсор приложению.

Просто запрос с параметрами пробовал. Не удалось заставить строить корректные планы при разных наборах параметров, как не хинтуй.
Пробовал матвью строить и индексировать - оказалось глупой идеей, слишком динамично изменяются некоторые данные.
Пробовал в денормализованную таблицу данные сливать, индексировать, и искать по ней - не сильно помогло. Блокировок много (приходится UL ставить) и таблица большая - выхлоп с блока маленький, чтений много, индексов много - запись медленная.
Динамический SQL в зависимости от набора параметров - пробовал, даже хинты в динамике вставлял, но приходится использовать литералы, т.к. не придумал, как забиндить произвольный набор из 30.
Писать if/case с тысячами ветвлений - кмк так себе идея...

В итоге сейчас работает на денормализованной таблице + динамика с литералами. Так работало быстрее остальных вариантов. Но начали одолевать блокировки - увеличивается интенсивность изменений. Да и планы строятся так себе...

AmKad1) Выявить набор наиболее часто передаваемых параметров и подумать в сторону партиционирования по ним исходного набора данных. Да, спасибо, думал об этом. Однако смущает судьба запросов, набор параметров в которых не будет "часто передаваемым". Есть прогноз, что таких будет много.

AmKad2)
gandalf-the-greyно приходится использовать литералы, т.к. не придумал, как забиндить произвольный набор из 30Тут я тебя не понял. Литералы и ограничение на 30 - вещи несвязанные.
Да, криво объяснил. Попробуем так: если зафигачить в запрос ВСЕ предикаты (30 штук) в виде:


то в плане сплошные full scan, даже если один из параметров на PK одной из таблиц ссылается (видимо, из-за left join'ов)
Если же формировать динамику, в зависимости от переданных параметров, то для написания потом execute immediate для формирования using придётся обернуть ещё в одну динамику.

AmKad3) gandalf-the-greyВ итоге сейчас работает на денормализованной таблице + динамика с литералами. Так работало быстрее остальных вариантов. Но начали одолевать блокировки - увеличивается интенсивность изменений.О каких блокировках идет речь? Данные обновляются из нескольких источников, по логике приложения надо лочить таблицу при этом, иначе сплошные дедлоки.

gandalf-the-greyЕсть отношения как один ко многим, там и многие к одному от основной к связанным.
Прошу прощения! Поторопился, от основной к связанным отношение многие/1 к 1! Никаких один ко многим нет.

Elic, спасибо!

ВМоисеев,

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

Elic,

ещё несколько вопросов, если не затруднит. Ваши ответы всегда весьма полезны.

1) Как думаете, почему в статье рекомендован 7-й вариант с execute immediate? Мне более импонирует 4-й. Хотя бы тем, что через DBMS_SQL.BIND_VARIABLE явно передаются параметры разных типов, исключая неявные или закостыленные в запрос преобразования.
У меня, к примеру, жизнь осложняется тем, что некоторые из параметров - массивы. DBMS_SQL.BIND_ARRAY?

2) Этот вопрос не особо связан с темой, но в моём случае напрямую.
Имеем веб-приложение + пул соединений --> каждый запрос с веба приходит в произвольную сессию из пула. Используем механизм прикладных псевдосессий. Результаты поиска юзеру на гуй надо отдавать постранично, а не всю портянку (которая может тупо в ОЗУ не влезть к нему).
Имеет смысл сохранять, например, в nologging-таблицу по результатам поиска набор ROWID от ведущей таблицы, чтобы при перемещении юзера по страницам отдавать нужные порции, не выполняя основного тяжелого запроса; или это все от лукавого и стоит переложить нагрузку на средний слой, обязав его доставать всю портянку и рулить постраничным разбиением самостоятельно?
В первом варианте весь груз ложится на БД - хранить временные списки ROWID от запросов пары тысяч юзеров, хоть и неподолгу, но, в худшем случае, до миллиона записей каждый
Во-втором варианте появляется много ненужного трафика от БД к среднему слою
За ресурсы среднего слоя особо не беспокоимся, там масштабируемый кластер, а вот БД в одном экземпляре...
Как сейчас принято такие задачи решать?

Elicgandalf-the-greyКак думаете, почему в статье рекомендован 7-й вариант с execute immediate?Это не статья. И я не думаю, а вроде как недвусмысленно написал, почему. - Сопровождаемость.
Спасибо, сразу не дошло.
Elicgandalf-the-greyМне более импонирует 4-й.Кто не понимает процедурного подхода или не может к нему привести, пишет тонны copy-paste-ного говнокода и вынужден иметь "терпение и крепкие нервы при отладке".
Ну, я готов написать по 2 строки кода на каждый параметр вместо одной, ибо их конечно число. Мне казалось, что DBMS_SQL безопаснее с точки зрения инъекций
Elicgandalf-the-greyХотя бы тем, что через DBMS_SQL.BIND_VARIABLE явно передаются параметры разных типов, исключая неявные или закостыленные в запрос преобразования.Двойное неявное преобразование число-строка-числа безопасно и ради удобства в простейших случаях им можно пренебречь.
А вообще, типизация там оставлена в качестве домашнего задания для пытливых умов.
Согласен. Но есть ещё даты ;)
Elicgandalf-the-greyжизнь осложняется тем, что некоторые из параметров - массивы . DBMS_SQL.BIND_ARRAY?Поподробней и с примером.
Например, глобально объявлено что-то вроде


один из параметров процедуры имеет объявлен как


и в случае статического SQL его применение к запросу выглядит как


Elicgandalf-the-greyпереложить нагрузку на средний слой, обязав его доставать всю портянку и рулить постраничным разбиением самостоятельно?IMHO, путь в никуда.Возможно. Я лишь знаю, что подобные реализации существуют, на практике не доводилось применить и сравнить.
Elicgandalf-the-greyЗа ресурсы среднего слоя особо не беспокоимся, там масштабируемый кластер, а вот БД в одном экземпляре...Странная расстановка приоритетов. Использование БД как транзакционного файлового сервера?
gandalf-the-greyКак сейчас принято такие задачи решать?Пока что слишком непарнокопытно-безвоздушно-шарообразно.
Создал отдельную тему-вопрос, попытался объясниться понятнее http://www.sql.ru/forum/1247913/tyazhelyy-zapros-i-vydacha-rezultata-polzovatelu-postranichno
Про БД как ФС - сорри, тоже не уловил. Архитектуру придумали до разработки и пока работаем с тем, что имеем. Есть кластер WebSphere, бэкенд на жаве, фронт на JS, и один единственный "мозг" на Oracle 11g
БД получает данные из нескольких источников, агрегирует, преобразует. Это не OLTP, не DWH... Скорее что-то типа DSS. Но данные некоторые обновляются динамично, кэшировать не получится. Юзер хочет среди этих данных искать, цели и мотивы - разные. В общем случае он может ввести фильтры, по которым отберутся 99% записей. Надо обеспечить комфортную навигацию, учитывая вводные по архитектуре.