Задача:
необходимо проверить есть ли схема "HR2" в базе данных.

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

#задача

В чем разница между потенциальным планом и планом выполнения

You asked…
Привет, Том!
Привет, у меня вопрос по поводу потенциального и реального плана. Я читал, что план выполнения - это план, который Oracle намеревается использовать для запроса, и фактический план выполнения может отличаться; если это так, то как мне получить реальный план выполнения. Заранее спасибо.

...and we said
Представьте, что вы планируете автопутешествие. Вы открываете навигатор и строите маршрут. Это ваш гипотетический план.

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

Гипотетический план - это предсказание.
Фактический план - это то как реально все прошло.

Существует множество способов получить план выполнения. Ссылки в статье.

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

#asktom #оптимизация

Задача:
Необходимо проверить есть ли схема HR2 в базе данных.

Решение:
-- 1 способ (подойдет для любых пользователей)

declare
  v_schema user_objects.object_name%type := 'HR2';
begin
  dbms_output.put_line('Схема '||dbms_assert.schema_name(v_schema)||' существует.');
exception
  when dbms_assert.invalid_schema_name then
      dbms_output.put_line('Схемы '||v_schema||' не существует');
end;
/

-- 2 способ (подойдет для привилегированных пользователей)

select count(*) from dba_users t where t.username = 'HR2';

Наверняка, есть и другие способы.

Рекомендую посмотреть модули в пакете dbms_assert. Довольно интересный пакетик, о котором мало кто знает.

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

Как посмотреть план запроса

Всем привет!
Первое видео из тех, за которые вы проголосовали 👍

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

Чего не будет - как читать планы запросов, как оптимизировать запросы. Я потихоньку формирую курс по оптимизации, в котором будет рассмотрен этот вопрос.

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

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

#видео #оптимизация

Представления

Представления - объекты, базы данных, представляющие собой либо виртуальную таблицу, либо физическую (в зависимости от типа). Они создаются с помощью запроса соединяющего одну или несколько таблиц.

Виды представлений
1. Обычные (view) - содержат в себе только определение как получать данные.
2. Материализованные (materialized view) - помимо определения содержат в себе данные.
3. Системные (system dictionary) - обычные представления, но с отображением системной информацией, предопределены в СУБД.

В следующем посте рассмотрим, зачем они нужны.

#представления

Сегодня будет немного необычная задачка в формате голосования.

Можно ли менять данные в таблицах через обычное представление?
Представление в вашей схеме, построено на ваших таблицах, т.е. права есть.

Вопрос довольно частый на собеседованиях.

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

#задача

Зачем нужны представления

1. Уменьшение сложности - “за кадром” происходит выполнение сложного запроса по соединению таблиц, агрегации данных и т.д.

2. Повышение безопасности - сокрытие некоторых полей.

3. Повышение удобства - работаем только с необходимыми данными, обращаемся к одному объекту, не таскаем кучу разных таблиц.

4. Переименование столбцов таблицы - можно представить конечную выборку в более удобном виде.

5. Настройка данных для пользователей - агрегация данных и другие преобразования

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

7. Сокрытие реального источника данных - иногда бывает полезно при переименовании исходной таблицы. Чтобы не ломать весь код, view создают с аналогичным названием.

Основные назначения я перечислил. Наверняка, этот список можно дополнить какими-то редкими кейсами использования.

#представления

Задача: Можно ли менять данные в таблицах через обычное представление?

Решение:
Правильный ответ: зависит от ситуации.

Данные в таблицах не получится менять через представления в следующих случаях:
1. Oracle не понимает какую строку менять в исходных таблицах. Например, есть агрегаты\группировки и т.д.
2. Если Oracle не понимает какую строку менять и нет триггера instead of.
3. Представление создано с опцией read only, которая запрещает изменение данных в исходной таблице(ах).

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

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

#решениезадачи #представления

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

Представление не существует физически как обычная таблица. Фактически представление создает виртуальную таблицу или подтаблицу только с теми строками и/или столбцами, которые нужно показать пользователю.

Всякий раз, когда нужен доступ к представлению, Oracle должен выполнить запрос,по которому определено представление, и вернуть результат. Этот процесс наполнения представления называется разрешением представления (view resolution) и он повторяется при каждом обращении пользователя к представлению.

Синтаксис создания

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];

#представление

Материализованные представления

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

Чаще всего, материализованные представления разделяют на два типа:
1. Обновляемые по commit’у (on commit)
2. Обновляемые вручную (on demand) - по умолчанию.

Синтаксис создания по ссылке.

Для обновления вручную (on demand) используется процедура: dbms_mview.refresh(‘название м.п.’);

Есть еще механизм журнальных групп изменений. Об этом в другой раз.

#представления

Журнальные группы изменений (log group)

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

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

Материализованное представление должно быть с опцией: REFRESH FAST

Синтаксис:

CREATE MATERIALIZED VIEW LOG ON таблица
TABLESPACE имя_табл_пространства
WITH PRIMARY KEY
INCLUDING NEW VALUES;

#представления

Системные представления/системные словари

Системные представления/системные словари - информация практически обо всех объектах в базе данных, о свойствах, о том как себя “чувствует” база данных, информация о сессиях и др.

Они создаются только системой, заполняются только системой. Максимум на них можно выдать грант на select.

1) user_xxx - информация про объекты схемы текущего пользователя. Доступны всем.
2) all_xxx - информация про объекты схемы текущего пользователя + объекты других схем, на которые есть права у текущего пользователя. Добавляется имя схемы. Доступны всем.
3) dba_xxx - информация вообще обо всем. Доступны только пользователям с ролью DBA, либо тем кому специально выдали права.

