ROWID - уникальный адрес/идентификатор строки

Самый быстрый доступ к строке. Никакие primary key/unique key не способы дать такой быстрый доступ.
Oracle достаточно получить физический адрес, чтобы адресоваться до нужной ячейки на диске.

Выглядит он примерно так: 000000FFFBBBBBBRRR
Соответственно, 0000000 - это номер объекта, FFF - это номер файла, BBBBBB - номер блока, RRR - номер строки.

Хранить и запоминать его в долговренное хранилище не стоит, т.к. он может измениться. Но вот использовать в наших алгоритмах очень даже можно.
———-
Приведу пример.

declare
   v_rowid  rowid;
begin
   select rowid from my_tab where ... for update;
   ... что-то делаем ...
   update my_tab t 
      set t.поле = новое_значение
    where t.rowid = v_rowid;
end;
/

На первом шаге мы выбрали по каким-то критериям искомую строку, произвели какие-то манипуляции и хотим её изменить (например, статус). Используя rowid в условии where мы можем максимально быстро получить к ней доступ.

#теория #архитектура #rowid

Oracle - сертификация

Сертификация - сдача тестирования и получение соответствующего сертификата.

Зачем?
- в процессе подготовки узнаете новое;
- закрепляете и систематизируете имеющиеся знания;
- получаете конкурентные преимущества на рынке труда.

Как проходит?
Сдается тест из нескольких десятков вопросов в одном из сертифицированных центров Oracle. Естественно, без шпаргалок и гугла.

Цена?
Примерно 95-245$

В итоге, получаете сертификат о сданном экзамене и ссылку в каталоге Oracle для демонстрации всем желающим (электронный сертификат).

И не забудьте обновить резюме 😀

#сертификация

Блокировки - это механизм, используемый для управления одновременным доступом к общему ресурсу.
Есть два вида блокировок: пользовательские и системные.

Сегодня рассмотрим только пользовательские. Таких есть три вида.
1. Команда select … from … for update;
Блокирует строки выбранные командой select. Это гарантирует, что пока мы работаем с заблокированной строкой никто не сможет ее изменить. Это применяется при блокировках строк с балансами и работе с другими критически разделяемыми ресурсами. Часто используется на практике.

2. Пакет dbms_lock
Используется для создания блокировки на уровне части кода. Допустим мы не хотим, чтобы какая-то логика приложения использовался одновременно из разных сессий. Применяется не часто, но иногда здорово выручает.

3. Команда lock имя_таблицы;
Блокирует полностью всю таблицу, никто ничего с ней сделать не сможет. Практически не применяется ввиду того, что блокируется полностью таблица, можно только читать.

В следующих постах рассмотрим на примерах каждый вид блокировок.

#теория #блокировки

Объявление с привязкой - объявление типа данных с привязкой к какому-то объекту. Например, к строке таблицы/курсора (%rowtype) или типу колонки/переменной (%type).

Особенности:
- Привязка происходит на стадии компиляции.
- Не приводит к увеличению времени выполнения.
- При изменении привязываемого элемента - код станет INVALID. Нужно выполнить повторную компиляцию.
- Ограничения/default-значения столбцов не транслируются в переменную.

Плюсы использования:
1. Синхронизация со структурами в БД - изменилась таблица или тип колонки, изменения сразу же применятся по всему - коду.
2. Нормализация локальных переменных - источник типа из одного места. Момент спорный, в некоторых ситуациях подойдет.

Рекомендация: использовать объявление с привязкой везде, где это возможно.

Пример:
-- Тип строки таблицы

v_department_row      dep%rowtype;

-- Параметры функции привязаны к таблицей

function get_dep_name_by_id (p_id dep.id%type) return dep.name%type is...

#теория #type #rowtype

ORA-30036: unable to extend segment in undo tablespace

Причина:
Очень большое изменение данных в одной транзакции. Просто не хватило место для сохранения всех изменившихся блоков.

Решение:
1. Не делать больших транзакций (при этом, не впадать в крайность и не делать частых коммитов).
2. Если такой возможности нет - увеличивать временно ТП. Либо использовать отдельное ТП большего размера.

Таким образом, ошибку можно решить как со стороны DBA, так и со стороны DBD.

#теория #ошибки_oracle

Сертификационные экзамены для DBD

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

“Associate” - считается начальной ступенью, “Professional” - средний уровень, “Master” - мастер своего дела. Экзамены на “Master” доступны только для DBA.
"Professional" недоступен без сдачи экзаменов уровня “Associate”.

