delta revisited. Работа с изменениями первичного ключа

Ранее я описал механизм delta для регистрации изменений в таблицах БД Oracle, позволяющий обрабатывать изменения независимо нескольким клиентам.

Долгое время все решения на базе механизма delta делались для базовых таблиц с неизменными первичными ключами.

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

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

delta revisited. Работа с обычной таблицей изменений

Ранее я описал механизм delta для регистрации изменений в таблицах БД Oracle, позволяющий обрабатывать изменения независимо нескольким клиентам.

Работа описанного механизма опирается на таблицу изменений с rowdependencies. Что делает его зависимым от СУБД Oracle. А в один прекрасный день возникает желание использовать хорошо зарекомендовавший себя механизм в СУБД PostgreSQL или другой СУБД с триггерами.

Можно ли использовать более универсальный подход при реализации таблицы изменений вместо таблицы с rowdependencies?

Захват и обработка изменений в БД Oracle (at_delta)

Ранее я рассказывал о том, как получить изменения данных в исходных таблицах за период с помощью операции MINUS.

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

Библиотека atop-plsql для разработки в СУБД Oracle

Недавно я оформил и выложил на GitHub "труд долгих лет" atop-plsql - набор PL/SQL пакетов и сопутствующих им типов, таблиц и некоторых других объектов схемы БД.

Эти пакеты развивались и использовались в течение нескольких лет для разработки на их основе конечных решений в СУБД Oracle 11gR2.

Типы данных TIMESTAMP и INTERVAL в СУБД Oracle

Сегодня в СУБД Oracle есть несколько типов данных для хранения дат и времени. Самый старый из них - тип date - совершенно точно был еще в Oracle 7 (с более ранними версиями СУБД я не работал). Некоторые интересные вещи про тип date я уже рассказывал. В версии Oracle 9 появились новые типы для дат и времени timestamp, timestamp with local time zone и timestamp with time zone, а также интервальные типы interval year to month и interval day to second, работающие вместе с новыми типами и типом date.

Типы timestamp, timestamp with local time zone и timestamp with time zone привнесли два новшества, по сравнению с типом date:

• возможность работать со временем с точностью до наносекунд,
• возможность работать с часовыми поясами (time zones).

Ниже мы поработаем с типами timestamp и interval, обращая внимание на задание значений этих типов с помощью литералов и на их арифметику. Затем обратимся к различиям между типами timestamp, timestamp with local time zone и timestamp with time zone, и рассмотрим, какой из них предпочтительно использовать в какой ситуации.

Комбинаторика в СУБД Oracle 11g

По запросу "oracle factorial" с ходу нагугливаются два подхода к вычислению факториала в СУБД Oracle:

• самоочевидный: в цикле получить произведение натуральных чисел от 1 до n

  create or replace
  function fac1(n pls_integer) return number
  is
      f number := 1;
  begin
      if n in (0, 1) then
          return 1;
      end if;
      for i in 2..n loop
          f := f * i;
      end loop;
      return f;
  end fac1;
  /
  

• забавный: вместо произведения натуральных чисел от 1 до n найти сумму их логарифмов, а затем возвести основание логарифма в найденную степень

  create or replace 
  function fac2(n pls_integer) return number
  is
      f number;
  begin
      select round(exp(sum(ln(level))))
      into f
      from dual
      connect by level <= n;
      return f;
  end fac2;
  /
  

Передача изменений между БД: подход в духе KISS

В БД SOURCE в таблице itemz ведется некий справочник. Время от времени в справочник добавляются новые строки и изменяются существующие. Возможно, строки даже иногда удаляются. В данных этого справочника нуждается БД DEST, причем контракт состоит в передаче из БД SOURCE всех изменений справочника. И как же организована передача изменений БД SOURCE в БД DEST? А вот так.

Два кейса для ora_rowscn в Oracle 11g

В СУБД Oracle системный номер изменения (SCN, system change number) есть непрерывно возрастающее число. Каждая транзакция, и даже каждое чтение этого номера из БД увеличивает его:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 7211772580

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 7211772583

