Способы получения Top-N записей (пагинация)

Существует три способа работать с пагинацией на уровне SQL. Допустим нам надо получить строки с 10 по 20.

Способ 1. Rownum + order by

select *
  from (select rownum rn,
               a.*
          from (select *
                  from employees e
                 order by e.hire_date) a
         where rownum < 20)
 where rn >= 10;

Способ 2. Аналитическая функция row_number

select *
  from (select row_number() over(order by t.hire_date) rn,
               t.*
          from employees t
         order by t.hire_date) a
 where a.rn between 10 and 19;

Способ 3. Использование расширения SQL (c Oracle 12c).

select t.*
  from employees t
 order by hire_date
 offset 9 rows fetch next 10 rows only;

По скорости примерно одинаковы. Как-нибудь, запилю видюшку с демонстрацией.

#sql #пагинация #rowcount #аналитическиефункции #offset

Задача: Требуется добавить или вычесть некоторое количество дней, месяцев или лет из текущей даты.
Например, получить набор дат - текущая дата, 5 дней до, 5 дней после, 4 месяца до, 4 месяца после, 3 года до, 3 года после.

Принцип решения:
Для вычисления дней используем стандартные сложение и вычитание, а для операций над месяцами и годами – функцию ADD_MONTHS (добавить месяцы). Для того чтобы получить дату в прошлом - вычитаем, в будущем - складываем.

Запрос:

select sysdate now,
       sysdate - 5 b_5d,
       sysdate + 5 a_5d,
       add_months(sysdate, -4) b_4m,
       add_months(sysdate, 4) a_4m,
       add_months(sysdate, -12*3) b_3y,
       add_months(sysdate, 12*3) a_3y
 from dual;

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи #системныепредставления

Защита кода (обфускация)

Защита кода - сокрытие реализаций (обфускация) исходного PL/SQL кода 🛡

Обычно применяется при поставки ПО заказчикам.
На скриншоте выше, можно посмотреть как выглядит такой код.

Ограничения:
- не стоит использовать для сокрытия паролей, имен таблиц.
- можно скрыть только тела, но не спецификации.
- код триггеров не обфусцируется.

Существует два способа обфусцировать код:
1. Пакет dbms_ddl

dbms_ddl.create_wrapped(ddl_text);

офусцированный код накатывается автоматом.

2. Утилита wrap

wrap iname=input_file [ oname=output_file ]

получившийся код накатываем на БД.

Обратный процесс (unwrap) не сложный, доступный любому. Поэтому, как сказано выше, не стоит шифровать подобным образом пароли и секретную информацию. Например, здесь можно разврапить код 🤓

#plsql #обфускация #wrap #dbms_ddl

Блокировки на уровне строк - select for update

В этом видео, я расскажу, что такое блокировки на уровне строк с использованием команды select for update, посмотрим основные параметры и разберем подробней на простеньких примерах.

Блокировки - это механизм, используемый для управления одновременным доступом к общему ресурсу.

Если вы используете базу данных, то с вероятностью в 99.9% одновременно будет работать более чем одна сессия. А значит есть вероятность конкурентного доступа к одному и тому же ресурсу. Это крайне важное понятие, при работе в OLTP-системах, и вообще базах данных. Обязательно используйте в реальной практике 😉

🎥 Смотреть видео - 8.5 мин

А впереди еще много интересных материалов 🎓

#видео #блокировки

Виртуальные поля

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

Объявление выглядит так:

create table имя_таблицы
(
  ...
  имя_столбца  generated always as (выражение) virtual
  …
);

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

Функция может быть пользовательская, но обязательно должна быть deterministic.

Альтернатива? обычный столбец, заполнение которого контролируется вручную в API, триггере и т.д.

#теория #column

Задача: Требуется подсчитать количество дней недели в текущем году. Дни недели должны быть на русском.

Формат вывода:
Среда 54
Понедельник 44
и т.д.

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Именование и комментарии

Вы пришли на новое место работы. Открыли БД. Иииии… ничего не понятно. Таблицы именованы как попало, нет комментариев к столбцам, таблицам и view и т.д. Знакомо? 🙃

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

Принцип самодокументации - хорошо определенный объект, говорит сам за себя. Чтобы понять что в нем хранится, как в нем хранится, какие ограничения и связи - достаточно посмотреть на его определение.

Как мы можем самодокументировать БД?

▫️ Название объекта - говорящее название. Об этом можно написать не одну статью, просто скажу, что команда должна выработать практики в именовании объектов и четко им следовать.
▫️Связи, ограничения - говорит о взаимосвязях объектов, характере данных.
▫️Комментарии к таблице и представлениям - кратко и по делу, отражающие суть.

