Использование объектных представлений в СУБД Oracle

Вот уже более 10 лет существуют объектно-реляционные СУБД, постоянно усложняется структура хранимой информации, стандарт SQL учитывает вопросы сопряжения с объектно-ориентированными языками программирования, но, тем не менее, функционал обеспечивающий поддержку объектной парадигмы крайне редко используются в БД. Отчасти, это объясняется консерватизмом разработчиков, отсутствием развитых методологий проектирования объектно-реляционных БД и широким распространением ORM-средств. Тем не менее, существует ряд задач, где использование объектных возможностей самих БД может повысить производительность и гибкость системы.

Начнём с того, что хранить данные в объектах вместо таблиц в самой БД несколько дискомфортно — большинство разработчиков уже привыкло к использованию ORM-средств. Один из подходов, решающих эту проблему заключается в использовании объектов, синтезируемых на основе существующих реляционных данных без вмешательства в структуру таблиц. Достичь этого можно с помощью объектных представлений (Object Views), которые позволяют синтезировать объекты на основе выборки из таблиц в атрибуты (по сути это тот же ORM, только на стороне сервера). Так же как реляционные представления являются виртуальными таблицами, объектные представления являются виртуальными таблицами объектов.

Кроме очевидных плюсов использования объектного подхода, этот подход позволяет переопределять объекты простым изменением представлений, фильтровать доступные для приложений данные, разграничивать доступ пользователей на уровне иерархий объектов, а также, в определённых ситуациях, повышать производительность. Важно отметить, что объектные представления не накладывают ограничений на применяемые способы хранения данных, возможность использования опций СУБД Oracle (таких как Partitioning, Label Security, Advanced Compression и т.д.), возможно использование таблиц, подключенных через дблинки.

Ниже будут продемонстрированы основные возможности объектных представлений реализованные в СУБД Oracle. В качестве схемы для примера предлагаю простой набор таблиц, в которых хранится информация о магазинах, дорогах, которые их связывают и о поставщиках товаров.



Таблицы содержат данные, описывающие следующую модель:



Итак, создание объектного представления состоит из трёх основных этапов:

1. Определение объектного типа с необходимыми атрибутами.
2. Написание запроса, выбирающего эти атрибуты из таблиц в том же порядке, как они определены в объекте.
3. Задание уникального значения, получаемого из выборки атрибутов, для использования в качестве ссылки на объект (часто достаточно использовать первичный ключ).

Сначала определим объекты, описывающие магазины и связи между ними:

CREATE FORCE TYPE road_t AS OBJECT (
shop REF shop_t,
distance NUMBER(8));
/
CREATE TYPE road_list_t AS TABLE OF road_t;
/
CREATE TYPE shop_t AS OBJECT (
id NUMBER(6),
name VARCHAR2(20),
links road_list_t) NOT FINAL;
/

Указание директивы FORCE потребовалось для реализации кольцевых ссылок: магазины ссылаются на дороги, которые, в свою очередь, ссылаются на другие магазины. Теперь определим представление:

CREATE FORCE VIEW shops_v OF shop_t WITH OBJECT IDENTIFIER(id) AS
SELECT s.id, s.name, СAST(MULTISET(
  SELECT MAKE_REF(shops_v,
    decode(r.dest_id - s.id, 0, r.src_id, r.dest_id)), r.distance
  FROM roads r WHERE s.id in (r.dest_id, r.src_id)
  ) AS road_list_t)
FROM shops s;
/

Конструкция WITH OBJECT IDENTIFIER задаёт уникальный идентификатор, предоставляемый объекту (в данном случае используется первичный ключ из таблицы shops). Так как информация о дорогах хранится в виде пары ссылок на магазины, для определения соседних магазинов используется конструкция decode. Сами ссылки формируются функцией MAKE_REF, возвращающей ссылку на объект по переданным ключу и объектной таблице либо представлению. Теперь можно получить список магазинов, соседствующих со заданным, используя навигационный обход:

SELECT DEREF(shop).id FROM table(SELECT links FROM shops_v WHERE id = 5);

DEREF(SHOP).ID
--------------
1
2
6

Оператор DEREF используется для разадресации ссылки, полученной из атрибута — вложенной таблицы (доступ к которой осуществляется через функцию table). В общем случае, использование ссылок позволяет реализовывать как связи один-ко-многим (как было показано выше), так и связи многие-ко-многим, например:

CREATE FORCE TYPE vendor_shop_t UNDER shop_t
(vendors vendor_list_t);
/
CREATE FORCE TYPE vendor_list_t AS TABLE OF REF vendor_t;
/
CREATE FORCE TYPE vendor_t AS OBJECT (
id NUMBER(3),
name VARCHAR2(10),
shops vendor_shop_list_t);
/
CREATE TYPE vendor_shop_list_t AS TABLE OF REF vendor_shop_t;
/

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

