Длинный список в условии 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. Что-нибудь другое (можете смело писать в личку тему).

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

Выбирайте 👍

Итоги голосования

Всем привет!

Судя по опросу, на момент написания поста, рейтинг опроса по видео такой:
1️⃣ Как посмотреть план запроса.
2️⃣ Условная компиляция в PL/SQL.
3️⃣ Секционированные таблицы.

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

Спасибо за активность на канале, за ваши голоса 👍

Ждите, скоро все будет 😉

Задача:
Имеется текст, содержащий JSON. Он может быть как сложным так и простым.
Необходимо проверить является ли текст валидным JSON. Решение может быть как на SQL, так и на PL/SQL.
Примечание: задача для версий выше Oracle 11g.

Пусть для примера будет такой JSON:

{
  "name": "value",
  "list": [
    "1",
    "2",
    "3"
  ]
}

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

#задача

Вызов внешнего RestAPI

You asked…
Мне необходимо вызывать внешний Rest-сервис. Как это лучше всего организовать? Приведите, пожалуйста, пример.

...and we said
Если вам необходимо вызывать из базы данных внешние сервисы необходимо:
1. Настроить доступ из БД через ACL по определенному адресу/порту.
2. Затем используя пакет utl_http организовать логику работы с внешним сервисом.

Пример такой организации вы можете посмотреть по ссылкам в статье.

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

---
От себя добавлю. Это решение подходит для систем построенных на серверной логике. Если же у вас трехзвенная архитектура со средним слоем на Java-приложении (например), то лучшее решение - организовать запросы во внешние сервисы именно там.

#asktom #restapi #acl #utl_http

Задача:
Имеется текст, содержащий JSON. Он может быть как сложным так и простым.
Необходимо проверить является ли текст валидным JSON. Решение может быть как на SQL, так и на PL/SQL.

Решение:
Для SQL:

select nvl(max(1),0) is_json
  from dual
 where '{ "name": "value", }' is json;

Для PL/SQL

begin
  if '{ "name": "value"} ' is json then
    dbms_output.put_line('It is json');
  end if;
end;
/

В примере, я использовал маленький JSON, чтоб влезло в пост.

В Oracle 12c появился набор функций\процедур для работы с JSON-форматом. Одна из полезных функций это IS JSON, которая возвращает true - если текст в формате JSON, false - если нет.

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

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

Правила именования объектов

Информация будет полезна тем, кто только недавно столкнулся с СУБД Oracle.

Существует ряд особенностей и ограничений в нейминге объектов и идентификаторов:
* регистронезависимые.
* не должны начинаться с цифры.
* иметь длину до 30 байт (до версии 12c) или не более 128 (12c+).
* не могут содержать пробелы, недопустимые символы “-”, “%”.
* могут включать символы «$», «_» и «#»;
* не должны быть ключевыми словами языка.

Системное представление v$reserved_words поможет определить, является ли слово ключевым в СУБД. Думаю, что подсветка слов в IDE также основана на этом представлении.

Как именовать объекты (переменные, таблицы, индексы и т.д.) договариваются внутри команды/компании. Далее следуют этим правилам для единообразия кодовой базы, более легкой поддержки и создания кода.

Однако, есть некоторые условности и договоренности в мире разработки Oracle. Об этом в следующий раз.

Ради интереса, запускаю голосовалку. Не поленитесь - жмакните кнопку 🙋🏻‍♂️

#именование

Задача:
1. Необходимо создать таблицу с названием "Моя Таблица" с тремя колонками любого типа на ваш выбор с названиями:
- "1-ый столбец"
- "2-ой столбец"
- "column name"
2. Выполнить insert в таблицу нескольких записей.
3. Выполнить select из таблицы только первых двух полей.

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

#задача

Соглашение об именовании (naming convention)

You asked…
Привет, Том!
Хотел бы узнать вашу точку зрения на соглашение об именах?
Например, вы ограничиваете количество символов (скажем, 20) для имени таблицы?
Предоставляет ли Oracle шаблон для соглашения об именах в базе данных?

...and we said
Лично у меня нет соглашений об именах. Я не люблю набирать действительно длинные имена, но время от времени я достигаю ограничения в 30 символов.

Это, прежде всего, вопрос выбора команды.

Единственные соглашения об именах, которые у меня есть, относятся к PLSQL.

Локальные переменные должны начинаться с L_
Параметры должны начинаться с P_
Глобальные переменные пакета должны начинаться с G_

Например, параметр ENAME не будет путаться со столбцом с именем ENAME в запросе - у меня никогда не будет столбца p_ename - поэтому нет двусмысленности.

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

#asktom #именование

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

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

Для нашего случая:

drop table "Моя Таблица";

create table "Моя Таблица"(
"1-ый столбец" number(10),
"2-ой столбец" date,
"column name" number(3)
);
insert into "Моя Таблица"
  ("1-ый столбец", "2-ой столбец", "column name")
select level, sysdate, level from dual connect by level <=10;

select "1-ый столбец", "2-ой столбец" from "Моя Таблица";

select * from user_tables t where t.table_name = 'Моя Таблица';

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

Когда это может пригодиться?
1. Если кто-то использовал не стандартное имя, и вам необходимо работать с этим объектом.
2. При крайней необходимости обхода стандарта именования в Oracle.

#решениезадачи #именование

На этой недели, мы кратко прошлись по теме нейминга при разработке.

Наличие зафиксированных договоренностей, best-practices по разработке, именованию объектов и идентификаторов, правила для форматирования кода (желательно автоматического), говорит об уровне инженерной культуры 👷‍♀️

Исходя из результатов голосования, 70% команд имеют некие договоренности. Довольно не плохой результат 👍

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

Если же у вас нет пока каких-то правил, рекомендую начать хотя бы с простых базовых вещей. Постепенно развивать. Не волнуйтесь, что legacy-код, который уже написан, не будет соответствовать вашим стандартам. Код имеет свойство протухать, рефакториться и т.п.

Примеры, нейминга смысла приводить не вижу. В основном все сводится к добавлению префиксов/постфиксов к именам. Типа: v_ - локальная переменная, c_ - константа, pi - входной параметр, ***_tbl - таблица и т.д.

Тут уж решает команда. Главное чтобы решение было 😉

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

#именование