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

Формат вывода:
Среда 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 #коллекции

Ассоциативный массив (associative array/index by)

Ассоциативный массив - множество пар ключ-значение.

Ключом может быть:
1) PLS_INTEGER (от −2 147 483 648 до 2 147 483 647).
2) VARCHAR2 (до 32К)

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

Синтаксис:
type имя_типа is table of тип_объекта index by varchar2(N char);


type имя_типа is table of тип_объекта index by pls_integer;

index by - ассоциативный массив
varchar2, pls_integer - типа ключа в массиве.

Примеры:

-- Ассоциативный массив с ключом строкой и элементами типа number.
type t_my_type2 is table of number(30,0) index by varchar2(30000 char);
v_my_list   t_my_type;

-- Ассоциативный массив с ключом числом и элементами типом boolean.
type t_my_type2 is table of boolean index by pls_integer;
v_my_list2   t_my_type2;

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

Вложенные таблицы (nested tables)

Вложенные таблицы - неограниченные индексированные несвязные коллекции.
* Индексация в коллекции - pls_integer;
* Могут использоваться в качестве столбца в таблице, но на практике это очень неудобно и никто так не делает.
* Наиболее часто употребляемый тип коллекций.

Синтаксис:

type имя_типа is table of тип_объекта;

Примеры:

-- Коллекция состоящая из элементов типа столбца tab.first_name.
type t_arr   is table of tab.first_name%type;
v_arr t_arr := t_arr(); -- обязательная инициализация

-- Коллекция состоящая из элементов типа record
type t_rec is record(
  field1 number, ...
);
type t_arr   is table of   t_rec;
v_arr t_arr := t_arr(); -- обязательная инициализация

Хотите, что бы я записал видео про коллекции и операции с ними? 🎥
Тогда жмите палец вверх 👍

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

Инициализация/создание коллекций

Заполнить коллекцию элементами можно тремя способами:
1. Инициализация при создании.
2. Заполнение в run-time вручную.
3. Заполнение через SQL-команды.

Пример заполнения коллекции через SQL:

declare
  type t_rec     is record( n1 number, n2 number);
  type t_tab_rec is table of t_rec;  -- тип коллекции
  v_tab1          t_tab_rec;  -- переменная коллекции
begin
  -- результаты помещаются в коллекцию
  select * 
     bulk collect into v_tab
    from dual connect by level <= 10;
end;

Остальные примеры заполнения я покажу в видео про коллекции 🎥

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

Задача: Требуется сгенерировать случайную строку случайной длины от 10 до 20 символов, состоящих только из английских букв верхнего и нижнего регистров.

Например: scEfxmqTsVTuSBNF

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

#sql #задача

Функции с кэшированием результатов - result_cache

Всем привет!
Я решил сделать видео на тему, занявшую второе место в голосовании, которое было три недели назад.

В нем, я расскажу, что такое функции с кэшированием результатов - result_cache, рассмотрим концепцию и разберем подробней на простеньких примерах. Материал относится к advanced-уровню и я надеюсь, вы уже знаете, что такое функции в Oracle 😉

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

#видео #plsql #функции #result_cache