@v1talys

alter table tab1
  add constraint check_vname
  check (not regexp_substr(vname, '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]{'||length(vname)||'}') is null);

или

alter table tab1
  add constraint check_vname2
  check(trim(translate(vname, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')) is null);

——-

@alexeionin

alter table tab1 add constraint tab1$vname$chk check (trim(translate(vname,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','                          ')) is null)

или

alter table tab1 add constraint tab1$vname$chk check (regexp_count(vname, '[^[:upper:]]|[^A-Z]')=0);

——-

Игорь Дроздов

alter table tab1 add constraint lat_upper check(regexp_like(vname, '^[ABCDEFGHIJKLMNOPQRSTUVWXYZ]+$'));

—--
@SergeyElagin

alter table tab1 add constraint lat_upper check(regexp_like(vname collate binary_ci,'^[A-Z]+$','c'));

—--
Я не буду комментировать решения коллег, лишь скажу спасибо за старания 🤝

Если вам понравился такой формат задач, когда решения присылаете вы - ставьте 👍
В этом, кстати, есть замечательная особенность, на одну и ту же задачу можно посмотреть с совершенно разных сторон.
Как говорится, "каждую задачу можно решить разными способами"

#решениезадачи #regexp

Задача:
Опять задачка на регулярки, давно крутилась у меня в голове.
Итак, дан запрос:

select count(*)
  from dual
 where regexp_like('A_^bc\D','[A-z]+');

nls_sort = binary (подвоха в этом нет),

Возможные варианты: 0️⃣ или 1️⃣.
Попробуйте, не выполняя в СУБД этот запрос, выбрать правильный вариант 🔮

Как всегда, смотрите решение в четверг 🎓

#задача

Задача: какой будет результат выполнения запроса:

select count(*)
  from dual
 where regexp_like('A_^bc\D','[A-z]+');

nls_sort равен binary ❗️

Решение: будет 1️⃣

Возможно кто-то шокирован этим ответом. Ведь в строчке присутствуют спец символы типа “_^\”. А в паттерне мы не указывали спец символы. Как так то?

Указание диапазона “A-z” содержит в себе подводные камни.
Может помнит кто-то из курса университета про ASCII табличку? Assembler? )
В общем, между символом “Z” и “a” есть еще шесть спец символов, которые как раз таки попадают в диапазон A-z (см. скрин).

Соответственно, регулярка их пропускает.

Эта особенность проявляется не только в Oracle, но и в других языках, например в Java.
Будьте внимательны 👀

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

#решениезадачи #regexp

Вакансия: Программист PL/SQL

Полное описание в вакансии.

Краткое:
- задачи OLTP-разработки (клики с сайтов/порталов), интеграционные решения точка-точка и средствами Informatica, работы с front-end на Oracle Application Express (APEX) и .NET, проектирование структур данных хранения, работа c legacy-решениями.
- ожидаемый уровень скилов Middle/Senior PL/SQL Developer .
- уровень з/п обсуждается индивидуально по итогам интервью.
- резюме (или краткое описание опыта разработки в свободной форме) просьба направлять на почту OFRuzaykina@rolf.ru Ольге Рузайкиной или в телегу https://t.me/Kapustin_Anton Капустину Антону.

#вакансия

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

Друзья, всем привет!
Пора выбирать тему для следующего видоса.

Для тех, кто вступил недавно. На нашем канале можно выбрать тему для обучающего видео, которое я сделаю.
А еще, вы можете предложить какую-то другую тему. Причем, тема может быть для любого уровня (Junior и выше).

Темы:
1. Отладка PL/SQL-кода.
2. Распараллеливаем выполнение задач через dbms_parallel_execute.
3. Практика. Логирование прикладных сообщений в БД (секционирование, типы сообщений и т.п.)
4. Оптимизация. Виды индексного доступа (unique scan, range scan и т.д,)
5. Полнотекстовый поиск средствами СУБД (почти как поиск в Яндексе =)
6. Что-нибудь другое - предлагайте в комментах или пишите в личку @denis_dbd. Не стесняйтесь 😉

Кстати, впервые решил сделать комментарии к посту. Посмотрим, как зайдёт 🤷🏻‍♂️

#видео

Если выбрали опцию "Что-нибудь другое", предложите ваш вариант в комментариях 😉

Пакет sys.standard

Довольно интересный системный пакет.
Содержит в себе определения типов, функций, именованных исключений и много чего другого. Можно сказать, все определение языка PL/SQL =)

Станем понятно, что за тип STRING или например, VARRAY. Чем они отличаются от VARCHAR2. Так же найдете забавные числовые типы, например SIGNTYPE.

Забыли какие есть именованные исключения типа NO_DATA_FOUND? Вам в этот пакет. Здесь они все перечислены и связаны с кодами ошибок (pragma exception init).

В некоторых версиях СУБД можно увидеть в тексте спецификации пакета забавные комменты, которые уже “не вырубить топором”.

Рекомендую заглянуть в него просто для расширения кругозора.

#пакеты

Друзья, всем привет!

В личку поступают вопросы от взволнованных подписчиков о снижении частоты постов.
Спешу успокоить, все в порядке 👌
Частота действительно слегка снизилась, но на то есть объективная причина.

Максимум усилий направлено на завершение курса по основам PL/SQL 🎓
Хочется сделать максимально удобный и качественный продукт.
Естественно, о нем я еще расскажу подробней чуть позже.

А пока, всем хороших выходных! 👍

B-tree индексы

Опрос показал, что многих интересует тема индексного доступа.
Прежде чем начать переходить к планам и обходам, необходимо затронуть тему физического устройства индексов.

Существует два основных типа физической организации (исключаем экзотику):
1. B-дерево (balanced, big).
2. Bitmap-карты.

Первый тип наиболее универсальный.
Второй для своих узких задач. Его мы рассматривать не будем, да простят меня любители DWH 😌

B-tree индекс
Используется в большинстве систем. Представляет собой сбалансированное дерево высотой не более четырех уровней (больше я не встречал).
1. В узлах ключи по которым происходит поиск нужного ветвления -> узла -> листа.
2. В листьях списки из значений + rowid. Rowid - самый быстрый способ получить доступ к строке таблицы.
3. Листья указывают друг на друга - типа двусвязного списка.
4. Null-значения не хранятся в индексе (значения всех столбцов дб is null).

#архитектура #индекс

Всем привет!

Коллега вчера поделился забавным куском кода с "фичей" Oracle 😊

Задача: Как думаете сколько записей будет в таблице test_tab1?

Думаете "больше чем ноль строк"? Нет =)
Выполните в СУБД посмотрите на результат.

create table test_tab1(a number);

begin
  for i in 1..2 loop
    insert into test_tab1 values (i);
  end loop
  commit;
  rollback;
end;
/

select * from test_tab1;

Отключаю коменты к этой задаче, чтобы не было спойлеров. Присылайте в личку объяснения, почему так происходит.
Опубликую ответ на этой недели.

#задача

Задача: Как думаете сколько записей будет в таблице test_tab1?
полную постановку смотрите в посте вторника.

Ответ:
Записей в таблице, действительно, не будет.
Все потому, что мы сталкиваемся с багом/фичей Oracle 🤷🏻‍♂️

Если внимательно посмотреть на код, то можно увидеть пропущенную ";" после loop.
Таким образом, "commit" выступает как метка цикла (label).
Сразу после цикла происходит откат вставленных строк (rollback).

Мы привыкли, что метка цикла (<< название >>) должна быть задана перед for.
Потому, код в примере выглядит немного диковато, зачем название метки в конце без указания её в начале 🤓
Еще и зарезервированное слово можно использовать...

Практически, все, кто написал мне, были правы. Спасибо, за решения 👍

Понравилась задачка?

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

Index Scan (Сканирование индексов)

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

Как я уже отмечал в первом посте, речь пойдет только про B-tree индексы.

Виды сканирования (scan):
▫️ Unique (поиск по уникальному ключу)
▫️ Range (поиск по диапазону)
▫️ Skip (поиск в индексе с пропусками)
▫️ Full и Fast Full (полное сканирование индекса)
▫️ Index join (соединение результатов индексного поиска)

В следующих постах, буду раскрывать подробности.

#оптимизация #индекс

Оптимизация "на ровном месте"

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

Судя по всему, необходимо раскрыть это чуть более подробней. Проще всего на примере.

create table demo1(
id number,
nname varchar2(200 char)
);

-- вставим 10К записей
insert into demo1 select level, level from dual connect by level <= 10000;

-- создадим уникальный индекс
create unique index demo1_id_unq on demo1(id);

-- соберем статистику
call dbms_stats.gather_table_stats(ownname => user, tabname => 'demo1');

Запросы:
-- 1. будет задействован только индекс
select id from demo1 where id = 1000;

-- 2. будет задействован индекс + таблица (по rowid)
select id, nname from demo1 where id = 1000;

1-й запрос: искомое значение найдено в индексе (это ключ), другие поля для возврата результата не нужны.

2-й запрос: помимо поля id, которое можно получить из индекса, еще нужно поле nname, которое достать можно исключительно сходив в таблицу по rowid. В плане запроса шаг с table access by index rowid.

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

Планы запросов на скриншоте 🖼

#оптимизация #индекс

Index Range scan
Индексное сканирование по диапазону.
Очень часто используемый вид сканирования. Применяется при поиске по диапазону значений или при равенстве из неуникальных индексов.
Поиски типа:
where col1 between или where col1 > … или where col2 = ...

Возможен при выполнении условий:
1. В условиях запроса используется один или несколько лидирующих столбцов индекса.
2. Индекс может быть уникальным или обычным.

Поиск в индексе происходит от узла в котором находится стартовое значение диапазона (берется из запроса). Затем происходит сканирование всех значений после этого стартового значения, в соответствии с условием. Если надо, алгоритм переходит с листа на лист, не поднимаясь к узлу. Помните, что листы связаны между собой как двусвязный список. Поэтому возможны любые направления прохода по индексу asc/desc.

В плане запроса: INDEX RANGE SCAN

Данные извлекаются в сортированном порядке. Это исключает доп сортировку при операциях group by/order by по ключу.

Примеры ниже ⬇️

Пример 1.
create index emp_department_ix on employees (department_id);

select * from employees t where t.department_id = 10;

Будет range scan. Казалось бы, почему тут не unique scan? Потому что индекс emp_department_ix не уникальный. И в листах у него не уникальные значения, а значит, добравшись до искомого листа, придется проходить по диапазону равному = 10. Таких элементов будет больше чем “1”. Потому и применяется range scan.

Пример 2.
select * from employees t where t.employee_id <= 130;

В плане фигурирует range scan по unique index - emp_emp_id_pk. Казалось бы, индекс уникальный, почему не unique scan? Потому, что в where задан диапазон - меньше либо равно. При этом не важно, уникальный индекс или нет. При сканировании придется проходить листы/значения меньше числа 130 включительно.


#оптимизация #индекс

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

create table t1(a number, b number);
create table t2(a number, b number);

Пример данных:

T1:
a   b
1   1
2   2
2   2
3   3
4   4

T2:
a   b
1   1
2   2
3   3
3   3
4   4

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

Кстати, эта задача со свежего тестового задания “Совкомбанк”. Признаться, где-то я эти задачи уже видел... лет 10 назад 😉

У меня давно бродит идея собрать тестовые задания, которые вы получаете и создать некий банк заданий с решениями. Можно разместить их в github. Кое-что уже есть - Магнит, Беркут, Ингосстрах и др. разных лет.

Если мысль нравится и готовы поделиться заданиями/решениями - отправляйте в чат или личку. Размещу в github с сохранением авторства.

#задача

Index skip scan

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

Проще всего объяснить на примере таблицы employees.
Есть индекс:

create index emp_name_ix on employees (last_name, first_name);

Выполняется запрос:

select * from employees t where t.first_name = 'Mark';

В предикатах не задан лидирующий столбец (“last_name”) индекса.
Скорее всего, будет использоваться Index Skip Scan.

Как же он работает?
Для лидирующего столбца получаются все уникальные значения. Дальше для каждого уникального значения выполняется range scan с учетом значения заданных столбцов.

Продолжение ⬇️

Возвращаясь к нашему примеру.

-- Уникальных значений 102.

select count(distinct t.last_name) from employees t;

Будет построено 102 условных поисковых дерева и по каждому будет совершен поиск по (last_name = ‘i-ое last_name’ and first_name = ‘Mark’).

Если лидирующий столбец имеет большое количество уникальных значений, скорее всего, index skip scan выбран не будет.
Но тут надо понимать, что лидирующим столбцом в B-tree индексах, лучше использовать столбец, как раз таки, с большим разнообразием значений.

Из практики, я не припомню, когда бы index skip scan был полезен. Разработчики старательно избегают его. Наличие skip scan - это повод для оптимизации.
Если есть какие-то узкие кейсы его полезности, напишите в комментариях. Всем будет полезно.

#оптимизация #индекс