▫️Названия столбцов - аналогично, отражает суть. Не надо добавлять тип столбца в название. Иногда они все же меняются, и тогда поимеите ооочень много геморроя по переименованию. Или того хуже оставить так как есть.
▫️Комментарии к столбцам - обязательны, аналогично отражается суть данных хранящихся в этом столбце, посмотрев на которую можно сразу понять без лишних приседаний, что в нем хранится.
▫️PL/SQL-код - коллеги-джависты, против такого подхода, однако чаще всего, когда смотришь на витиеватую бизнес-логику или запрос с трехэтажный дом, очень хочется увидеть, хотя бы маааленький комент, что здесь собственно происходит 😊

“Встречают по одежке” = “встречают по таблице”. Как только видишь разгильдяйство в определениях таблиц, начинаешь понимать как ведется система в целом, какого уровня был разработчик 💁🏻‍♂️

Очень рекомендую прочитать книгу «Читаемый код, или Программирование как искусство» (“The Art of Readable Code”) авторы Дастин Босуэлл (Dustin Boswell), Тревор Фаучер (Trevor Foucher). Её легко найти на просторах интернета совершенно бесплатно. Просветляет по многим моментам 📚

#теория #clearcode

Задача:
Требуется подсчитать количество дней недели в текущем году. Дни недели должны быть на русском.

Принцип решения:
1. Для того чтобы получить все дни в году, используем иерархический запрос. Для указания сколько строк нужно сгенерировать получить количество дней в текущем году.
- add_months(trunc(sysdate, 'YYYY'), 12) - первый день следующего года.
- trunc(sysdate, 'YYYY') - первый день текущего года.

2. Для каждой строки используя level - 1 получаем дату от начала года.

3. И преобразовываем в формат день недели через функцию to_char с указанием необходимой локали (nls_date_language).

4. Далее группируем.

Финальный запрос:

select day
      ,count(*)
  from (select trim(to_char(trunc(sysdate, 'YYYY') + level - 1,
                            'DAY',
                            'nls_date_language = Russian')) day
          from dual
        connect by level <= (add_months(trunc(sysdate, 'YYYY'), 12) -
                   trunc(sysdate, 'YYYY')))
 group by day;

Дополнительное задание: отсортировать результат в порядке дней недели (понедельник, вторник и т.д.).

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи #to_char #иерархическиезапросы

Ora-00600 - ночной кошмар DBD/DBA 😱

Из документации:

Error: ORA-00600 internal error code, arguments: [%s], [%s],[%s], [%s], [%s]
Cause: This is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition.
Action: Report this error to Oracle Support Services after gathering the following information.

Она говорит, о том что возникла внутренняя ошибка СУБД, которая не поддается классификации, скорее всего связана с особенностями или багами ядра. В основном, ошибка отдается с адресами и названиями структур памяти. Именно по этой информации можно понять как обойти эту ошибку.
Deadlock тоже отваливается по Ora-00600.

Варианты решения:
▫️Переписать запрос/код в соответствии с рекомендацией Oracle (далеко не всегда она есть).
▫️Если возможно, избавиться от этого куска кода, переписать.
▫️Сделать решение с учетом устранения последствий этой ошибки.
▫️Накатить фикс от Oracle с решением, если есть (далеко не всегда).

▫️Upgrade версии СУБД (самая крайняя мера).

Гуглим по содержимому ошибки, если рекомендации не подходят, обращаемся к DBA, они смотрят на металинке (оракловый суппорт) решение этой проблемы.

Примеры:

ORA-00600: internal error code, arguments: [SKGMBUSY], [1], [0], [0], [0], [], [], []
ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17114], [0x7FEA96179698], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [15851], [3], [3499], [2], [1], [], [], [], [], [], [], []

#теория #ошибкиoracle

Выбираем видео 🎥

Всем привет! Сегодня немного необычный пост.

Народонаселение нашего канала растет и мне хотелось бы записать видео с разбором темы, интересующей большинство.

Создал голосовалку с перечислением тем.
Просто нажмите на соответствующую кнопку 😉

Выбирайте 🎯

Партиционирование таблиц

Партиционирование - возможность разбить таблицы/индексы на несколько меньших, проще управляемых частей. Логически - это один объект, физически - объект состоит из разных частей.

Ключ секционирования (partitioning key) - набор столбцов, которые определяют, в какую секцию будет попадать та или иная строка.

