Задача о трансформации запроса
Постановку смотрите в посте вторника.

Немного теоретической части
Любой запрос при hard parse проходит через стадию “оптимизации” для построения плана запроса. Одним из этапов оптимизации является трансформация запроса.

Например, запрос

select * from tab1 where col1 = … or col2 = … 

при определенных условиях будет преобразован в запрос вида:

select * from tab1 where col1 = …
union all
select * from tab1 where col2 = … and col1 not in ...

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

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

1️⃣ В плане запроса в блоке Outline Data есть намек, на то, что таблица была исключена из JOIN - ELIMINATE_JOIN(@"SEL$1" "D"@"SEL$1")

2️⃣ Если хочется копнуть глубже. Можно выполнить трассировку оптимизатора и посмотреть через какие этапы прошел запрос при построении плана. В результате будет файл с данными на сервере СУБД.

Сформировать такой файл можно разными способами. Приведу только один (запрос должен выполняться впервые):

alter session set events '10053 trace name context forever';
alter session set tracefile_identifier='PLAN_TRC_EXAMPLE1';

select t.first_name
  from hr.employees t
  join hr.departments d on d.department_id = t.department_id
 where t.first_name like 'Alex%';

alter session set events '10053 trace name context off';

В результате выполнения в каталоге на сервере будет создан trc-файл с меткой PLAN_TRC_EXAMPLE1.
Заглянув в секцию с преобразованиями, можно заметить интересную картину (см. скриншот выше ⬆️).

⚠️ для запросов, которые уже выполнялись используйте - dbms_sqldiag.dump_trace.

На курсе по оптимизации будем разбирать подобные вопросы 🎓

Обсудить в чатике

#решениезадачи #оптимизация
Oracle Developer

Шутка в тему оптимизации 😉

Всем хорошей пятницы и выходных 🎊

#юмор
Oracle Developer

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

Рад сообщить, о старте пятого потока курса “Oracle PL/SQL.Основы”.

Он будет полезен: QA-инженерам, разработчикам, аналитикам.

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

🔹22 видео с теорией;
🔹18 практик = единый проект;
🔹13 онлайн встреч в Zoom с разбором вопросов;
🔹закрытый телеграм-канал;

Практика построена таким образом, чтобы вы с нуля, шаг за шагом, создали API на PL/SQL для прототипа платежной системы. Начав с анонимных блоков вы закончите своим мини-фреймворком для Unit-тестирования. Пример.

Подробности и программа - здесь. Отзывы - здесь.

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

⚠️ Группа 10 человек, 5 мест уже забронировано.

#plsql_basic

Задача

Дан триггер:

create or replace trigger my_tab_seq_tg
   before insert on my_tab
   for each row
begin
   if inserting then
      if :new.seq_id is null then
         select some_seq.nextval into :new.seq_id from dual;
      end if;
    end if;
end;
/

Вопросы: все ли в порядке? можно ли его как то улучшить/изменить?

Уровень сложности: легкий

#задача

Ревью триггера
Постановка в посте вторника.

Исходный код триггера:

create or replace trigger my_tab_seq_tg
   before insert on my_tab
   for each row
begin
   if inserting then
      if :new.seq_id is null then
         select some_seq.nextval into :new.seq_id from dual;
      end if;
    end if;
end;

Начнем-с.
Триггер - это PL/SQL-код, который вызывается при наступлении определенных событий, указанных в его определении.
В нашем случае: триггер на операцию insert на таблицу my_tab, срабатывает для каждой новой строки (for each row) до вставки (before).
В теле триггера происходит проверка с использованием функции inserting происходит ли вставка. Если поле новой строки :new.seq_id не заполнено до начала операции, то значение берется из последовательности some_seq.

Опираясь на название и код, триггер предназначен только для генерации ID для некоего поля, если оно не заполнено.
С этим понятно.

Возникают некоторые моменты.
1️⃣ Зачем проверять, что происходит вставка (INSERTING), если триггер срабатывает только на INSERT? Ответ - абсолютно бессмысленно.

2️⃣ Зачем использовать SQL-запрос “select some_seq.nextval into :new.seq_id from dual;” для получения следующего значения, что приведет к переключению контекста между SQL и PL/SQL-машинами?
Нет никакого смысла. Это вносит задержку, пусть и минимальную, в операцию insert. Такая конструкция была справедлива для старых версий Oracle, но с 11й вполне можно использовать:
:new.seq_id := some_seq.nextval

3️⃣ Проверку “ :new.seq_id is null ”, можно сократить до :new.seq_id := coalesce(:new.seq_id, some_seq.nextval).
А можно воспользоваться конструкцией when в условиях срабатывания: when (new.seq_id is null).

Итоговый код триггера:

create or replace trigger my_tab_seq_tg
  before insert on my_tab
  for each row
  when (new.seq_id is null)
