Задача: постановку смотрите в посте вторника.

Решение:

select level
       ,t.name
       ,t.year
       ,prior name parent_name
       ,prior year parent_year
  from example t
 connect by prior t.name = t.p_name
    and prior t.year = t.p_year
   start with t.p_name is null and t.p_year is null;

* level - уровень иерархии.
* name, year - поля текущего уровня иерархии.
* prior - через конструкцию prior можно обращаться к полям родителя.
* соединение предка с потомком:
в соединении участвуют два поля name/year, соответственно, надо через AND перечислить оба поля.
* start with - нужен для указания с какого узла мы разворачиваем нашу иерархию. В данном случае, стартуем с узлов, у которых нет предков.

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

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

Всем привет!
Отпуск закончился 🌴
Я с новыми силами берусь за любимое дело.
Итак, поехали 😉

План запроса - это то, как СУБД будет выполнять наш запрос.
А именно:
1. Какие структуры хранения будет использовать;
2. Как будет извлекать из них данные;
3. Как будет соединять таблицы;
4. Как будет сканировать партиции;
5. Как будет выполнять другие необходимые действия.

sql_id - это уникальный идентификатор SQL-запроса.
plan_hash_value - это уникальный идентификатор плана SQL-запроса.
У одного запроса может быть несколько планов выполнения.

Планы запросов формирует стоимостной оптимизатор. За основу берется хранящаяся статистика по объектам, участвующих в запросе. А также подсказки разработчика (hints, хинты).

Статистика - это регулярно собирающаяся информация по объектам БД. Например: количество строк, количество уникальных значений, глубина индексного дерева и прочие характеристики.

Как читать план запроса?
Обсудим в одном из следующих постов.

#теория #оптимизация

Задача: имеется таблица. Заполнена данными. Выполняется запрос. План запроса показан на рисунке.
Что в этом запросе некорректно, на какие мысли наталкивает план выполнения, как можно исправить?

Скрипты:

create table my_order(
  order_id number,
  sernum varchar2(100 char),
  order_date date
)
partition by range (order_date)
interval (interval '1' month)
(
  partition pmin values less than (date '2008-01-01')
)
enable row movement;

alter table my_order add constraint my_order_pk 
primary key (order_id, order_date) using index local;

insert into my_order 
select level, level, sysdate - level 
  from dual connect by level <= 1000;
commit;

select * from my_order t where t.order_id = :x;

Смотрите объяснения в посте в четверг 🎓

#sql #задача

план запроса

You asked…

У меня вопрос по поводу Varray (это один из типов коллекций в Oracle).
Зачем именно они нам нужны? Можете ли вы привести пример их использования?
Было бы интересно узнать ваше мнение.

...and we said

Я никогда их не использую. Я использую nested tables в PL/SQL-коде.
Также, никогда не использую коллекции в качестве столбцов физических таблиц.
Varray лимитированы размером и их не очень удобно использовать в коде.

Подробности в статье Тома

⚠️ От себя добавлю. В реальной жизни, кроме как на собеседованиях тип Varray мне не встречался. Использовать в качестве типа столбца таблицы - является плохим тоном по разным причинам. В PL/SQL-коде nested tables хватает более чем.

#asktom #коллекции #varray

Задача: смотрите постановку в посте вторника.

Решение:
Довольно частая ошибка ДБД. Таблица секционированная, однако ключ секционирования в запросе не использован. СУБД вынуждена сканировать все партиции. Чем больше у нас партиций, тем больше трудоемкость этой операции.

В плане такие кейсы обнаружить достаточно просто. Стоит обратить внимание на столбцы:
Pstart - с какой партиции начинается сканирование.
Pstop - какой партицией заканчивается.

В нашем исходном плане, Pstart = 1, Pstop = 1048575 (условный максимум).
Т.е. сканирование начинается с первой партиции и заканчивается самой последней.
Partition Range All - также, непрозрачно намекает на сканирование всех партиций.

Наша задача не забыть добавить ключ секционирования в запрос. Где это значение взять? Уже другой вопрос. Существуют различные методики. Может быть, как-нибудь напишу.

