[Oracle, SQL] Эволюция моих SQL запросов

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
26-Янв-2021 02:30

Цитировать

Всем привет! Я тимлид и Senior Oracle Developer, 12 лет работаю с OeBS и в основном пишу SQL запросы. Хотел бы рассказать, как за это время менялся мой подход в написании SQL запросов. Вначале было слово, а точнее запрос. Скажем

select name from user where id = 1

Написать такой запрос как-то не так практически невозможно. Он одинаково хорошо работает во всех известных мне базах данных. А знаю я только oracle :З Но подозреваю что и в других реляционных тоже всё будет ок.Так что же произошло? Проблемы начались, когда таблиц стало две:

select name from user u, rest r where u.id = 1 and u.id = r.user_id

Этот код вызывал у меня больше вопросов. Например, как должны быть соединены эти таблицы? Казалось бы что проще id = user_id, но мне что-то не нравилось. В блоке where мне не хватало четкого разделения между условиями фильтрации и соединениями таблицам. Когда запрос содержал 2 таблицы всё ещё было норм, но когда кол-во таблиц доходило до 5 - всё рассыпалось. Взглянув на запрос, я не мог сразу понять как соединены таблицы и не пропущена ли какая-то связка. И с этим все прекрасно жили, но я не мог. Однажды мне, молодому джуну, на глаза попался ANSI синтаксис.

select name from user inner join rest on u.id = r.user_id where u.id = 1

букв стало немного больше, но я намного лучше стал понимать, как связаны таблицы в моих SQL выражениях. Мир запросов расцвёл для меня новыми красками, и я больше не писал запросы как-то иначе. А ещё распространял эту весть среди других джунов. Это был мой первый шаг в эволюции SQL. Я вырвался от привычных шаблонов легаси кода и сделал что-то своё. Но была одна проблема. Когда используется скажем левостороннее соединение ANSI синтаксис заставляет переносить в связки, и все прочие ограничения для таблицы.

select u.name, r.resp_name
from user u
left join resp r on u.id = r.user_id and r.end_date > sysdate
where id = 1

Это меня жутко бесило, так как опять связи и параметры сливались в одну кучу. Помимо этого, наступал момент, когда запрос разрастался до гигантских размеров и становился практически не читаемым. К тому времени я уже дорос до мидла и хотел рассказывать истории своими селектами. И это подтолкнуло меня на второй шаг эволюции. И имя ему 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 позволили мне разбить монолитный запрос и группировать разные кусочки запроса по “историям”, а потом сплетать их вместе. Я мог рассказать про свой запрос так: “Получаем список пользователей. Список ролей. Объединяем их в одну выборку и отсекаем тех кто нам не нравится. С оставшимися идём дальше, взявшись за руки.” И за каждый шаг отвечала свой небольшой именованный запрос. Это также помогло мне бороться с моим злейшим врагом WET, т.к. одни и те же истории я мог использовать в разных частях своего запроса, не дублируя код. Ко всему прочему, упростилась отладка. Знай в блок from подставляй разные именованные запросы и отлаживай их по отдельности. А ещё, как выяснилось позже, с помощью with можно оптимизировать запросы, используя hint MATERIALIZE. Он материализует именованный подзапрос и данные при запросе из него берутся из темпового пространства. До этого я использовал обычные темповые таблицы. Это было более грубое решение, т.к. создавались лишние объекты БД + надо было помнить про очистку. Как итог, теперь, если запрос сложнее 10 строк, я почти всегда использую with.Но чего-то не хватало. По своей природе я люблю кодить, но, когда приходит время тестировать, весь мой энтузиазм куда-то пропадает. Как итог, я часто отдавал не до конца протестированный код. Мне регулярно приходилось слышать про unit тесты, автотесты и прочее. Но сложно было это применить к БД. Сегодня сумма за период равна 100р, а завтра 120р. И как ты тут напишешь тест? Так и жил… Но, уже став тимлидом, мне попалась задача, в которой надо было найти отмененные документы. Условие отмены было достаточно сложным и собиралось из множества нюансов (спрятал под функцию).

select * from document where xxstorno(id) = 'Y'

У меня было порядка 10 примеров документов. И завершая условие для одного документа, что-то ломалось в другом. А так как тестировал руками и глазами, времени уходило просто море. Я уже думал этому не будет конца. Пока не понял, что вокруг моего запроса можно написать обертку, которая будет за меня проверять все мои кейсы и говорить какие документы прошли проверку, а какие нет. Потратив на обертку несколько минут, я сократил время тестирования с 5-7 минут, до нескольких секунд.

with test_case as (
select 10 id, 'Y' storno from dual
union all
select 5 id, 'N' storno from dual)
, run_test as (
select tc.id, decode(xxstorno(d.id), tc.storno, 'OK', 'Error') result
from test_case tc
left join document d on d.id = tc.id)
select * from run_test

После правки функции, я просто запускал тест-запрос и смотрел сколько документов прошло тестирование, а сколько нет. В процессе тестирования я накидывал туда ещё кейсов, при том что про старые тесты тоже не забывались. И тогда я понял, как же это здорово! Как можно легко тестировать свой запрос, повышать надёжность и при этом не нужно ничего делать руками. Это может показаться элементарным, но до этого мне не встречались подобные конструкции. Обычно я видел конструкции типа and id = 5--6 7 10 135 1345 в которой просто перебором подставлялись разные значения и руками смотрелось что и как оно должно возвращать. С того дня я написал несколько разработок, и к каждой из них я уже готовил свой тестовый скрипт. Данный стиль мне очень понравился и теперь я пытаюсь привить его и своим разработчикам. Чтобы им не пришлось проделать путь в 12 лет, чтобы писать красивые SQL запросы.По итогу в мире SQL не происходит почти ничего нового уже много лет, тем не менее всегда приятно найти возможность улучшить свои запросы.
===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_oracle, #_sql, #_sql, #_oracle, #_developer, #_pl/sql, #_oracle, #_sql

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 19:50
Часовой пояс: UTC + 5