Задача:
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 - таблица и т.д.

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

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

#именование

Продолжаем посты про оптимизацию.

Виды планов запроса
Существует два вида планов запросов:

1. Гипотетический или потенциальный (explain) - это то как Оракл предположительно будет выполнять запрос. Его можно посмотреть без выполнения самого запроса. При этом не нужен sql_id, plan_hash_value. Иногда такой тип планов называют design time plan.

2. Фактически или реальный план (execution) - это то как реально выполнялся запрос. Соответственно, чтобы получить такой план, запрос необходимо выполнить хотя бы один раз. Иногда такой тип планов называют run time plan.

Эти планы могут отличаться, при том что вы не меняли текст запроса.

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

Для каждого типа плана существует несколько способов их просмотра.

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

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

Задача:
необходимо проверить есть ли схема "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 мин

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