begin
  :new.seq_id := some_seq.nextval;
end;
/

Есть еще интересная холиварная тема: а зачем нам такой триггер в принципе? Коллеги в чатике отчасти это уже обсудили.

Это, всего лишь, один из примеров, которые мы разбираем на курсе по Oracle PL/SQL. Если вы пишете или читаете код на PL/SQL и не раскусили суть задачи, вполне вероятно, что стоит записать на курс и добить пробелы.
⚠️ Осталось 5 мест. Если сейчас нет возможно оплатить, можно забронировать место - пишите в личку.

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

Задача

Вопрос из теста в курсе PL/SQL.Основы. Рассчитан на изучающих PL/SQL.

На скриншоте представлен код пакета. Вызывается процедура пакета:

begin
  my_pack.say_hello();
end;
/

Что будет выведено в буфер вывода? Варианты ниже ⬇️

Разбор, как всегда, в четверг 🎓

#задача

Задача на переменные пакета и его состояние

Постановка в посте вторника

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

В нашем случае создается пакет my_pack. В теле пакета объявлена внутренняя глобальная переменной g_hello и задан блок инициализации.

Глобальная внутренняя переменная видна во всем теле пакета в любой его части.

Блок инициализации выполняется, при первом обращении к пакету для инициализации его состояния. Так же может выполниться повторно, если состояние пакета было сброшено (например, перекомпиляция).

Порядок инициализации в пакете: значения в глобальных объектах, затем блок инициализации.

На момент выполнения процедуры say_hello значение в переменной будет ‘Hello’.

Правильный ответ: А

Это самые основы. Эти и другие темы будем разбирать на курсе PL/SQL.Основы старт 9.09 🎓

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

Всем хорошей пятницы и выходных 🎊

#юмор
Oracle Developer

Задача

Вам на ревью поступил следующий PL/SQL-код:

create or replace function get_text(p_id t1.id%type)
return varchar2
is
  type my_type_cur is ref cursor;
  v_curs my_type_cur;
  v_text t1.text%type;
begin
  open v_curs for
    select text from t1 where id = p_id;

  fetch v_curs into v_text;

  close v_curs;

  return v_text;
end;
/

Что делает данный код? Корректный ли он?
Как вы считаете, можно ли его переписать?
Действительно ли нужно использовать курсор?

Подобные вещи мы разбираем на 3х месячном курсе PL/SQL.Основы. Старт 9 сентября.

Обсуждение, как всегда, в четверг 🎓

#задача
Oracle Developer

код к анализу ниже ⬇️

Ревью функции с курсором

Постановка: смотрите пост вторника.

Анализ
Кратко: я бы такой код не пропустил на ревью.
см код выше
1️⃣ Стр 2. Возвращается тип без привязки к колонки таблицы. Нет причин не использовать жесткую привязку к колонке text.
2️⃣ Стр 4. Избыточное объявление курсора. Можно использовать системный тип sys_refcursor.
3️⃣ Стр 13 и 16. Закрывать курсор надо 100%. В штатном режиме он закроется. А вот в случае возникновения исключения - нет закрытия. Ошибка неопытного курсоро-пользователя. Добавляется блок exception с проверкой на открытость курсора и его закрытием if v_curs%is_open then close v_curs; end if;
4️⃣ Но это частности. Основной вопрос к коду: зачем использовать явный курсор? Ответ: не имеет никакого смысла. Код можно переписать, например, так:

function get_text_new(p_id t1.id%type)
return t1.text%type
is
  v_text t1.text%type;
begin
  select max(t1.text)
    into v_text
    from t1 where t1.id = p_id;
   return v_text;
end;
/

▫️В данном варианте, не важно id уникально или нет (как в первом так и во втором будет рендомно отдавать какую-то строку).
Есть ли значение по переданному id или нет. Поведение осталось прежним.

▫️Должна ли вызывающая среда знать, о том что строка не найдена? Не имея, каких-либо доп вводных ответить на этот вопрос сложно.

▫️Можно ли использовать result_cache? Определенно сказать сложно. Если таблица t1 постоянно меняется (это не справочник), то нет. Если меняется редко - можно попробовать. Опять же при условии, что тип колонки text проходит по ограничениям result cache.

▫️Если функция используется в SQL, то стоит подумать насчет pragma udf, deterministic (осторожно).

Мало чего ясно, но очень хочется понимать?
Есть возможность с 9-го сентября ворваться в магию PL/SQL 🎩
Напишите мне, если хотите забронировать место на новый поток. Оплатить можно непосредственно перед стартом.

Палец вверх, если задача понравилась. А еще можно перетереть в чатике.

#решениезадачи #cursor
@oracle_dbd

Всем хорошей пятницы и выходных 🎊

#юмор
Oracle Developer

