Using Object Views in Oracle Database Management Systems

For more than 10 years, object-relational DBMS has existed, the structure of stored information is constantly becoming complicated, the SQL standard takes into account interfacing with object-oriented programming languages, but, nevertheless, the functionality providing support for the object paradigm is extremely rarely used in the database. In part, this is due to the conservatism of 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 databases themselves can improve system performance and flexibility.

Let's start with the fact that storing data in objects instead of tables in the database itself is somewhat uncomfortable - most developers are already accustomed to using ORM tools. One approach that solves this problem is to use objects that are synthesized from existing relational data without interfering with the structure of the tables. To achieve this, you can use object views (Object Views), which allow you to synthesize objects based on a sample of tables into attributes (in fact, this is the same ORM, only on the server side). Just as relational views are virtual tables, object views are virtual tables of objects.

In addition to the obvious advantages of using an object-based approach, this approach allows you to redefine objects by simply changing the views, filtering the data available for applications, restricting user access at the level of object hierarchies, and also, in certain situations, improving performance. It is important to note that object representations do not impose restrictions on the methods of data storage used, the possibility of using the options of Oracle DBMS (such as Partitioning, Label Security, Advanced Compression, etc.), it is possible to use tables connected via dlinka.

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


The tables contain data describing the following model:



So, the creation of an object representation consists of three main stages:

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 an object reference (often it is enough to use the primary key).

First, we define objects describing stores and the connections 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 ring 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 specifies a unique identifier provided by the object (in this case, the primary key from the shops table is used). Since the road information is stored as a pair of links to stores, the decode construction is used to identify neighboring stores. The links themselves are formed by the function MAKE_REF, which returns a link to the object by the passed key and the object table or view. Now you can get a list of shops adjacent to the specified using the navigation traversal:

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

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

The DEREF statement is used to distribute a link obtained from an attribute — a nested table (accessed through the table function). In general, using 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;
/

The object describing the company stores vendors vendor_shop_t was determined through inheritance from shop_t. Views, like objects themselves, can be arranged in a hierarchy, otherwise the definitions of views 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, thus adjusting the level of detail. The selection of object types is carried out using the 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

Restrictions on the possibility of using DML over object views are similar to those for relational views. However, it is possible to use INSTEAD OF triggers to implement non-standard 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 these views are initially taken from relational tables and there are limitations in terms of building indexes and others, often the object approach allows you to get a performance gain. It is important to understand that this effect is achieved on large amounts of data and queries that require a full view with the relational approach and work with links with the object. The comparison was made with the following table sizes: shops - 1000 lines, 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;

compared to the 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 double performance boost. The next couple of requests that receive a list of neighboring stores that have common suppliers with this one, 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;

The result is a fifteen-fold drop in performance. The reason, as it is not difficult to notice, is hidden 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 the proportional growth of readings from the roads table. Note that in both examples the object request is much easier to read.

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

Literature

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 S. Substitution and Object Type Conversion into Hierarchies - Oracle Magazine / Russian Edition, June 2002.
4. WP Zhang, Norbert Ritter. The real benefits of object-related software development 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. S.D. Kuznetsov. Object-relational databases: past stage or underestimated opportunities ?, 2007. HTML

Source Code Highlighter .