Pstart - должен начинается точно не со значения 1. Это может быть KEY(AP) или номер одной из последних секций. Зависит от того как вычисляется стартовая\конечная секции.

#решениезадачи #секционирование

План запроса с добавлением ключа секционирования.

* Pstart = KEY
* Pstop = 1048575
* Partition Range Iterator.

#sql #решениезадачи #оптимизация #секционирование

Отвлечемся на время от оптимизации.

Глобальные временные таблицы (GTT) - таблицы, используемые для хранения данных на время транзакции или сеанса.

Память для хранения данных выделяется во временном ТП (TEMP). При манипуляции с этими данными генерируется крайне мало redo-информации.

Очень часто происходит путаница по срокам жизни таблицы и данных.
GTT - создаются заранее, а не по ходу выполнения приложения. Их не нужно создавать в каждой сессии. А нужно лишь заполнять данными. Данные между сессиями не видны.

Существует два типа GTT:
1) Хранят данные до commit’a (on commit delete rows)
2) Хранят данные до окончания сеанса (on commit preserve rows).

Тип можно задать только при создании.

Синтаксис:

create global temporary table name_gtt 
(
…
)
on commit delete rows;

или

create global temporary table name_gtt 
(
…
)
on commit preserve rows;

Есть ограничения по использованию, но на практике это никогда не мешает. О практическом использовании напишу в следующем посте.

#временныетаблицы

Задача: Есть таблица my_table. На момент вставки она пустая. Что выведет select?

Структура таблицы:

create global temporary table my_table
(
  id number(30) not null
)
on commit delete rows;

Скрипт:

insert into my_table values(1);
insert into my_table values(2);

grant select on my_table to some_user;

select count(*) from my_table;

Смотрите объяснения в посте в четверг 🎓

#sql #задача

Длинный список в условии IN

You asked…
Сколько элементов может быть в списке WHERE x IN (,,,)?

Я придумал два способа обхода:
1. Динамически формировать список через OR.

x = el_1 OR x = el_2 OR x = el_3 OR …

2. Динамически создавать таблицу, заполнять её элементами и использовать в условии where, но создание таблицы может занимать до 15 секунд.

...and we said
Лимит элементов в конструкции IN - 1000.
ORA-01795: maximum number of expressions in a list is 1000

Первый способ, не стоит использовать, т.к. будет порождать лишние запросы в кэше, будут hard parse и т.д.

Второй способ, плох тем, что вы каждый раз выполняете DDL при создании таблицы. Это дорогая операция.

Решение: использовать временные таблицы. Они создаются один раз, но заполнять их можно в соответствии с вашими требованиями.

insert into GTT_TAB values ( ... );
select * from T where (a,b) in (select x,y from temp);

Подробности в статье Тома

#asktom #временныетаблицы

Задача: смотрите постановку в посте вторника.

Решение:
1) Таблица является глобальной временной. Данные из неё удаляются по commit’у. О чем говорит опция создания - on commit delete rows;

2) Команда grant коммитит транзакцию. Соответственно, удаляет строки из таблицы my_table.

Итого: запрос вернет “0”.

На этом простейшем примере показана работа временных таблиц.

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

#sql #решениезадачи #временныетаблицы

Использование временных таблиц

1. Сохранение промежуточных этапов расчетов (DWH, DSS), которые нужны только внутри сеанса/транзакции.
2. Реализация нетривиальной логики зависящей от времени хранения данных (например, пользовательская нумерация транзакций).
3. Передача данных между приложением и БД.
4. Кэширование данных на время сеанса/транзакции.

Есть некоторые особенности в части оптимизации запросов с использованием GTT.
После вставки данных в GTT оптимизатор не обладает актуальной статистикой по количеству строк в неё. “На лету” собирать статистику - не вариант по разным причинам.
На помощь приходит хинт cardinality, который подсказывает оптимизатору порядок строк хранящихся в GTT.

Пример:

select /*+ cardinality(t2 1000) */
   from table t1
   join gtt_tab t2 on …

В Oracle18c появились Private Temporary Table, немного другая концепция. Если вам интересно, могу как-нибудь запилить видос по ним.

