Comments 36
Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию)
Это называется плохой дизайн.
Сделайте флаг is_canceled Int(1) not null default 0 check (is_canceled in (0,1)) и повесьте на него чек на ваши достаточно сложные условия. Наглядность, удобство и бонусом чек будет контролировать еще если где то ошибетесь.
select * from document where xxstorno(id) = 'Y'
Почему то сколько встречаю архитекторов и программистов бд, именно у ораклистов мания писать вагонами процедуры и функции, вкладывать их друг в друга и присваивать дебильные имена, орды их. А раз у них есть процедуры и функции давайте еще и данные запутаем дальше некуда, ура!
ЗЫ Раньше очень хотело поработать с ораклом, такая знаменитая бд. Поработал, да не с одной, теперь хочется развидеть все это.
Да, функции — это убийцы производительности, если их неправильно готовить. А в случае, если это ещё и легко заменить простым составным выражением, то уж лучше добавить виртуальное поле и на нем уже создать индекс.
Так же думаю что не стоит проектировать базу так, что нужна функция, чтобы найти отмененные документы, я выше написал как этого избежать. Вообще вычисления при селектах — зло.
И раз уж пошла такая пьянка, «resp_q», «main_q» вы очень пожалеете о таких именах частей CTE, ну и преемники ваши вам спасибо точно не скажут.
«resp_name, userid» — два разных стиля именования, почему не user_id, плюс, что такое resp вот приходится догадываться, сейчас же у всех автокомплитеры, зачем вы экономите. Вы же сами пишете «хотел рассказывать истории своими селектами», ну дак рассказывайте, запрос должен читаться от начала до конца.
По своей природе я люблю кодить, но, когда приходит время тестировать, весь мой энтузиазм куда-то пропадает.
Потому что не пользуетесь check. Когда у вас база обвязана внешними ключами, стоят ограничения на значения везде и всюду, частичные индексы, контроли уникальности, и тд итп, тестирование становится очень легким, а иногда вообще не нужным.
ЗЫ плюс еще и избавляетесь от запоминания не нужных нюансов и всякой шелухи, например: ой, мне вот нужно выбрать только опубликованные, а не нужно ли проверять являются ли они удаленными, допишу ка еще условия… или
constraint is_deleted_publish_check check ((is_deleted = false) OR (is_published = false))
p.s.: За неэкранированные названия таблиц и полей надо бить канделябром. Имхо.
Поясните, почему? У меня например от лишних (= не меняющих ничего в работе запроса) кавычек начинает рябить в глазах.
не нужно создавать таблицы/столбцы в lowercase — потом замаяться можно это все прописывать в SQL-запросах
+ не весь софт с ними потом сможет работать
не нужно создавать таблицы/столбцы в lowercase — потом замаяться можно это все прописывать в SQL-запросах
А что за проблемы у Оракла с lowercase названиями таблиц и столбцов?
SQL ведь по своему духу не похож на обычные императивные языки: там надо написать что нужно получить, но не надо(в идеале, жизнь сложнее) описывать как это получить. Поэтому расказывать истории — это IMHO не в духе SQL.
Аналогично с тестированием — тестировать лучше на специально подобранных данных. Для теста стоит завести тестовую БД со специальным наполнением и проверять запрос на ней. Правда, не знаю как там будет интеграцией с используемой IDE — можно ли «не отходя от кассы» сразу увидеть зелененькие огоньки прошедших тестов.
Бывает что нужно именно по шагам расписать весь алгоритм получения данных. Вот хороший пример (для Postgres, но это сути не меняет):
https://www.endpoint.com/blog/2020/10/02/postgresql-binary-search-correlated-data-cte
В плане SQL я с этим столкнулся ещё лет двадцать назад, когда мы обнаружили, что планировщик запросов Interbase 4.0 (была такая СУБД, в те времена весьма популярная, ибо шла в комплекте с Delphi) совершенно не умеет оптимизирвать запросы с LEFT OUTER JOIN, скатываясь в последовательную выборку по левой таблице (естественно — самой большой). Нам тогда пришлось разделить запрос на два (благо OUTER JOIN был нужен ровно один) и объединять их через UNION.
Но вот пример по ссылке — это то, как делать не надо (ну, если вообще есть возможность так не делать, конечно ;-), ещё раз повторю: жизнь сложнее ): сначала там отказались от использования совершенно стандартной возможности СУБД — индекса, а потом героически написали фактически свой планировщик запроса на SQL, чтобы получить хоть сколь-нибудь эффективную выборку на тех возможностях, что у них остались. Ну да, не спорю: для написания планировщика запроса продумывать последовательность действий, естественно, необходимо. Но такие задачи — редкость (мягко говоря): чаще всего хватает возможностей планировщика, который уже есть в СУБД.
Я даже понимаю (вроде бы) резоны владельца СУБД, куда пишется лог — почему он мог запретить создавать индекс: обновление индекса может быть затратным, а новые записи в логи иногда сыпятся так быстро… Но, наверное там надо было бы что-то поменять в архитектуре.
К примеру, хранение лога, вообще говоря, вовсе не требует такой сложной штуки, как СУБД. В частности, для задачи последовательной записи лога и бинарного поиска в нем номеру записи хватило бы применения такого артефакта древних времен, как «набор данных произвольного доступа с записями переменной длины»: данные совершенно последовательно пишутся в конец лога, а бинарный поиск делается чтением этого набора, открытого с произвольным доступом к записям по их номерам. Когда-то, во времена IBM System/360, поддержка такой штуки опиралась даже на аппаратные возможности самих дисков (CKD Devices). Сейчас со штатной поддержкой такого, конечно, стало сложнее, но даже на базе обычного плоского файла сделать что-то можно — правда, границы записей придется искать самим в программе.
Но ещё лучше бы подошел другой артефакт тех же врмен: «индексно-последовательный набор данных» (который тогда обслуживался утилитой с примечательным именем, которое я помню до сих пор: IEBISAM): данные в нем располагались последовательно в порядке некоего ключа, и записи по этому кючу можно было искать. поэтому для задачи поиска по ключу в виде даты/времени записи он подошел бы лучше. И вот у у этого древнего артефакта есть прямой аналог — clustered index — в современных СУБД, по крайней мере — в некоторых (точно знаю, что есть в MS SQL, но вот за Postgress не скажу). То есть для приема логов в БД создается таблица с первичным кластерным индексом по дате/времени (лучше — с повышенным фактором его заполнения, чтобы не терять зря много места). Запись в нее фактически идет последовательно, без особых потерь на поддержку индекса (разделения блоков дерева там не будет, а поворот, чтобы дерево сбалансировать, может быть отложен и до момента, когда нагрузка снижается). А запрос для выборки по диапазону дат отлично оптимизируется по первичному индексу шатным планировщиком. Вот как-то примерно так можно было бы сделать.
сначала там отказались от использования совершенно стандартной возможности СУБД — индекса, а потом героически написали фактически свой планировщик запроса на SQL, чтобы получить хоть сколь-нибудь эффективную выборку на тех возможностях, что у них остались.
Там в первом же абзаце написано, что опции изменить приложение или дизайн базы у них не было, так что пришлось пораскинуть мозгами. В результате они устранили аварийное завершение запросов по таймауту на вебсервисе, и запрос стал выполняться за доли секунды. Кстати, они консультировались на IRC канале #postgresql с экспертами.
Так часто бывает у приглашённых консультантов, я и сам оказывался в такой ситуации не раз. Помню как на большом проекте OeBS ужасно тупил Oracle Application Server (конкретно его компонент Concurrent Program Manager), и не было совершенно никакой возможности исправить SQL запрос внутри исполнимого файла сервера (бинарника), который постоянно нагружал базу и вылезал в топ AWR. Проблему решили при помощи stored outlines (это коллекция сохранённых хинтов оптимизатора для конкретного хэша запроса). Сотрудники Oracle подтвердили что других вариантов нет, и патч для сервера выпускать не захотели.
Но вот выдавать нужду за добродетель и пропагандировать как универсальный такой подход, что надо писать на SQL поперек его идеологии языка декларативного программирования — с этим не согласен.
PS По то, что непосредственно у авторов статьи по ссылки не было возможности менять что-то в базе — это я понял. Но такая возможность была у других, тех кто базой владеет (а у авторов — убедить владельцев в пользе этого). Добавление индекса к таблице, в отличие от изменения запроса — операция почти бесплатная и почти не влияющая на работу других приложений (но опять-таки, почти: обновление добавленного индекса добавляет нагрузку, например).
Потому и написал — отказались (и не написал — кто).
Для теста стоит завести тестовую БД со специальным наполнением и проверять запрос на ней...
Пробовали как-то ещё в лохматых 2010-2011 годах в компании внедрить такой подход — с треском провалился. Проекты по построению DWH и отчетности над ним. Очень трудозатратно генерировать синтетические данные или специально отбирать «нужные». Кроме того данные быстро устаревают.
В итоге самым эффективным (с точки зрения скорости внедрения функционала в ПРОД, поддержки, внесения изменений, трудозатрат) оказался подход, когда тестировщик по ТЗ пишет «прототип» (т.е. набор SQL-скриптов, которые в итоге собирают целевую витрину/таблицу в виде временной) и потом результат этого прототипа сравнивается с результатом разработанного разработчиком ETL. Дополнительно, если у заказчика есть «эталон» (отчета, собранный вручную например или альтернативным способом), то сверяем с ним.
Помимо этого, наступал момент, когда запрос разрастался до гигантских размеров и становился практически не читаемым.
Вы меня извините, конечно, но ВСЕ запросы в этой статье оформлены абсолютно нечитаемо.
with user_groups_actual as (select id,
name
from acl.user_groups
where validity_period @> curdate),
users as (select ut.id as user_id,
ut.name as user_name,
gt.id as group_id,
gt.name as group_name
from acl.users as ut
left join user_groups_actual as gt
on ut.group_id = gt.id
and ut.pretty_field = 1
where ut.id = :user_id)
select *
from users;
ЗЫ не смог придумать зачем тут CTE но оставил все же, хотя выборка ВСЕХ групп — глупость. И да, я не экранирую имена полей, у pg замечательный парсер.
ЗЫЗЫ where r.end_date > sysdate — это не верно, вы так выберете и не вступившие в силу.
WITH user_groups_actual AS (
SELECT id, name
FROM acl.user_groups
WHERE validity_period @> curdate
)
, users AS (
SELECT ut.id AS user_id
, ut.name AS user_name
, gt.id AS group_id
, gt.name AS group_name
FROM acl.users AS ut
LEFT JOIN user_groups_actual AS gt
ON ut.group_id = gt.id AND ut.pretty_field = 1
WHERE ut.id = :user_id
)
SELECT *
FROM users
;
Ну перенос тела CTE на новую строку тоже надо настроить да но у меня datagrip очень любит сопротивляться настройке отступов, надо наверное сносить и новый ставить а там багрепорты писать пачками.
Давайте я попробую.
Вот всё хорошо, но после select, from и where мне кажется нужно делать новую строку с отступом.
Да и после открывающей скобки. Тогда отступ у селектов после as будет одинаковый и не надо будет напрягаться, чтобы понять, что это запросы одного уровня.
Лично я ещё и после with перехожу на новую строку и не делаю на этой строке отступ.
Вот всё хорошо, но после select, from и where мне кажется нужно делать новую строку с отступом.
Пробовал, плохо получается когда большой каскад идет.
Да и после открывающей скобки. Тогда отступ у селектов после as будет одинаковый и не надо будет напрягаться, чтобы понять, что это запросы одного уровня.
Это да, донастроить надо, почему то датагрип очень любит мозги мне потрогать когда отсупы настраиваешь.
И имя ему with
select resp_q as ( select resp_name, userid from resp where r.end_date > sysdate) ,main_q as ( select u.name, r.respname from user u left join resp_q r on u.id = r.userid where id = 1) select * from main_q
Вопрос на засыпку. А где тут, собственно, with?
Я вижу только вложенный запрос. Или это он имеется ввиду (если что, такой трактовки with в SQL еще не встречал)?
Как бы там ни было, мне статья принесла пользу. Пару лет не работал с SQL и забыл про тот же with. На днях снова с ним столкнулся и вот как раз нужно было оптимизировать довольно крупный запрос со всякими вложенностями. Понятно, что хорошо бы почитать перед этим книги и статьи, для освежения памяти, но времени мало. Наткнулся на статью и вспомнил про with, теперь копну в нужном направлении и сделаю что требуется. Так что спасибо автору, такие обзоры несут реальную пользу на моем примере, для джунов или нет :)
Эволюция моих SQL запросов