Зачем оно нужно?
1. Повышение доступности данных - фрагменты независимые сущности. Доступность/недоступность одного фрагмента не означает, что весь объект не доступен.
2. Повышение производительности в операциях - связано с потенциальной возможностью распараллеливания DML и считывания данных (parallel в DWH).
3. Упрощение администрирования - архивирование/удаление “ненужных” фрагментов, с мелкими фрагментами управляться проще и т.п.
4. Использование в различных прикладных задачах как часть алгоритма.

В Oracle существуют несколько видов партиционирования, но об этом в следующий раз.

#теория #секционирование

Задача: Написать запросы для поиска всех мест использования в схеме какой-то конкретной таблицы.

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Виды партиционирования таблиц

Существует три основных вида партиционирования таблиц в Oracle.
1. Range (по диапазону) - последовательные диапазоны значений. Например: таблица заказов, секционированная по диапазонам поля “дата заказа” (order_date).
Наиболее часто используемый тип.

2. List (по списку значений) - неупорядоченные списки значений. Например: таблица заказов, секционированная по списку значений "регионов/штатов.

3. Hash (хеш-функции) - внутренний алгоритм хеширования СУБД. Например: таблица заказов, секционированная по хешуидентификатора заказчика (customer_id).


Максимальный уровень партиционирования - 2.
Для определения таблицы с двухуровневым секционированием (субпартиции) используется комбинация двух методов распределения данных. Сначала таблица секционируется одним методом распределения данных. Затем каждая секция подразделяется на подсекции с использованием другого метода распределения данных.

На практике, больше 1го уровня мало кто использует.

#теория #секционирование

Задача: Написать запросы для поиска всех мест использования в схеме какой-то конкретной таблицы.

Принцип решения: Для поиска используем системные представления СУБД. Может быть несколько мест, где можно найти упоминание искомой таблицы.

Финальные запросы:

-- 1) поиск в исходных кодах (исключаем динамический sql)
select * from user_source t where upper(t.text) like '%MY_TAB%';

-- 2) зависимости в других объектах (зависимость явная)
select *
  from user_dependencies t
 where t.referenced_name = upper('MY_TAB');

-- 3) старые джобы
select * from user_jobs t where upper(t.what) like '%MY_TAB%';

-- 4) новые джобы
select *
  from user_scheduler_jobs t
 where upper(t.job_action) like '%MY_TAB%';

Надеюсь, вам понравилась задачка 😉

#sql #решениезадачи #системныепредставления

Партиционирование по виртуальному полю

Можно использовать в качестве ключа партиционирования - виртуальный столбец.
1. Создается вычисляемое “на лету” поле в таблице, зависящее от других полей или deterministic-функции.
2. По виртуальному полю создается партиционирование.

Иногда это может быть удобно. Если мы хотим партиционировать не по исходным данным, а по преобразованным. Например, не по столбцу с timestamp, а по date и т.д.

Не стесняйтесь применять этот подход.

create table my_tab
(
  id      number(20) not null,
  dtime   timestamp(6) default systimestamp,
  minute  date generated always as (trunc(dtime, 'mi')) virtual,
  status  number(10) default 10
)
partition by range (minute)
(
  partition pmin values less than (to_date(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
);

#теория #секционирование #column

Именованные блокировки - dbms_lock

Всем привет! Как и обещал, я сделал видео по теме, набравшей большее число голосов 🙋🏻‍♀️🙋‍♂️

В этом ролике, я расскажу, что такое блокировки на уровне базы данных с использованием пакета dbms_lock, посмотрим основные параметры и разберем подробней на простеньких примерах и создадим Java приложение на Spring, на примере которого посмотрим, как можно пользоваться данным типом блокировок.

🎥 Смотреть видео - 9.5 мин


#видео #plsql #блокировки #dbms_lock

Коллекции

Коллекция - структура данных, сходная со списком или одномерным массивом.

Существует три вида коллекций:
1. Ассоциативный массив (assoc arrays) - неограниченные разряженные коллекции. Индекс может быть строка/число. Редко используется.
2. Вложенная таблица (nested table) - неограниченные индексированные несвязные коллекции. Индекс число. Могут иметь “дырки”. Используется очень часто.
3. Массив с фиксированной длинной (varray) - размер всегда ограничен. Не может быть “дырок” (разряженности). Практически не используется.

Все коллекции одномерные и однородные (все элементы имеют один тип). Элементами могут быть как примитивы так и объекты. Индексация начинается с “1”.

Применение:
1. Манипуляция списком данных в программе.
2. Ускорение многострочных SQL-операций.
3. Кэширование данных.

Одна из горячих тем на собеседованиях 🎓

#теория #plsql #коллекции