CREATE FORCE VIEW vendor_shops_v OF vendor_shop_t UNDER shops_v AS
SELECT s.id, s.name, CAST(MULTISET(
  SELECT MAKE_REF(shops_v,
   decode(r.dest_id - s.id, 0, r.src_id, r.dest_id)), r.distance
  FROM roads r WHERE s.id in (r.dest_id, r.src_id)
 ) AS road_list_t),
 CAST(MULTISET(
  SELECT MAKE_REF(vendors_v, vs.vendor_id)
  FROM vendor_shops vs WHERE vs.shop_id = s.id
 ) AS vendor_list_t
)
FROM shops s;
/
CREATE FORCE VIEW vendors_v OF vendor_t WITH OBJECT IDENTIFIER(id) AS
SELECT v.id, v.name, CAST(MULTISET(
  SELECT MAKE_REF(vendor_shops_v, vs.shop_id)
  FROM vendor_shops vs WHERE vs.vendor_id = v.id
 ) AS vendor_shop_list_t
)
FROM vendors v;
/

Запросы к иерархиям позволяют выбирать объекты не только из заданного представления, но и его потомков, регулируя таким образом уровень детализации. Выборка типов объектов осуществляется с помощью конструкций ONLY и IS OF TYPE:

SELECT count(0) FROM shops_v WHERE id < 3;

COUNT(0)
--------
4

SELECT count(0) FROM ONLY(shops_v) WHERE id < 3;

COUNT(0)
--------
2

SELECT count(0) FROM vendor_shops_v s WHERE id < 3 AND VALUE(s) IS OF TYPE (ONLY shop_t);

COUNT(0)
--------
0

Ограничения на возможность применения DML над объектными представлениями аналогичны тем что действуют для реляционных представлений. Тем не менее, возможно использование триггеров INSTEAD OF для реализации нестандартной логики DML. Следующий фрагмент кода реализует вставку объектов vendors_v, добавляя информацию в нижележащие таблицы:

CREATE TRIGGER vendors_v_insert INSTEAD OF INSERT ON vendors_v FOR EACH ROW
DECLARE
 shop_id NUMBER;
 CURSOR c IS SELECT DEREF(COLUMN_VALUE).id FROM table(:NEW.shops);
BEGIN
 OPEN c;
 INSERT INTO vendors VALUES (:NEW.id, :NEW.name);
 LOOP
  FETCH c INTO shop_id;
  EXIT WHEN c%NOTFOUND;
  INSERT INTO vendor_shops VALUES(:NEW.id, shop_id);
 END LOOP;
 CLOSE c;
END;
/

Не смотря на то то что данные представлений изначально берутся из реляционных таблиц и существуют ограничения в плане построения индексов и прочие, часто объектный подход позволяет получить выигрыш в производительности. Важно понимать что данный эффект достигается на больших объёмах данных и запросах, требующих полного просмотра при реляционном подходе и работы с ссылками при объектном. Сравнение производилось при при следующих размерах таблиц: shops – 1000 строк, vendors – 60, vendor_shops — 20 тысяч, roads — 300 тысяч. Рассмотрим пример:

SELECT v1.id, v2.id
FROM vendor_shops_v v1, vendor_shops_v v2 WHERE
CARDINALITY(v1.vendors MULTISET INTERSECT v2.vendors) > 0 AND
v1.id = 2;

в сравнении с реляционным вариантом

SELECT v1.id, v2.id
FROM shops v1, shops v2 WHERE
EXISTS (
  SELECT vendor_id FROM vendor_shops WHERE shop_id = v1.id
  INTERSECT
  SELECT vendor_id FROM vendor_shops WHERE shop_id = v2.id
)
AND v1.id = 2;

даёт двукратный прирост производительности. Следующая пара запросов, получающих список соседних магазинов имеющих общих поставщиков с данным, наоборот, является примером того, как делать не надо:

SELECT v1.id, v2.id
FROM vendor_shops_v v1, vendor_shops_v v2
WHERE v2.id in (SELECT DEREF(shop).id FROM table(v1.links)) AND
 CARDINALITY(v1.vendors MULTISET INTERSECT v2.vendors) > 0 AND
 v1.id = 2;

SELECT v1.id, v2.id
FROM shops v1, shops v2
WHERE
 EXISTS (SELECT 1 FROM roads WHERE src_id=v1.id AND dest_id=v2.id) AND
 EXISTS (
  SELECT vendor_id FROM vendor_shops WHERE shop_id = v1.id
  INTERSECT
  SELECT vendor_id FROM vendor_shops WHERE shop_id = v2.id
 ) AND 
 v1.id = 2;

Как результат — пятнадцатикратное падение производительности. Причина, как не сложно заметить, скрывается в сложном определении атрибута links для объекта shop_t, которое вызывает генерацию удвоенного количества объектов road_t и пропорциональный рост чтений из таблицы roads. Обратите внимание, в обоих примерах объектный запрос гораздо проще читается.

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

Литература

1. Eric Belden, Janis Greenberg. Oracle Database Object-Relational Developer's Guide 11g Release 2 (11.2) – Oracle, March 2010. PDF
2. Jim Melton. Advanced SQL:1999. Understanding Object-Relational and Other Advanced Features – Morgan Kaufmann Publishers, 2003.
3. Фернстайн С. Подставляемость и преобразование объектных типов в иерархии – Oracle Magazine/Russian Edition, июнь 2002.
4. W.P. Zhang, Norbert Ritter. The real benefits of object-relational db-technology for object-oriented software development. In B. Read, editor, Proc. 18th British National Conference on Databases (BNCOD 2001), Advances in Databases, pages 89-104. Springer-Verlag, July 2001. PDF
5. С.Д. Кузнецов. Объектно-реляционные базы данных: прошедший этап или недооцененные возможности?, 2007. HTML

All source code was highlighted with Source Code Highlighter.