Using Object Representations in Oracle Database

For more than 10 years, object-relational DBMSs have existed, the structure of stored information is constantly being complicated, the SQL standard takes into account issues of interfacing with object-oriented programming languages, but, nevertheless, the functionality that provides support for the object paradigm is extremely rarely used in the database. In part, this is due to the conservatism of the developers, the lack of developed methodologies for designing object-relational databases and the widespread use of ORM tools. However, there are a number of tasks where using the object capabilities of the database itself can improve system performance and flexibility.

To begin with, storing data in objects instead of tables in the database itself is somewhat uncomfortable - most developers are already used to using ORM tools. One approach that solves this problem is to use objects synthesized based on existing relational data without interfering with the table structure. This can be achieved using Object Views, which allow you to synthesize objects based on a selection of tables into attributes (in fact, this is the same ORM, only on the server side). Just as relational representations are virtual tables, object representations are virtual tables of objects.

In addition to the obvious advantages of using the object approach, this approach allows you to redefine objects by simply changing views, filter data available for applications, delimit user access at the level of object hierarchies, and also, in certain situations, increase productivity. It is important to note that object representations do not impose restrictions on the applied data storage methods, the ability to use Oracle DBMS options (such as Partitioning, Label Security, Advanced Compression, etc.), and tables connected via dlinks are possible.

Below we will demonstrate the main features of object representations implemented in Oracle DBMS. As a diagram for an example, I propose a simple set of tables that store information about stores, the roads that connect them, and about suppliers of goods.



The tables contain data describing the following model:



So, creating an object representation consists of three main steps:

1. Defining an object type with the necessary attributes.
2. Writing a query that selects these attributes from the tables in the same order as they are defined in the object.
3. Setting a unique value obtained from a selection of attributes for use as a reference to an object (often it is enough to use a primary key).

First, we define objects that describe the stores and the relationships between them:

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;
/

The FORCE directive was required to implement circular links: stores link to roads, which in turn link to other stores. Now define the view:

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;
/

The WITH OBJECT IDENTIFIER construct sets the unique identifier provided to the object (in this case, the primary key from the shops table is used). Since road information is stored as a pair of store links, the decode construct is used to identify neighboring stores. The links themselves are formed by the MAKE_REF function, which returns a reference to the object by the passed key and the object table or view. Now you can get a list of stores adjacent to the given one using the navigation bypass:

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

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

The DEREF operator is used to redirect a link obtained from an attribute, a nested table (accessed through the table function). In the general case, the use of links allows you to implement both one-to-many relationships (as shown above) and many-to-many relationships, for example:

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;
/

An object describing vendor brand stores vendor_shop_t was defined through inheritance from shop_t. Representations, like the objects themselves, can be arranged in a hierarchy, otherwise the definitions of representations will be similar to the previous one:

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;
/

Queries to hierarchies allow you to select objects not only from a given view, but also from its descendants, thereby adjusting the level of detail. Object types are selected using ONLY and IS OF TYPE constructs:

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

The restrictions on the ability to use DML over object representations are similar to those for relational representations. However, you can use INSTEAD OF triggers to implement custom DML logic. The following code fragment implements the insertion of vendors_v objects, adding information to the underlying tables:

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;
/

Despite the fact that the data of representations are initially taken from relational tables and there are limitations in terms of constructing indexes and others, often the object approach allows you to get a gain in performance. It is important to understand that this effect is achieved on large amounts of data and queries that require full viewing with the relational approach and working with links with the object. Comparison was made with the following table sizes: shops - 1000 rows, vendors - 60, vendor_shops - 20 thousand, roads - 300 thousand. Consider an example:

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;

versus relational option

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;

gives a twofold increase in productivity. The following pair of queries that get a list of neighboring stores that have common suppliers with the given data, on the contrary, is an example of how not to do it:

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;

As a result, a fifteen-fold drop in performance. The reason, as it is not difficult to notice, lies in the complex definition of the links attribute for the shop_t object, which causes the generation of twice the number of road_t objects and a proportional increase in readings from the roads table. Note that in both examples the object query is much easier to read.

I will make a reservation that the article was supposed to be an introductory one, so the examples were deliberately simplified. If the publication finds a response, I will cover this topic in more detail (loading and saving objects via JDBC, buffering, using methods in classes, analogs of the Reflection API, etc.).

References

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. Fernstein C. Substitution and Conversion of Object Types in Hierarchies - Oracle Magazine / Russian Edition, June 2002.
4. WP 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.C.D. Kuznetsov. Object-relational databases: past stage or underestimated features ?, 2007. HTML

All source code was highlighted with Source Code Highlighter .