Начиная с версии 10 в таблицах БД Oracle появился псевдостолбец ora_rowscn. Этот псевдостолбец содержит SCN, связанный с последним изменением соответствующей строки. Посмотрим на него поближе.

Запуск задач в БД Oracle по расписанию, заданному регулярным выражением

Расписание запуска задач для crontab мне нравится своей простотой и наглядностью.

Можно ли в Oracle реализовать запуск задач по расписанию, похожему на расписание crontab? И при этом обойтись без программирования разбора строк, задающих расписание?

Загрузка данных в БД Oracle из csv-файла

Когда-то наиболее естественным способом загрузки структурированных данных в БД Oracle была загрузка при помощи SQL*Loader. Требовалось подготовить файл с параметрами, управляющими загрузкой, и вызвать утилиту sqlloader, передав ей управляющий файл и файл с данными.

Затем производители Oracle предоставили новый механизм для загрузки данных из внешних файлов - external tables. Нужно создать внешнюю таблицу командой DDL CREATE TABLE, указав внешний файл в качестве источника данных и описав его структуру. Средства для описания структуры очень похожи на средства описания структуры для SQL*Loader.

Если вам приходится часто загружать данные из структурированных текстовых файлов, то и SQL*Loader и внешние таблицы начинают казаться слишком громоздкими. Оба инструмента позволяют достаточно гибко описывать структуры загружаемых данных, определять проверки и преобразования данных в ходе загрузки. Это плюс. Минус в том, что для загрузки в БД данных из очередного внешнего файла нужно специально создавать для этого файла таблицу, будь то обычная таблица, в которую загрузит данные SQL*Loader, или внешняя таблица, связанная с внешним файлом при ее создании.

В этой статье я расскажу о еще одном подходе к загрузке данных из внешних csv-файлов (которым уже давно успешно пользуюсь).

Трассировка и профилирование PL/SQL в Oracle 11g, часть II

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

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

В СУБД Oracle 11g главным инструментом профилирования PL/SQL является иерархический профайлер (hierarchical profiler). Иерархический профайлер, в частности, обеспечивает

• регистрацию времени выполнения отдельно для SQL и PL/SQL кода,
• подсчет числа вызовов каждой подпрограммы PL/SQL и времени, затраченного на ее выполнение,
• генерацию набора отчетов для анализа данных о выполнении программы.

Трассировка и профилирование PL/SQL в Oracle 11g, часть I

Что такое трассировка (tracing), профилирование (profiling) и чем они отличаются друг от друга?

Трассировка - это прогон программы c регистрацией того, какие строки (команды) каких модулей (подпрограмм) в какой последовательности были выполнены.

Профилирование - это прогон программы с регистрацией времени выполнения отдельных модулей (подпрограмм) и, возможно, внешних вызовов, выполняемых программой.

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

Коллекции в Oracle PL/SQL

Кандидатам на позиции, связанные с программированием на PL/SQL, на собеседованиях я задаю вопроc: "Существует ли в языке PL/SQL структура, подобная массиву? Если да, то расскажите о ней."

Из пары дестков кандидатов, с которыми довелось беседовать за три последних года, подавляющее большинство более или менее уверенно рассказывали об index-by table. Только один кандидат рассказал обо всех трех разновидностях коллекций в PL/SQL:

• ассоциативный массив, или index-by table,
• вложенная таблица, или nested table, и
• массив переменной длинны, или varray.

О них и пойдет речь в этой статье.

Вызов внешних библиотечных функций из PL/SQL в Oracle 11gR2

Для того, чтобы в Oracle 11gR2 из PL/SQL вызвать функцию внешней динамической библиотеки необходимо:

• создать динамически подгружаемую библиотеку с необходимой функцией, это .so - shared object - в ОС Unix и .dll - dynamic link library - в ОС Windows,
• поместить динамическую библиотеку в директорию на сервере, где работает экземпляр Oracle,
• сконфигурировать процесс exptproc для того, чтобы он мог найти и загрузить динамическую библиотеку,
• создать в БД Oracle объект LIBRARY, который присвоит имя полному пути к динамической библиотеке,
• создать в БД Oracle PL/SQL процедуру или функцию - обертку, указав объект LIBRARY и спецификацию вызова функции динамической библиотеки.