Задача. Ревью анонимного PL/SQL-блока

И снова ревью кода.
Кстати, отличная практика проводить code review pull request’ов:
🔸 всякая дичь не попадает в prod;
🔸 команда шарит экспертизу и т.п.

Чем дальше от dev-стенда найдена ошибка, тем она дороже. Найти багу в prod совсем не то, что найти в test-окружении. Однако, это лирика, перейдем к нашему примеру.

declare
  type name_salary_rt is record(
     name   varchar2(1000)
    ,salary number);

  type name_salary_aat is table of name_salary_rt index by pls_integer;

  l_employees name_salary_aat;
begin
  execute immediate q'[select first_name || ' ' || last_name, salary
           from employees
          order by salary desc]' bulk collect
    into l_employees;

  for indx in 1 .. l_employees.count loop
    dbms_output.put_line(l_employees(indx).name);
  end loop;
end;
/

Что делает данный код? Корректный ли он? Как вы считаете, можно ли его переписать? Пропустили бы на code-review?

Подобные вещи мы разбираем на 3х месячном курсе PL/SQL.Основы. Старт 9 сентября.
Осталось 2️⃣ места 🔥

Обсуждение, как всегда, в четверг 🎓

#задача
@oracle_dbd

Ревью функции с динамическим SQL. Решение

Постановка: смотрите пост вторника.

Анализ
Кратко: я бы такой код не пропустил на ревью.

Такое ощущение, что это писал человек, который вчера узнал про коллекции и динамический SQL и начал применять это везде.

Начнем с требуемого функционала: нужно вывести определенные поля из таблицы employees отсортированной по полю salary.

Весь код можно упростить до:

begin
  for r in (select first_name || last_name name
              from employees
             order by salary desc) loop
    dbms_output.put_line(r.name);
  end loop;
end;
/

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

Пример упрощенный, поэтому вместо “чего-то деланья”, выводится текст. Как правильно заметили участники чатика, было бы неплохо отделить мух от котлет. Вывод данных от получения данных. Но это уже совсем тонкая материя, не для этого уровня задачи.

Хотите научиться писать PL/SQL-код, за который не будет стыдно?
Уже сегодня в 20:00 запускается очередной поток курса “Основы PL/SQL”. Не упустите свой шанс 😉

Палец вверх, если задача понравилась 👍
Обсудить в чатике 🗣

#решениезадачи #dynamicsql #cursor
@oracle_dbd

Коллеги, всем привет!

Не могу не поделиться ☺️
Вчера стартанул очередной поток курса "Основы Oracle PL/SQL". 10 человек отправились в трехмесячное увлекательное плаванье ⛵️

—-
Сегодня пятница и немного баянного юмора не помешает😉

Всем хороших выходных 🎊

#юмор
Oracle Developer

Вакансии в Дом.рф

Из рубрики "а вдруг!" 😄

Компания ДОМ.РФ в поисках Team Lead’a и разработчиков со знанием PL/SQL и Новой Афины

Предстоит:
• Проектирование и разработка бэк-офисной системы Банка (ИСУБД «Новая Афина»);
• Консультирование аналитиков и тестировщиков;
• Участие в выборе оптимальных решений, оценка работ;
• Участие в разборе инцидентов и исправление дефектов программного обеспечения;
• Подготовка релизов, сред тестирования функционала;
• Разработка автотестов;
• Ответственность за качество продукта.

Наши ожидания:
• Опыт работы в роли Разработчика на PL/SQL от 1.5-года;
• Знание архитектуры СУБД Oracle;
• Знание ИСУБД «Новая Афина».

Мы предлагаем:
• Офис в Москве или полная удаленка в пределах РФ;
• ЗП до 500 к gross;
• Расширенная программа ДМС, включая стоматологию;
• Компенсация абонемента в фитнес-клуб и обучения английскому языку (любые клубы и школы);
• Специальные условия по кредитам и ипотеке от Банка ДОМ.РФ для сотрудников.

Для связи: @technokotya @ValentinaKozyrevait

#вакансия #НоваяАфина
@oracle_dbd

Всем привет!

Задача 2в1. Освежим немного знания по исключениям и транзакциям.

Дана таблица tab1.

create table tab1(
  id number(38)
); 

содержит одну строку, других сессий в БД нет.

Выполняется PL/SQL-блок:

declare
  v_cnt number(30);
begin
  insert into tab1 values (1);
    
  v_cnt := 1/0;
  
  dbms_output.put_line('1');
  
exception 
  when others then
    select count(*) into v_cnt from tab1;
    dbms_output.put_line(v_cnt);
end;
/

Какое число будет выведено в буфер вывода? Голосуем ⬇️
Рекомендую сначала ответить, затем уже запустить код в БД.

Обсуждение, как всегда, в четверг 🎓

#задача
Oracle Developer