Актуальный список экзаменов можно посмотреть на сайте Oracle. Основные для разработчика:
* 1Z0-071. Oracle Database SQL Certified Associate
* 1Z0-144. Oracle PL/SQL Developer Certified Associate
* 1Z0-048. Oracle Database PL/SQL Developer Certified Professional

Без предварительной подготовки сдавать не стоит, только сольете кровно заработанные.

У меня уходило примерно 1.5 месяца на подготовку к каждому. Конечно, всё индивидуально.

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

#сертификация

Задача: Требуется подсчитать количество дней в текущем году.

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Задача: Требуется подсчитать количество дней в текущем году.

Принцип решения: Количество дней в текущем году – это разница между первым днем следующего года и первым днем текущего года (в днях). Каждое решение включает в себя следующие этапы:
1. Определение первого дня текущего года.
2. Добавление одного года к этой дате (для получения первого дня следующего года).
3. Вычитание текущего года из результата шага 2.

Запрос

select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') from dual;

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

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

Автономные транзакции (autonomouse_transaction, АТ)

АТ позволяют создавать новые подтранзакции, которые можно сохранять или отменять вне зависимости от родительской. Достигается это путем указания специальной директивы PL/SQL компилятору:

pragma autonomous_transaction;

Автономную транзакцию можно использовать в любом PL/SQL-блоке (анонимном блоке, функции, процедуры, триггеры).
АТ - обязательно необходимо завершить оператором фиксации/отмены - commit/rollback.

Когда стоит применять:
1. Логирование/аудит - запись действий будет зафиксирована, даже если в родительской транзакции произойдет откат.
2. Выполнение DDL операторов - используется, когда влияние на текущую транзакцию недопустимо или необходимо изменять данные в операторах SQL (что не очень хорошая практика).

#теория #транзакции

Выбираем IDE для работы с Oracle

IDE - интегрированная среда разработки

1. PLSQL Developer - на мой взгляд, одна из удобнейших средств разработки PL/SQL-кода. Интерфейс комфортен, все под рукой, ничего лишнего, управлять объектами одно удовольствие. Можно настроить автоматическое форматирование кода. Недостаток работает только в Windows. Платная. Лично я использую только эту IDE 😌

2. Quest Toad (лягушка) - занимает второе место в моем рейтинге. Отличный инструмент для управления БД, т.е. в большей степени подходит DBA, но я знаю некоторых разработчиков, которые только в ней и работают. Платная.

3. Oracle SQL Developer - отношения с этой IDE у меня не сложились, в виду крайне не удобного интерфейса, редактора и огромного количества странностей. Единственные плюсы - бесплатна и есть версии под все ОС.

4. JetBrains Datagrip - создана в стиле всех продуктов от JetBrains. Многие Java-разработчики используют именно её ввиду кроссплатформенности и привычности интерфейса. На мой взгляд, пока сыровата, доступ к объектам не очень удобен. Если команда JetBrains приложит достаточно усилий по её доработке, однозначно займет пальму первенства.
Платная.

5. DBeaver - для кого-то может оказаться "золотой серединой". Достаточно удобная IDE, заточена под разные СУБД, в меру удобный редактор кода и объектов. Я бы сказал, что-то между Oracle SQL Developer и PL/SQL Developer. Есть платная и бесплатная версии. Лично я использую DBeaver для работы с PostgreSQL. Однозначно рекомендую попробовать, тем более что есть версии под все ОС.

Так какую же IDE выбрать? Я думаю, стоит попробовать каждую, написать пару пакетов, создать несколько десятков таблиц и сделать свой выбор 😉

#ide

ORA-01555: Snapshot too old

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


Решение:
1. Увеличивать параметр undo_retention - сколько держать принудительно в UNDO старые блоки при возможности расширяться.
2. Читать меньшими по продолжительности порциями. Например, в цикле пачками.
3. Увеличиnm табличное пространство UNDO для хранения большего количества измененных данных

Таким образом, ошибку можно решить как со стороны DBA, так и со стороны DBD.

#теория #ошибки_oracle

Задача: Требуется разложить текущую дату на шесть частей: день, месяц, год, секунды, минуты и часы. Результаты должны быть возвращены в численном виде.

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Функция EXTRACT

Oracle SQL функция EXTRACT извлекает значение из типов date и timestamp [with timezone].
Временную составляющую можно извлечь только из timestamp.
Иногда бывает очень полезна, в виду своей лаконичности и простоты.

Примеры:

--Результат:  2020
select extract(year from date '2020-03-13')
  from dual;