Я проделаю перечисленные шаги и продемонстрирую вызов функций внешней динамической библиотеки из PL/SQL.

О работе с большими объектами в СУБД Oracle

Для работы с данными большого объема СУБД Oracle предоставляет типы данных BLOB, CLOB, NCLOB и BFILE. Здесь LOB означает large object, или большой объект, и далее по тексту термины LOB и "большой объект" взаимозаменяемы. По сути, большой объект - это абстрактный тип для манипуляции данными большого объема внутри БД, а типы BLOB, CLOB, NCLOB и BFILE - его конкретные реализации.

Указанные типы данных можно использовать в СУБД Oracle для определения столбцов таблиц, атрибутов объектных типов и переменных PL/SQL.

DML триггеры в Oracle 11g

Как известно, DML триггер в СУБД Oracle может срабатывать в следующие моменты времени при выполнении команд SQL INSERT, UPDATE, DELETE и MERGE:

• BEFORE STATEMENT - перед тем, как начнут делаться изменения,
• BEFORE EACH ROW - перед изменением каждой строки таблицы,
• AFTER EACH ROW - после изменения каждой строки таблицы,
• AFTER STATEMENT - после того, как сделаны все изменения.

Что если вам понадобится в триггере AFTER STATEMENT узнать количество сток, измененных текущей командой DML?

Oracle 11g PL/SQL, еще несколько особенностей

Я уже делился тем, что недавно (вновь или впервые) привлекло мое внимание в языке PL/SQL. Сегодня - продолжу.

Oracle 11g PL/SQL, неcколько особенностей

Как и любой язык программирования, PL/SQL имеет свои особенности. Одни особенности делают язык программирования тем, что он есть, и программист использует их постоянно. А без других он может обходиться годами и даже вовсе не подозревать (или забыть) об их существовании.

Например, особенностью Java является необходимость объявлять исключения, которые выбрасывает метод. Без этого не обойтись. Другая особенность Java - невозможность использования одноименных переменных во вложенных блоках. Об этом вспоминаешь не часто. Очень разные вещи, но характерные для Java. А особенностью PL/SQL до версии Oracle 11g было отсутствие предложения CONTINUE - и для его имитации приходилось использовать безусловный переход на метку в конце тела цикла. Теперь эта особенность осталась в прошлом.

Недавно я предпринял систематический обзор PL/SQL по книге Study Guide for 1Z0-144: Oracle Database 11g: Program with PL/SQL: Oracle Certification Prep by Matthew Morris. Про некоторые особенности PL/SQL, которые привлекли мое внимание в процессе чтения, я сегодня и расскажу.

Параллельная обработка с помощью Oracle Scheduler

Можно получить выигрыш в производительности, если выполнять обработку данных параллельно несколькими потоками - в нескольких сеансах Oracle. При помощи пакета DBMS_JOB этого можно было добиться и в более ранних версиях Oracle. Однако Oracle Scheduler, появившийся в версии 10 и усовершенствованный в версии 11, предоставляет для этого более развитые возможности. В этой статье я

1. кратко рассмотрю цепочки (chains) Oracle Scheduler,
2. продемонстрирую создание цепочки для параллельной обработки данных,
3. продемонстрирую организацию параллельной обработки данных без создания цепочки.

Java в СУБД Oracle, часть III

В этой статье я рассмотрю, как Java код, выполняющийся внутри СУБД Oracle, работает с данными в БД. Попутно продемонстрирую работу резолвера (resolver) встроенной Oracle JVM и экспериментально установлю, с какими правами выполняется Java код внутри СУБД - правами владельца кода (definer) или вызывающего пользователя (invoker). В заключение, я продемонстрирую использование SQLJ для включения команд SQL в исходный код JAVA.