Не всегда представления с префиксом dba есть с префиксами user и all.

#представления

Условная компиляция

УК - это механизм позволяющий компилировать код/часть PL/SQL-кода в зависимости от заданных условий (флагов компиляции, переменных пакетов).
Иными словами, задав какое-то условие (флаг) вы можете отсекать часть кода в скомпилированной программе.
Довольно распространено в языке С, да и в других языках.

В PL/SQL так же есть возможность задавать то, какой код попадет в скомпилированный объект.

Используются специальный директивы $IF $THEN $ELSE $ERROR для задания ветвлений и обрамления кода. Список директив и возможности расширяются в новых версиях СУБД.

В следующем посте рассмотрим где применять условную компиляцию.

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

#компиляция

Использование условной компиляции

1) В зависимости от версии Оракла использовать тот или иной функционал.
К примеру, в поздних версия СУБД появляются некоторые возможности, но ПО поставляется на разные версии. Чтобы ничего не сломать, можно использовать УК.

2) Исключение некоторого кода из prod-версии.
Например, на дев-версии вы включаете вывод информации в буфер, некие ассерты, отладочный код и т.д. Для прод-среды этот код вреден.

3) Конфигурация PL/SQL-приложения в зависимости от каких-то условий.
Вы поставляете ваше ПО в N организаций. Дистрибутив и патчи одинаковые. Но одна из организаций не оплатил некоторую опцию. Воспользовавшись УК, можно реализовать логику вкл\выкл функционала.

4) Включение/выключение функционала в зависимости от ОС, аппаратных платформ и т.д.
Этот прием в основном используется самим создателями встроенных системных PL/SQL-модулей.

Напишите в комментариях, если вы знаете другие варианты использования 💬

#компиляция

Условная компиляция [видео]

Всем привет!

Второе видео из тех, за которые вы проголосовали 📊

В этом видео, я расскажу, что такое условная компиляция PL/SQL-кода, зачем она нужна, приведу примеры. Решил сделать его максимально сжатым и информативным.
Уже после монтажа, понял, что все равно видюха получилась почти на 7 минут 🤷🏻‍♂️
Хотелось бы, делать видосы не более 5 минут. Буду исправляться 👨🏻‍💻

В планах, сформировать курс уровня Advanced по PL/SQL, в котором будут эти и другие фишки СУБД.
В процессе написания курс для новичков по PL/SQL.

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

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

#видео #компиляция

Условная компиляция в запросах

You asked…
Привет, Том!
Я пытаюсь использовать условную компиляцию в запросе для курсора в процедуре.
При компиляции не получаю ошибок. Я получаю две колонки в курсоре, но ожидаю три, т.к. передаю входной параметр, который должен выбирать какой курсор отдавать. И должно быть три колонки. Пожалуйста, подскажи, что я не так делаю.

...and we said
Подсказка в названии. Вы используете условную компиляцию. И уже на этапе компиляции отсекается код, который не соответствует условию. Если вы хотите изменить поведение скомпилированного кода, вам необходимо изменить условия и заново его скомпилировать. С учетом этого, в run-time изменить поведение процедуры не получится.
Если вы все таки хотите добиться получения разных курсоров, то вам необходимо использовать динамический SQL в процедуре.

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

#asktom #компиляция

Все же, зачем нужна условная компиляция?

На днях прилетает вопрос от подписчика:
“Рассуждал с коллегой, чем условная компиляция отличается от заключения кода в простые if-else? Защиты тут нет, source в итоге можно смотреть, да и скорее всего сами пакеты. Просто что в памяти получается этого кода не будет, не захламлять лишней логикой?”

Я понял, что видимо, не совсем донес суть, не смотря на посты и видео-урок.

Суть в том, чтобы на этапе компиляции исключать фрагменты кода.

Рассмотрим простейший примера, показанные на рисунке. Процедура Proc1 отсутствует. flag = false.

1) Обычное условие - будет ошибка, т.к. компилятор доберется при компиляции до строки с Proc1, увидет, что её нет, и откажется компилировать код.

2) Условная компиляция - ошибки не будет, т.к. в код для компиляции вызов этой функции просто не попадет.

Мы исключаем/добавляем код перед самой компиляцией, проверку зависимостей и т.д. Это, пожалуй, самое главное отличие.

Всем хороших выходных! 👯‍♀️

#компиляция

Всем привет!
Это будет заключительный пост в этом году. Впереди предновогодняя суета и времени, для написания постов, не будет.

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

Подведу итоги с точки зрения канала. Он был создан 27-го февраля. Изначально, я думал о нем как источнике информации для своих студентов. Но вскоре понял, что темы могут быть полезны для более широкого круга специалистов.
В целом, цифра ~1200 подписчиков говорит мне, о том, что я был прав.

Конечно, формат коротких постов, а их ~200, не может охватить все темы и все тонкости, связанные с ними. Некоторые темы я освещаю на своем youtube-канале. Вышло уже 15 видео. Тем не менее, этого все равно недостаточно.

Поэтому я формирую курс по основам PL/SQL, который будет полезен новичкам. Также в планах курсы по другим направлениям: тестирование, оптимизация, PL/SQL Advanced-уровня. Надеюсь, в следующем году, у меня хватит сил и времени реализовать эти планы.

Спасибо за ваши сообщения со словами благодарности и поддержки. Для меня это очень важно 🔥

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

С наступающим новым годом! 🎄