--Результат:  3
select extract(month from date '2020-03-13')
  from dual;

--Результат:  номер дня текущей даты
select extract(day from sysdate)
  from dual;

--Результат:  2
select extract(minute from timestamp '2020-03-13 10:02:03')
  from dual;

#sql #функции #extract

Задача: Требуется разложить текущую дату на шесть частей: день, месяц, год, секунды, минуты и часы. Результаты должны быть возвращены в численном виде.

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

В данном случае используем функции TO_CHAR и TO_NUMBER.

Запрос:

select to_number(to_char(sysdate, 'dd')) day
      ,to_number(to_char(sysdate, 'mm')) month
      ,to_number(to_char(sysdate, 'yyyy')) year
      ,to_number(to_char(sysdate, 'hh24')) hour
      ,to_number(to_char(sysdate, 'mi')) min
      ,to_number(to_char(sysdate, 'ss')) sec
  from dual

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

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

Таблица DUAL

Таблица DUAL - специальная таблица для выполнения операций. В Oracle нельзя из ниоткуда получить данные, поэтому специальная таблица доступная каждому пользователю через которую можно выполнять выражения.
Заполнена всегда одной строкой со значением “X”.

Определение:

create table sys.dual
(
  dummy varchar2(1)
);
grant select on sys.dual to public with grant option;

Использование:

select функция(...) from dual;

Например, получить текущее время и пользователя БД

select sysdate, user from dual;

🚫 Удалить данную таблицу нельзя.

ℹ️ При переезде на PostgreSQL, специально создают таблицу DUAL, т.к. там не нужна таблица для выполнения выражений, а множество запросов в Oracle используют DUAL.

#sql #dual

Псевдостолбец ROWNUM

ROWNUM - число, указывающее порядок для каждой строки, возвращаемой запросом, в котором Oracle выбирает строку из таблицы или набора соединенных строк.

Порядок выполнения запроса:
1. Выборка и фильтрация (FROM/WHERE)
2. Присвоение к каждой получившейся строки номера ROWNUM.
3. SELECT
4. GROUP BY
5. HAVING
6. ORDER BY

⚠️Таким образом, ROWNUM не порядковый номер финального результата.

Если нужен именно финальный, то:
- либо оборачиваем в запрос-обертку. Сортировка во внутреннем запросе.
- либо используем аналитическую функцию row_number() over (sort by ...) с требуемой сортировкой.

Пример 1. Запрос вернет 5 строк и уже после этого отсортирует результат.
❗️Очень частая ошибка. Разработчик хотел получить первых 5 строк отсортированных по salary.

select * 
  from employees
 where rownum <= 5 
 order by salary desc;

Пример 2. Пропуск первой строки. Не даст никаких результатов вообще (неправильный запрос).

select *
  from employees
  where rownum > 1;

Пример 3. Пропуск первой строки. Даст верный результат.

select *
 from (select rownum rn,
                     a.*
         from (select *
                     from employees e
                    order by e.hire_date) a)
where rn > 1;

#sql #rownum

Задача: Требуется возвратить даты начала и конца каждого из четырех кварталов текущего года.

Смотрите готовый SQL запрос в решении в посте в четверг 🎓

#sql #задача

Задача: Требуется возвратить даты начала и конца каждого из четырех кварталов текущего года.

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

Итого. Для формирования четырех строк используем системное представление all_objects. Для представления квартала, которому соответствуют та или иная начальная и конечная даты, используем псевдостолбец ROWNUM (номер строки).
С помощью функции ADD_MONTHS находим начальную и конечную даты каждого квартала.

Запрос:

select rownum qtr
      ,add_months(trunc(sysdate, 'y'), (rownum - 1) * 3) q_start
      ,add_months(trunc(sysdate, 'y'), rownum * 3) - 1 q_end
  from all_objects
 where rownum <= 4;

Запрос можно усовершенствовать, если использовать в качестве генерации четырех строк иерархический запрос.

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

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

Генерация последовательностей значений от 1 до N

Очень часто для генерации N-строк в Oracle используются иерархические запросы.
Конструкция - connect by.

Проще всего показать на примере:

select level id
  from dual
connect by level <= 4;

Данный запрос сгенерирует четыре строки со столбцом num, в котором будет значение от 1 до 4х.

Генерация до десятка миллионов строк на обычной машине вполне возможна.

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

Теперь вы знаете, что ответить на вопрос на собеседовании - "Как сгенерировать строки в Oracle одним запросом?" 🤓

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