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

Существует три основных вида партиционирования таблиц в 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

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

Принцип решения:
Для генерации случайных значений используется пакет dbms_random.
В нем есть ряд функций для генерации строковых и числовых значений.

Для генерации строки используем фунцию string. У неё есть два параметра:
1) Какие значения генерировать.
- 'u','U' : upper case alpha characters only
- 'l','L' : lower case alpha characters only
- 'a','A' : alpha characters only (mixed case)
- 'x','X' : any alpha-numeric characters (upper)
- 'p','P' : any printable characters
2) Какой длины нужна строка

Для получения случайной длины используем функцию value с заданием диапазона.

Запрос:

select dbms_random.string(opt => 'a',
                          len => dbms_random.value(10, 20)) rnd_str
  from dual
connect by level <= 3;

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

#sql #решениезадачи #dbms_random

WHILE (пока выполняется условие)

Условный цикл WHILE выполняется до тех пор, пока условие выполняется. В основном используется, когда когда количество повторений цикла заранее неизвестно. Условие WHILE проверяется в начале цикла и в начале каждой его итерации, перед выполнением тела цикла. Блок внутри может ни разу не выполниться.

Пример:

declare
  v_index pls_integer := 1;
begin  
  -- от 1 до 3х
  while v_index <= 3
  loop    
     dbms_output.put_line('simple while: '||v_index);
     v_index := v_index + 1;
  end loop;
end;
/

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

Задача: Есть таблица, в которой две колонки имеют значение по умолчанию. Обе они не допускают вставки NULL-значений.
Необходимо вставить новую строку в эту таблицу, так чтобы эти две колонки заполнились значениями по умолчанию.
В insert нельзя использовать конкретные значения из определения таблицы.

Структура таблицы:
create table tab1
(
id number(30) not null,
ttype number(1) default 0 not null ,
tname varchar2(20 char) default 'none' not null
);

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

#sql #задача

Всем привет! Как и обещал, я записал видео про коллекции 😉

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

На всякий случай, скажу, что весь контент на этом канале создается лично мной, в том числе видео. Приятного просмотра 👍🏻

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

#видео #plsql #коллекции #nestedtables

Задача: Есть таблица, в которой две колонки имеют значение по умолчанию. Обе они не допускают вставки NULL-значений.
Необходимо вставить новую строку в эту таблицу, так чтобы эти две колонки заполнились значениями по умолчанию. В insert НЕ использовать конкретные значения из определения таблицы.

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

Запрос:

insert into tab1
  (id
  ,ttype
  ,tname)
values
  (1
  ,default
  ,default);

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

#sql #решениезадачи #default

FOR (цикл со счетчиком)

Классический цикл FOR с начальным и конечным значениями. Нач и конечн значения вычисляются один раз. Переменную индекса объявлять не надо. Конечное значение включается в диапазон.
Синтаксис приведен на скриншоте.
Reverse - обратный проход, причем “начальное значение” - не стартовое, а конечное, “конечное значение” - является стартовым, а не конечным :)

Примеры:

-- Цикл от 1 до 3-х (вкл). Шаг: +1
for i in 1..3 loop
   dbms_output.put_line('simple loop: '||i);
end loop;

-- Цикл от с 3-х до 1-го (вкл). Шаг: -1
for i in reverse 1..3 loop
   dbms_output.put_line('reverse loop: '||i);
end loop;

-- Цикл не выполнится
for i in reverse 3..1 loop
   dbms_output.put_line('reverse loop: '||i);
end loop;

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

WHILE (пока выполняется условие)

Условный цикл WHILE выполняется до тех пор, пока условие выполняется. В основном используется, когда когда количество повторений цикла заранее неизвестно. Условие WHILE проверяется в начале цикла и в начале каждой его итерации, перед выполнением тела цикла. Блок внутри может ни разу не выполниться.

Пример:

declare
  v_index pls_integer := 1;
begin  
  -- от 1 до 3х
  while v_index <= 3
  loop    
     dbms_output.put_line('simple while: '||v_index);
     v_index := v_index + 1;
  end loop;
end;
/

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

Задача: Требуется возвратить разницу между двумя датами в секундах, минутах, часах.

Запрос:

select ... diff_in_sec
       ... diff_in_min
       ... diff_in_hours
  from (select to_date('01.01.2020 12:00', 'dd.mm.YYYY hh24:mi') date2
              ,date '2020-01-01' date1
          from dual);

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

#sql #задача

Oracle Live SQL - онлайн инструмент от Oracle для выполнения SQL-запросов, написания PL/SQL-кода.

Если у вас нет под рукой БД, а вам необходимо где-то выполнить запрос, то этот инструмент вполне подойдет. Например, задачки из моего канала, можно выполнять в нем💡

Доступ к сервису дается на время. В начале сеанса создается пустая схема. Все что вы выполняете живет пока не закончилось время использования. Все созданные объекты можно выгружать в скрипты и сохранять в вашем личном хранилище на сайте. Так что всегда можно восстановить всю вашу работу.

Вход осуществляется под Oracle-учеткой. Регистрация проста и бесплатна.

Live SQL работает в Oracle Database 19c Enterprise Edition - 19.5.0.0.0.

Если вам интересно могу записать видео про этот сервис и как его использовать👍🏻

#ide

Задача: Требуется возвратить разницу между двумя датами в секундах, минутах, часах.

Принцип решения:
В Oracle разница между двумя датами - количество часов. Следовательно, полученное значение можно выразить в секундах, минутах, используя умножение.

Запрос:

select (date2 - date1) * 24 * 60 * 60 diff_in_sec
      ,(date2 - date1) * 24 * 60 diff_in_min
      ,(date2 - date1) diff_in_hours
  from (select to_date('01.01.2020 12:00', 'dd.mm.YYYY hh24:mi') date2
              ,date '2020-01-01' date1
          from dual);

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

#sql #решениезадачи #даты

Метки циклов
Циклу можно присвоить имя при помощи метки.
Метка заключается в двойные угловые скобки с обоих сторон.
Она располагается непосредственно перед конструкцией цикла.

Назначение меток:
1. Для понимания кода - циклы в циклах в циклах.
2. Для уточнения имени переменных.
3. Для использования в exit/continue для указания конкретного цикла.

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

Примеры:

{{main_loop}}
for i in 1..10 loop
   
   {{inner_loop}}
   for j in 1..10 loop
      if (...) then
          continue main_loop;
      elsif(...) then
         exit main_loop;
      end if; 
   end loop;

end loop;
/

К сожалению, телеграм-разметка никак не хочет пропускать угловые скобки 😒 Я заменил их на фигурные. Смотрите скриншот, как правильно использовать синтаксис.

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