В целом, GTT очень полезные таблицы - их нужно знать и применять.

#временныетаблицы #cardinality

Private Temporary Table
Решил не откладывать в долгий ящик информацию по приватным временным таблицам.

Приватные временные таблицы (PTT) - таблицы, создающиеся и хранящие данные на время сеанса или транзакции. Они создаются “на лету” по ходу выполнения кода.

Хранение структуры и данных происходит целиком в памяти. Redo и Undo не генерятся.

Два типа PTT:
1. Существуют до commit’a (on commit drop definition)
2. Существуют до окончания сеанса (on commit preserve definition).
Тип можно задать только при создании.

Название должно начинаться с префикса - ORA$PTT_

Синтаксис:

create private temporary table ora$ptt_***(
    column_definition,
    ...
) on commit
  [drop definition | preserve definition];

#временныетаблицы

Задача: Выполняется скрипт. В нем есть ошибка. Где она?

Скрипт:

create private temporary table ora$ptt_my_tab
(
  id number(30)
)
on commit drop definition;

insert into ora$ptt_my_tab values(1);
insert into ora$ptt_my_tab values(2);

commit;

select count(*) from ora$ptt_my_tab;

———-
Рекомендую не выполнять код, а решить эту задачу "в уме".

Смотрите объяснения в посте в четверг 🎓

#sql #задача

Задача: смотрите постановку в посте вторника.

Решение:
1. Таблица является приватной временной. Сама таблица и данные из неё удаляются по commit’у (опция создания - on commit drop definition).

2. Команда commit зафиксирует транзакцию, начавшуюся после создания таблицы, и удалит её вместе с данными.

3. Команда select не выполнится из-за ошибки "ORA-00942: table or view does not exist”. Таблицы то уже нет.

Итого: команда select не выполнится из-за отсутствия таблицы.

На этом простейшем примере показано свойство приватных временных таблиц.

Ставьте 👍 , если вам понравилась задачка.

#sql #решениезадачи #временныетаблицы

Private Temporary Table

Всем привет!

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

Приятного просмотра!

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

#видео #временныетаблицы

Задача:
Как получить версию СУБД, к которой вы подключены при помощи SQL, PL/SQL?
Существует несколько способов. Попробуйте найти хотя бы два.

Смотрите объяснения в посте в четверг 🎓

#задача

Профилирование PL/SQL-кода

Всем привет!

Месяца полтора назад вы проголосовали за видео с профилированием PL/SQL-кода.
Сделано 😉

В этом видео, я расскажу, что такое профилирование PL/SQL кода, как его производить, зачем оно нужно, как читать отчет профилировщика.
Будет полезно тем, кто не понимает, как найти узкое место в PL/SQL коде, где он тормозит, как его можно оптимизировать.

Приятного просмотра!

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

#видео #профилирование #оптимизация

Задача: Как получить версию СУБД, к которой вы подключены при помощи SQL, PL/SQL?

Решения:
1 способ

declare
  v_version varchar2(1000 char);
  v_compatibility varchar2(1000 char);
begin
  dbms_utility.db_version(version => v_version, compatibility => v_compatibility);
  dbms_output.put_line('Version: '||v_version);
  dbms_output.put_line('Compatibility: '||v_compatibility);  
end;

2 способ

select * from v$version;

3 способ
Для привелегированных пользователей

select version from v$instance;

4 способ

select * from product_component_version;

5 способ

begin
  dbms_output.put_line(dbms_db_version.version||'.'||dbms_db_version.release);
end;

Есть и другие способы.
——
Ставьте 👍 , если вам понравилась задачка.

#решениезадачи #версиясубд

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

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

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

Создал голосовалку с перечислением тем.
1. Как посмотреть план запроса.
2. Что такое “compound”-триггеры.
3. Что такое “instead of”-триггеры.
4. Секционированные таблицы.
5. Условная компиляция в PL/SQL.
6. Что-нибудь другое (можете смело писать в личку тему).

Запилю видосы по трем темам, набравшим большее количество голосов.

Выбирайте 👍