Обновить
Комментарии 36
Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию)

Это называется плохой дизайн.
Сделайте флаг is_canceled Int(1) not null default 0 check (is_canceled in (0,1)) и повесьте на него чек на ваши достаточно сложные условия. Наглядность, удобство и бонусом чек будет контролировать еще если где то ошибетесь.
Угу. Очень плохой. Использование функции в предикате, если по такому выражению нет FBI-индекса, приведет к full table scan. И если нужно выбрать только документы со сторнированием, то перебирать тогда вообще все записи нет никакой пользы акромя вреда:
select * from document where xxstorno(id) = 'Y'
Действительно, но тут хотябы индекс построить можно (хотя у ораклистов мода на функции которые нельзя объявить deterministic), а вот бд где нужно с хрустальным шаром данные рассматривать я вижу постоянно и это просто запредельное зло.

Почему то сколько встречаю архитекторов и программистов бд, именно у ораклистов мания писать вагонами процедуры и функции, вкладывать их друг в друга и присваивать дебильные имена, орды их. А раз у них есть процедуры и функции давайте еще и данные запутаем дальше некуда, ура!

ЗЫ Раньше очень хотело поработать с ораклом, такая знаменитая бд. Поработал, да не с одной, теперь хочется развидеть все это.

Да, функции — это убийцы производительности, если их неправильно готовить. А в случае, если это ещё и легко заменить простым составным выражением, то уж лучше добавить виртуальное поле и на нем уже создать индекс.

Конечно, реализация вышла несколько другой и в таком виде запрос никогда бы не отработал за приемлемое время. Но я не хотел прикладывать конечный запрос на 150 строк, т.к. он вносил ненужные усложнения, не имеющие отношения к данной статье. Я хотел показать как можно тестировать, а не как писать запросы. Думаете стоило бы выложить?
Думаю не стоит называть так функцию: «xxstorno»
Так же думаю что не стоит проектировать базу так, что нужна функция, чтобы найти отмененные документы, я выше написал как этого избежать. Вообще вычисления при селектах — зло.

И раз уж пошла такая пьянка, «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))
Какая-то не сеньерская статья, извините, но ощущение что это радостные ощущения джуниора прослушавшего первый курс по sql и спешащего поделиться результатами:) Простите, написано приятно, здравые мысли есть, но уровень статьи (не Ваш разумеется, статьи) реально sql для dummies:)
p.s.: За неэкранированные названия таблиц и полей надо бить канделябром. Имхо.
Сейчас любой может назвать себя сеньером-помидором и никто ничего не докажет.
Спасибо за обратную связь. Писал как есть. Надеюсь мои работодатели будут оценивать мой уровень не только по моим статьям)))
> p.s.: За неэкранированные названия таблиц и полей надо бить канделябром. Имхо.
Поясните, почему? У меня например от лишних (= не меняющих ничего в работе запроса) кавычек начинает рябить в глазах.
Боязнь наступить на ногу тупому парсеру, привычка из глубины времен. Хотя и сейчас, после достаточно умного парсера пг, mysql или оракл иногда удивляют.
кавычки в оракл в именах объектов не нужны и даже вредны имхо.
не нужно создавать таблицы/столбцы в lowercase — потом замаяться можно это все прописывать в SQL-запросах
+ не весь софт с ними потом сможет работать
Вы говорите о создании таблиц а я о запросах к ним.
не нужно создавать таблицы/столбцы в lowercase — потом замаяться можно это все прописывать в SQL-запросах

А что за проблемы у Оракла с lowercase названиями таблиц и столбцов?

по умолчанию имена объектов регистронезависимы, но если имя объекта заключить в двойные кавычки, то оно станет регистрозависимым.
Если не ошибаюсь то как то так
MY_TABLE = my_table = My_Table = "MY_TABLE"
"MY_TABLE" != "my_table" != "My_Table"

Я бы сказал — статья по духу совершенно не SQLвская.
SQL ведь по своему духу не похож на обычные императивные языки: там надо написать что нужно получить, но не надо(в идеале, жизнь сложнее) описывать как это получить. Поэтому расказывать истории — это IMHO не в духе SQL.
Аналогично с тестированием — тестировать лучше на специально подобранных данных. Для теста стоит завести тестовую БД со специальным наполнением и проверять запрос на ней. Правда, не знаю как там будет интеграцией с используемой IDE — можно ли «не отходя от кассы» сразу увидеть зелененькие огоньки прошедших тестов.
Ну, я именно по этому поводу и написал — жизнь сложнее.
В плане 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 По то, что непосредственно у авторов статьи по ссылки не было возможности менять что-то в базе — это я понял. Но такая возможность была у других, тех кто базой владеет (а у авторов — убедить владельцев в пользе этого). Добавление индекса к таблице, в отличие от изменения запроса — операция почти бесплатная и почти не влияющая на работу других приложений (но опять-таки, почти: обновление добавленного индекса добавляет нагрузку, например).
Потому и написал — отказались (и не написал — кто).
Для хранения данных у пг берется TimescaleDB делается гипертаблица и погнали писать туда ведрами.
Для теста стоит завести тестовую БД со специальным наполнением и проверять запрос на ней...

Пробовали как-то ещё в лохматых 2010-2011 годах в компании внедрить такой подход — с треском провалился. Проекты по построению DWH и отчетности над ним. Очень трудозатратно генерировать синтетические данные или специально отбирать «нужные». Кроме того данные быстро устаревают.
В итоге самым эффективным (с точки зрения скорости внедрения функционала в ПРОД, поддержки, внесения изменений, трудозатрат) оказался подход, когда тестировщик по ТЗ пишет «прототип» (т.е. набор SQL-скриптов, которые в итоге собирают целевую витрину/таблицу в виде временной) и потом результат этого прототипа сравнивается с результатом разработанного разработчиком ETL. Дополнительно, если у заказчика есть «эталон» (отчета, собранный вручную например или альтернативным способом), то сверяем с ним.

Автор не зная о джойнах устроился разрабом SQL? oO Только на уровне мидла узнал о CTE? Серьезно? Если так, то я тогда почти сеньор
Помимо этого, наступал момент, когда запрос разрастался до гигантских размеров и становился практически не читаемым.

Вы меня извините, конечно, но ВСЕ запросы в этой статье оформлены абсолютно нечитаемо.
Посоветуйте пожалуйста, как стоило бы оформить запросы?
Давайте я попробую. Хотя автоформатер у меня сейчас переснастраивать надо но он выдаст нечто вроде:

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, теперь копну в нужном направлении и сделаю что требуется. Так что спасибо автору, такие обзоры несут реальную пользу на моем примере, для джунов или нет :)

Аккуратней с CTE, помните что его части выполняются параллельно и в зависимости от базы там есть нюансы с материализацией.
Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.