Efficient implementation of typed view hierarchies for ORDBMS

[0002] The present invention relates to relational database management systems, and more particularly to typed view hierarchies for an object-relational database management system (ORDBMS).

[0003] More and more software applications are being written in object oriented languages such as C++ or Java. Over the past few years, object oriented database management systems (OODBMS) have been developed to serve this need. However, known object oriented database management systems do not provide the many features and the scalability of known is relational database management systems (RDBMS). In response to this need, vendors of relational database management systems have attempted to extend the functionality of their RDBMS products to capture the object oriented market. The result is an object-relational database management system (ORDBMS) which provides the application program with an object oriented view of its data while maintaining a relational treatment for queries and update operations.

[0004] The foundation of a relational database management system or RDBMS is a set of tables which hold the actual relational data. On top of the tables, the RDBMS provides a level of encapsulation known as “views”. The purpose of the views is to provide different “views” on the same set of data for different groups of clients. The different views protect both the data from being seen by unauthorized groups and the clients from being overwhelmed by information that is not useful. In addition, views provide pre-processing of the raw data.

[0005] It will be appreciated that for object-oriented applications a uniform table is too flat. In a RDBMS, all objects within a table are of the same form, i.e. the objects appear as homogeneous rows. As a result, an application that pushes various types of objects into the DBMS will need to add more semantics and thus the overhead for storing the object as a row is increased. When the object is queried these semantics need to be analyzed again by the application in order to deduce the type of the object. One approach to handling the additional semantics is to store each type of object in a separate table. Alternatively, the table can be enriched with additional typecode columns that need to be analyzed. Either way queries on these objects cannot be optimized by the DBMS since the DBMS is not aware of the added semantics and the mapping done by the application. Furthermore, the mapping imposes a higher load of coding on the application. The problem of mapping object oriented data into the relational model is often referred to as “impedance mismatch”.

[0006] The SQL standard now provides a facility for creating typed table hierarchies within the DBMS. The DB2 product available from IBM has realized this feature of pushing the mapping of different objects into rows of a physical table into the DBMS through the use of a so-called hierarchy table for a physical representation and through the generation of so-called logical tables serving as query targets on the table hierarchy to project and filter the rows in the hierarchy table to match the requested type. The SQL standard also now provides for a facility to expand the concept of views over tables to typed view hierarchies over typed table hierarchies.

[0007] Existing prior systems typically result in large access graphs having very poor performance. Essentially every object in the hierarchy is mapped without exploiting the meta information of the requested classes and the one stored in the structure of the hierarchies.

[0008] Accordingly, there still remains a need for enriching tables and views in a relational database management system with the concept of hierarchies and typed rows comprising classes and objects.

[0009] The present invention provides a method for defining an efficient representation for the complete hierarchy that can be shared by multiple references from different classes.

[0010] The method according to the invention features a dense implementation which allows subsequent query rewrite to eliminate all unneeded access to physical tables. Advantageously, this reduces the access to nearly the one chosen when not using the view at all. Moreover, this is transparent and scaleable. As a cookie, the present invention overcomes cheaply the hurdles of insert and updating through the hierarchies, which for a straight forward solution would be expensive.

[0011] According to the invention, all views in the hierarchy are merged into a hierarchy view through the use of case expressions for mapping of view types and wrapping of overloaded column. Advantageously during this procedure the underlying logical tables are eliminated which results in a more straight forward query graph. Since the transformation is a non-loss transformation and condenses the type information within the case-expressions, then update, delete and insert operations can be resolved in the graph on a local level. Query rewrite can simplify these case-expressions with well known general rules and this minimizes the query to the nearly optimal case by eliminating the case-expressions and using the resulting predicates to drop non accessed table hierarchy from the graph.

[0012] Reference will now be made to the accompanying drawings which show, by way of example, preferred embodiments of the present invention and in which:

[0013] FIG. 1(a) shows in diagrammatic form a hierarchy table and a hierarchy of logical tables according to a first aspect of the present invention;

[0014] FIG. 1(b) shows the hierarchy table of FIG. 1(a) in more detail;

[0015] FIG. 2 shows in schematic form an object view hierarchy on a single table hierarchy;

[0016] FIG. 3 shows in schematic form an object view hierarchy over multiple table hierarchies;

[0017] FIG. 4 shows in flow chart form a method for building a type map according to the present invention;

[0018] FIG. 5 shows in flowchart form a method for building a case expression in a select list according to another aspect of the present invention;

[0019] FIG. 6 shows in flowchart form a method for dispatching the user defined typed views to unified groups.

[0020] The present invention is described in the context of an object-relational database management system or ORDBMS, such the well-known DB2™ database program from IBM. The database management system comprises the combination of an appropriate central processing unit (i.e. computer), direct access storage devices (DASD) or disk drives, and the database management software or program. It will however be appreciated that the invention is applicable to other types of relational database management systems.

[0021] Reference is first made to FIG. 1(a) which shows in diagrammatic form a mechanism for typed tables in a relational database management system. The typed tables indicated generally by reference 10 comprise a hierarchy table 12 and a hierarchy of logical tables 14, indicated individually by 14a, 14b, 14cand 14d. The hierarchy table 12 as shown in FIGS. 1(a) and 1(b) comprises a physical data table containing all the columns that occur in all typed tables as represented by the logical tables 14.

[0022] As shown in FIG. 1(b), the hierarchy table 12 includes a typecode column indicated by 16 and object identifier (OID) column indicated by 18. The typecode column 16 is used to map a row to the proper type (i.e. table). The object identifier (OID) column 18 identifies the row uniquely within the table hierarchy as a distinct object.

[0023] As will now be described, the present invention provides an internal implementation for a view hierarchy defined by a user in an object-relational DBMS or ORDBMS.

[0024] The ORDBMS provides for the creation of typed object views, and these object views can either be root views or sub-views of other object views. The root view and its sub-views together form an object view hierarchy. A view hierarchy on a single table hierarchy is shown in FIG. 2 and described in more detail below A view hierarchy over a multiple table hierarchy is shown in FIG. 3 and also described in more detail below.

[0025] As will be described in more detail below with reference to examples, the object view hierarchy comprises the following features and attributes. The type used in a given object view definition is a structured type, just as in a typed table definition. The body of an object view definition is a SQL query, as is the case for regular views, but the select list of the query must be type-compatible with the declared type of the object view. As for a typed table, instances of object views have object id's, and the name of the object id column is specified in the view definition. The object id's are required to be unique within the view's object view hierarchy, and uniqueness is preferably checked at view definition time. Object views may contain references, just as base tables may, and the references must be scoped if they are to be de-referenced. The scope of a reference in an object view can be either an object view or a base table. For each object view in a view hierarchy, the body of the view or sub-view specifies how the objects that belong in that specific view or sub-view are to be obtained, i.e. each view/sub-view body tells the ORDBMS how to compute the contents of its particular virtual table within the overall hierarchy.

[0026] Reference is made to FIG. 2 which shows an object view hierarchy over a single table hierarchy 100. In a relational database management system, views are virtual tables whose contents are defined by a query. To a user's application or query, a view looks just like a table. The object view hierarchy is indicated generally by 102 and defined on a single table hierarchy 110 comprising a root table for persons 111, a sub-table for employees 112, a sub-table for managers 113 and a sub-table for students 114. The single table hierarchy 100 is defined by a user utilizing data definition language (DDL) statements in SQL for the ORDBMS having the following form;

[0027] (User) Create Table Type Hierarchy:

[0028] CREATE TYPE person_t AS(name varchar(20), birthdate date) ref using integer;

[0029] CREATE TYPE emp_t UNDER person_t AS(salary int, room char(4));

[0030] CREATE TYPE mgr_t UNDER emp_t AS(dept varchar(20), bonus int);

[0031] CREATE TYPE student_t UNDER person_t As(major varchar(20), grade char(1));

[0032] (User) Create Table Hierarchy:

[0033] CREATE TABLE person OF person_t(REF IS oid USER GENERATED);

[0034] CREATE TABLE emp OF emp_t UNDER person INHERIT SELECT PRIVILEGES;

[0035] CREATE TABLE mgr OF mgr_t UNDER emp INHERIT SELECT PRIVILEGES;

[0036] CREATE TABLE student OP student_t UNDER person INHERIT SELECT PRIVILEGES;

[0037] (User) Create View Type Hierarchy:

[0038] CREATE TYPE person_vt As(name varchar(20)) ref using integer;

[0039] CREATE TYPE emp_vt UNDER person_t AS(room char(4));

[0040] CREATE TYPE mgr_vt UNDER emp_t AS(dept varchar(20));

[0041] CREATE TYPE student_vt UNDER person_t AS(major varchar(20));

[0042] It will be appreciated that the types person_t, emp_t, mgr_t and student_t form a type hierarchy for modelling the various kinds of people of interest as given by this example. A subtype, e.g. emp_t, inherits all the attributes of its parent type, e.g. person_t. It will further be appreciated that types must exist before being referred to in a SQL statement. Furthermore, the optional clause “ref using Integer” in the statement that creates the root type person_t tells the ORDSMS to use integers as the internal representation type for references to objects of this type and its subtypes.

[0043] Internally in the ORDBMS, the table hierarchy 100 is defined by one physical table 120 or person_hier. The table 120 for person_hier holds all the columns plus the typecodes

[0044] as described in FIG. 1(b). Columns which are non-existent in a given sub-table are set to NULL in the person_hier table 120 (as shown in FIG. 1(b)). The root table 111 and the sub-tables 112, 113, 114 are described by logical tables which are represented by views as follows:

[0045] Internal (hierarchy table);

[0046] CREATE TABLE person_hier (typecode integer, oid integer, name varchar(20), birthday date, salary int, room char(4), dept varchar(20), bonus int, major varchar(20), grade char(1));

[0047] Internal (logical tables):

[0048] CREATE VIEW ONLY(emp)(oid, name, birthdate, salary, room)

[0049] AS SELECT emp_t(oid), name, birthdate, salary, room FROM person_hier

[0050] WHERE typecode IN (emp_tcode);

[0051] CREATE VIEW (emp)(oid, name, birthdate, salary, room)

[0052] AS SELECT person_t(oid), name, birthdate, salary, room FROM person_hier

[0053] WHERE typecode IN (emp_tcode, mgr_tcode, student_tcode);

[0054] When a typed table of the table hierarchy is referenced in a DMI statement (e.g. a select query), the system generates a fitting query target, i.e. logical table, that will fit the queried typed table as illustrated by the following examples:

[0055] SELECT*FROM ONLY(EMP),

[0056] CREATE VIEW emponly_qtarget(oid, . . . );

[0057] SELECT*FROM EMP:

[0058] CREATE VIEW emp_qtarget(oid, . . . );

[0059] To build the object view hierarchy 102, the ORDEMS is provided with a “create view” DDL statement in SQL. The user specifies the object view hierarchy 102 as follows:

[0060] (User) Create View Hierarchy:

[0061] CREATE VIEW person_v OF person_vt(REP IS oid USER GENERATED)

[0062] AS SELECT person_vt(int(oid)), name FROM ONLY(person);

[0063] CREATE VIEW emp_v OF emp_vt UNDER person_v INHERIT SELECT PRIVILEGES

[0064] AS SELECT emp_vt(int(oid)), name, room FROM ONLY(emp);

[0065] CREATE VIEW mgr_v OF mgr_vt UNDER emp_v INHERIT SELECT PRIVILEGES

[0066] AS SELECT mgr_vt(int(oid)), name, room, dept FROM ONLY(mgr);

[0067] CREATE VIEW student_v OF student_vt UNDER person_v

[0068] INHERIT SELECT PRIVILEGES

[0069] AS SELECT student_vt(int oid)) name, major FROM ONLY(student);

[0070] For the object view hierarchy 102, the first statement creates an object view 121 person_v of type person_vt with an object id column named oid. The body of the view tells the ORDBMS how to derive the extent of the view (including the object id values for the view objects). In this case, the SQL query that forms the body selects the oid and name columns from the person base table. The next three statements create the person_v view hierarchy. The second create view statement shown above creates an object view emp_v (shown as 122 in FIG. 2), of type emp_vt, In the body of the statement, the use of the ONLY keyword in the FROM clause (which can be used in most any SQL query in the DB2™ database program) instructs the ORDBMS to exclude sub-table rows from consideration. It is essentially a convenient shorthand for the equivalent type predicate and the object view emp_v will contain rows that are derived exactly from the emp_table. Similarly, the third create view statement creates an object view mgr_v(shown as 123 in FIG. 2), and the fourth create view statement creates an object view student_v (shown as 124 in FIG. 2).

[0071] For the DB2™ database program, in particular, the object ids in the object views are unique within their view hierarchy. In the case of the person_v hierarchy, this means that the database program checks (i) that the derivation of the oid column of emp_v is duplicate free, (ii) that the derivation of the oid column of mgr_v is duplicate free, (iii) that the derivation of the oid column of student_v is duplicate free, and (iv) that oid's are still unique across these views when considered together. In the context of the present invention, it is assumed that OID uniqueness is given.

[0072] Once defined by the user, object views can be used in queries, including path queries. The object views can also be updated if the defining SELECT statements are of an updatable nature.

[0073] Internally in the ORDBMS, the rows of all views/sub-views in a given object view hierarchy reside together in a single implementation view that is referred to as a hierarchy view or H-view. Although the H-view is not materialized as a physical entity, logically the H-view behaves similarly to a hierarchy table. Advantageously, this implementation allows most of the DB2™ query compiler code that deals with query and update operations on table hierarchies, translating them into corresponding H-table queries and updates internally, to be reused for operations on object view hierarchies according to the present invention. For example, when a user operates on a table in the person_v hierarchy 102 (FIG. 2), the ORDSMS (e.g. DB2™) is configured to internally translate the user's query into operations on temporary views that are based on the hierarchy's H-view. The generation of these internal views in DB2™ according to the invention is now described in greater detail.

[0074] According to this aspect of the invention, all object views in the hierarchy are merged into one view using case expressions for mapping and wrapping of overloaded columns. For the object view hierarchy 102 of FIG. 2, the case expression for the typecode mapping is as follows:

[0075] MAP(person_hier.typecode):

[0076] SELECT CASE WREN typecode IN (person_tcode) AND person_where

[0077] THEN person_vcode

[0078] WHEN typecode IN (emp_tcode) AND emp_where

[0079] THEN emp_vcode

[0080] WHEN typecode IN (mgr_code) AND mgr_where

[0081] THEN mgr_vcode

[0082] WHEN typecode IN(student_tcode) AND exec_where

[0083] THEN studejt_vcode END

[0084] AS typecode

[0085] FROM VALUES(1);

[0086] CREATE VIEW person_v hier (typecode, oid, name, room, dept, major)

[0087] As SELECT map.typecode, oid-expr,

[0088] CASE WHEN map.typecode IN (vc₁, . . . , vc_n)

[0089] THEN name-expr₁

[0090] WHEN map. typecode IN (vc_n+1, . . . , vc_m)

[0091] THEN name-expr₂END,

[0092] . . .

[0093] FROM person_hier, map(person_hier.typecode);

[0094] The view person_vhier contains a Case expression that is a wrapper in the select-list, i.e. namely the one for the name-column. The MALP query is the case expression which describes the type mapping.

[0095] It will be appreciated that the above representation for the view hierarchy provides the following advantages: (1) the representation of the view hierarchy is compact—the amount of Computations necessary for the view encapsulation is limited to the dispatch and mapping of typecodes and expressions. The latter of which can actually be removed by query rewrite in the common simple case. (2) the representation of the view hierarchy is free of the UNION ALL SQL statements, which means each row only has to be touched once. This makes access more efficient. (3) the representation allows data updates and inserts to be performed through analysis of case expressions only.

[0096] The representation of the view hierarchy according to the invention also accommodates existing relational, i.e. non-object, tables. Advantageously, this provides a migration path for users who have legacy relational data (i.e. existing data which non-object oriented), and wish to begin exploiting object-relational modelling for developing new applications. For example, an existing university database includes a pair of relational tables, emp_t and dept_t. The structure of the emp_t table is (eno, name, salary, deptno) and the structure of the dept_t table is (dno, name, mgrno), where the columns eno and dno are integer keys and the columns “mgrno” and “deptno” are corresponding foreign keys. The object views for the stored data in the legacy database is created using the following DDL SQL statements:

[0097] CREATE VIEW dept_v of dept_vt(REF IS oid USER GENERATED)

[0098] AS SELECT salary, name, emp_vt(mgrno)

[0099] FROM dept;

[0100] CREATE VIEW person_v of person_vt(REF IS oid USER GENERATED)

[0101] AS SELECT person_vt(eno), name

[0102] FROM emp

[0103] WHERE salary is null;

[0104] CREATE VIEW emp_v of emp_vt UNDER person_v

[0105] INHERIT SELECT PRIVILEGES

[0106] AS SELECT emp_vt(eno), name, salary

[0107] FROM emp

[0108] WHERE salary <100000;

[0109] ALTER VIEW dept_v ALTER COLUMN mgr ADD SCOPE emp_v;

[0110] In the above object view hierarchy for person_v, rows in the emp_v table with null salary values represent regular people, while rows with non-null salaries under $100,000 represent the employees that are to be made visible through the definition for the view emp_v. It will be appreciated that the object id's for the object views are derived from the primary keys of the legacy tables. Once the DDL statements are executed, the resulting dept_v and person_v view hierarchies will have the same behaviour in subsequent queries as the object views described above.

[0111] When a user operates on a table in the person_v hierarchy, ORDBMS (e.g. DB2™) internally translates the user's query into operations on temporary views for the view hierarchy, and having the following form. (These operations are based on the hierarchy's H-view.)

[0112] Map (emp.salary)

[0113] (Internal) CREATE VIEW emp_with_type_ids(type_id, eno, name, salary)

[0114] SELECT CASE WHEN salary IS NULL THEN 3100

[0115] WHEN salary 100000 THEN 3200

[0116] END as type_id,

[0117] (Internal) CREATE VIEW personv_hview(type_id, oid, name, salary)

[0118] AS SELECT type_id, eno,

[0119] CASE WHEN type_id IN (3100, 3200) THEN name ELSE NULL END,

[0120] CASE WHEN type_id IN (3200) THEN salary ELSE NULL END

[0121] FROM emp,map(emp.salary) WHERE type_id IS NOT FULL

[0122] The first view is the view emp_with_type_ids, which pre-pends one extra column, type_id, to rows of the legacy table (emp) upon which the user's view hierarchy was defined above. The purpose of the type_id column is to distinguish among legacy emp rows based on whether they should appear in the view person_v, the view emp_v or neither. This is done by attaching a type_id to each row which identifies its corresponding structured type (i.e. based on the user's view definitions) within the object view hierarchy. The case expression in the body of the emp_with_type_ids returns the value 3100 when the employee's salary is null, thereby classifying an emp row as belonging to the object view of type person_vt (i.e. person_v) if it satisfies the predicate provided in the user's view definition for the view person_v . Similarly, the second branch of the case expression is based on the predicate of the view emp_v, associating emp_vt's type id (3200) with those rows in emp_that satisfy the emp_v predicate. If neither branch of the case expression is satisfied, then the resulting type id will be null. This is appropriate as rows that satisfy neither predicate should appear in neither of the hierarchy's object views and a null type id will disqualify such rows from both.

[0123] The second internal view person_hview as shown above is the hierarchy view or H-view. The purpose of this view is to provide the view-equivalent of an H-table. As such, the person_v: hview has the union of the columns required to represent a row for any view/sub-view in the hierarchy. Root-level columns of person_hview can be selected directly from the view emp_with_type_ids, while sub-view columns (in this case the dept column for the sub-view emp_v) must be NULL for the rows that are not part of their sub-view of the hierarchy. In general, a column introduced by a sub-view must be NULL in all rows pertaining to superviews. Since a sub-view may have “sibling” views, it is necessary to make sure that the column is NULL in all views except those in the sub-hierarchy rooted at the sub-view that introduced the column in question. As shown above, the body of the view person_hview includes a case expression that computes its dept column. It will be appreciated that the case expression not only discriminates between NULL and non-NULL cases, but also among the different expressions that apply for computing the column value for a given row.

[0124] In addition to the hidden temporary views, the ORDBMS generates an internal realization of possible object view query targets having the following form:

[0125] INTERNAL VIEW vperson_qtarget (oid, name)

[0126] (REF IS oid USER GENERATED)

[0127] AS SELECT person_vt(eno), name

[0128] FROM personv_hview

[0129] WHERE type_id IN (3100, 3200);

[0130] INTERNAL VIEW vemp_qtarget OF emp_vt UNDER person_v

[0131] (dept WITH OPTIONS SCOPE dept_v)

[0132] AS SELECT emp_vt(eno), name, dept

[0133] FROM personv_hview

[0134] WHERE type_id IN (3200);

[0135] The headers, i.e. “Interfaces”, of these internal views are identical to those of the user's view definitions, but the bodies have been reformulated in terms of the hierarchy view. The changes to the view body are that (i) the columns are taken verbatim from the H-view (except for oid caste), and (ii) filtering of rows is now done via the computed type id,s rather than via the user's original predicates since the type id's themselves were computed based on the user's predicates. It is to be appreciated that while the above described object view hierarchy is described for a legacy database, the implementation details are the same for object views on object tables or for object views upon object views upon object views.

[0136] Reference is next made to FIG. 3 which shows an object view hierarchy over multiple table hierarchies 200. The object view hierarchy is indicated generally by 202 and is defined on four table hierarchies 210, 220, 230 and 240. The first table hierarchy 210 comprises a root table for persons 211, a sub-table for employees 212, a sub-table for managers 213 and a sub-table for students 214. The second table hierarchy 220 comprises a root table for other employees 221 and a sub-table for managers 222. The third table hierarchy 230 comprises a single root table for managers 231, and the fourth table hierarchy 240 comprises a single root table 241 for students. The table hierarchies 210, 220, 230 and 240 are defined by a user utilizing data definition language (DDL) statements as follows;

[0137] (User) Create Table Hierarchy A (Covers the whole previous hierarchy):

[0138] CREATE TABLE person_a OF person_t (REF IS oid USER GENERATED);

[0139] CREATE TABLE emp_a OF emp_t UNDER person_a INHERIT SELECT PRIVILEGES;

[0140] CREATE TABLE mgr_a OF mgr_t UNDER emp_a INHERIT SELECT PRIVILEGES;

[0141] CREATE TABLE student_a OF student_t UNDER person_a INHERIT SELECT PRIVILEGES;

[0142] (User) Create Table Hierarchy B (Missing the students and persons).

[0143] CREATE TABLE emp_b OF emp_t (REF IS oid USER GENERATED);

[0144] CREATE TABLE mgr_b OF mgr_t UNDER emp_b INHERIT SELECT PRIVILEGES;

[0145] (User) Create Table Hierarchy C (only managers):

[0146] CREATE TABLE mgr_c OF mgr_t (REF IS oid USER GENERATED);

[0147] (User) Create Table Hierarchy D (only students):

[0148] CREATE TABLE student_d OF student_t (REF IS oid USER GENERATED);

[0149] This example shows how each of the user defined sub-views can be merged into a single hierarchy view consisting of as many UNION ALL branches as there are physical hierarchy tables. The merging is performed by type mapping using case expressions.

[0150] Internally in the ORDBMS, the table hierarchies 200 are defined by four physical tables 250, 251, 252 and 253. The first physical table 250 or PersonA_hier is for persons. The second physical table 251 or EmpB_hier is for employees. The third physical table 252 or MgrC_hier is for managers and the fourth physical table 253 or StudentD_hier is for students.

[0151] Next the object view hierarchy 202 matching the objects from the different branches is built using the “create views” statement (as described above) and shown below:

[0152] (User) Create view Hierarchy:

[0153] CREATE VIEW person_v OF person_vt(REF IS oid USER GENERATED

[0154] UNCHECKED)

[0155] AS (SELECT person_vt(integer(oid)), name FROM ONLY(person_a);

[0156] CREATE VIEW emp_v OF emp_vt UNDER person_v INHERIT SELECT PRIVILEGES

[0157] AS (SELECT emp_vt(integer(oid)), name, room FROM ONLY(emp_a))

[0158] UNION ALL

[0159] (SELECT emp_vt(integer(oid)), name, room FROM ONLY(emp_b));

[0160] CREATE VIEW mgr_v OF mgr_vt UNDER emp_v INHERIT SELECT PRIVILEGES

[0161] AS (SELECT mgr_vt(integer(oid)), name, room, dept

[0162] FROM ONLY(mgr_a)

[0163] UNION ALL

[0164] (SELECT mgr_vt(integer(oid)), name, room, dept FROM ONLY(mgr_b))

[0165] UNION ALL

[0166] (SELECT mgr_vt(integer(oid)), name, room, dept FROM ONLY(mgr_c));

[0167] CREATE VIEW student_v OF student_vt UNDER person_v

[0168] INHERIT SELECT PRIVILEGES

[0169] AS (SELECT student_vt(initeger(oid)), name, major

[0170] FROM ONLY(student_a))

[0171] UNION ALL

[0172] (SELECT student_vt(integer(oid)), name, major

[0173] FROM ONLY(student_d));

[0174] Next as described above, the object views are merged into one view hierarchy using case expressions for the typecode mapping as follows:

[0175] CREATE VIEW person_vhier (typecode, oid, name, room, dept, major) AS

[0176] (SELECT CASE WHEN typecode IN (person_tcode) THEN person_vcode

[0177] WHEN typecode IN (emp_tcode) THEN emp_vcode

[0178] WHEN typecode IN (mgr_tcode) THEN mgr_vcode

[0179] WHEN typecode IN (student_tcode) THEN student_vcode

[0180] END

[0181] AS typecode, oid, name, room, dept, major

[0182] FROM person_a_hier

[0183] UNION ALL

[0184] (SELECT CASE WHEN typecode IN (emp_tcode) THEN emp_vcode

[0185] WHEN typecode IN (mgr_tcode) THEN mgr_vcode END

[0186] AS typecode, oid, name, room, dept, NULL

[0187] FROM emp_b_hier)

[0188] UNION ALL

[0189] (SELECT CASE WHEN typecode IN (mgr_tcode) THEN mgr_vcode END

[0190] AS typecode, oid, name, room, dept, NULL

[0191] FROM mgr_c_hier)

[0192] UNION ALL

[0193] (SELECT CASE WHEN typecode IN (student_tcode) THEN student_vcode

[0194] END

[0195] AS typecode, oid, name, NULL, NULL, major

[0196] FROM student_d_hier);

[0197] The illustrated technique of pushing down the type-mapping into correlated sub-queries and the wrapping of the expressions in the select lists allows for overloading of the expressions in the view definitions. Inserts are possible if the definition of the sub-view body does not contain a UNION ALL operator. In the above example, this is the case only for the view person_v. The person_vcode occurs only in one case-expression and therefore is the only possible target of an insert into the view person_v . Similarly all possible targets for an update may be found. Since updates on the view emp_v can result in updates on emp_v and mgr_v (i.e. all of emp_v's sub-views), targets are all branches of the union containing emp_vcode or mgr_vcode. This information may also be used to find applicable check-constraints for insert and update and which triggers will need to be fired on the UDI query. In addition, the case-expressions together with the where-predicates of the logical view ranging over the hierarchy view person_vhier enhance “query rewrite” operations as will be described in more detail below.

[0198] The following more general examples are now considered with reference to FIGS. 4 to 6.

[0199] For an object view hierarchy over a single table hierarchy, the user defines a table type hierarchy which takes the following general form:

[0200] CREATE TYPE ttype1 AS (c11 INT, c12 INT) REF USING INT;

[0201] CREATE TYPE ttype2 UNDER ttype1 AS (c21 INT, c22 INT);

[0202] CREATE TYPE ttype3 UNDER ttype2 AS (c31 INT, c32);

[0203] CREATE TYPE ttype4 UNDER ttype1 AS (c41 INT, c42);

[0204] Next the user defines a table hierarchy as follows;

[0205] CREATE TABLE t1 OF ttype1 (REF IS oid USER GENERATED);

[0206] CREATE TABLE t2 OF ttype2 UNDER t1 INHERIT SELECT PRIVILEGES;

[0207] CREATE TABLE t3 OF ttype3 UNDER t2 INHERIT SELECT PRIVILEGES;

[0208] CREATE TABLE t4 OF ttype4 UNDER t1 INHERIT SELECT PRIVILEGES;

[0209] Internally, the hierarchy table, t1_hier, is created in the ORDBMS as follows:

[0210] CREATE TABLE t1_hier (ttc INT, oid INT c11 INT, c12 INT, c21 INT, c22 INT, c31 INT, c33 INT, c41 INT, c42 INT);

[0211] Next, the logical tables are created internally in the ORDBMS as follows;

[0212] CREATE VIEW “t1only_qtarget”

[0213] AS SELECT ttype1(oid), c11, c12 FROM t12_hier

[0214] WHERE ttc IN (<ttc1>);

[0215] CREATE VIEW “t2only_qtarget”

[0216] AS SELECT ttype2(oid), c11, c12, c21, c22

[0217] WHERE ttc IN (<ttc1>);

[0218] CREATE VIEW “t3only_qtarget”

[0219] As SELECT ttype3(oid), c11, c12, c22, c31, c32

[0220] FROM t1_hier

[0221] WHERE ttc IN (<ttc3>);

[0222] CREATE VIEW “t4only_qtarget,

[0223] AS SELECT ttype4(oid), c11, c12, c41, C42

[0224] FROM t1_hier

[0225] WHERE ttc IN (<ttc4>);

[0226] For a single table hierarchy, the user creates a view hierarchy having the following form:

[0227] CREATE TYPE vtype1 AS (vc1 INT) REF USING INT;

[0228] CREATE TYPE vtype2 UNDER vtype1 AS (vc2 INT);

[0229] CREATE TYPE vtype3 UNDER vtype2 As (vc3 INT);

[0230] CREATE TYPE vtype4 UNDER vtype1 AS (vc4 INT);

[0231] CREATE VIEW v1 OF vtype1 (REF IS oid USER GENERATED)

[0232] AS SELECT vtype1 (INT(oid)), c11

[0233] FROM ONLY (t1)

[0234] WHERE (c12>c11)

[0235] CREATE VIEW v2 OF vtype2 UNDER v1 INHERIT SELECT PRIVILEGES

[0236] AS SELECT vtype2 (INT(oid)), c11, c21

[0237] FROM ONLY (t2)

[0238] WHERE (c12>c22),

[0239] CREATE VIEW v3 OF vtype3 UNDER v3 INHERIT SELECT PRIVILEGES

[0240] AS SELECT vtype3 (INT(oid)), c11, c21, c31

[0241] FROM ONLY (t3)

[0242] WHERE (c12>c31):

[0243] CREATE VIEW v4 OF vtype4 UNDER v1 INHERIT SELECT PRIVILEGES

[0244] AS SELECT vtype4 (INT(oid)), c11, c41

[0245] FROM ONLY (t4)

[0246] WHERE (c42=5);

[0247] In the ORDBMS, the hierarchy view is merged using CASE expressions for mapping of types with WHERE causes in views as follows:

[0248] CREATE VIEW v1_hier

[0249] AS SELECT vtc, oid, c1 as vc1, c21 as vc2, c31 as vc3, c41 as vc4

[0250] FROM t1_hier,

[0251] (SELECT CASE WHEN t1_hier.ttc IN (<ttc1>)

[0252] AND (C12>C11) THEN <vtc1>

[0253] WHEN t1_hier.ttc IN (<ttc2>)

[0254] AND (c12>c22) THEN <vtc2>

[0255] WHEN t1_hier.ttc IN (<ttc3>)

[0256] AND (c21>C31) THEN <vtc3>

[0257] WHEN t1_hier.ttc IN (<ttc4>)

[0258] AND (c42 =5) THEN <vtc4>

[0259] ELSE NULL END FROM VALUES(1))

[0260] AS typemap (vtc)

[0261] WHERE vtc IS NOT NULL;

[0262] The logical views are generated internally in the ORDBMS as follows:

[0263] CREATE VIEW “only(v1)”

[0264] As SELECT vtype1(oid), vc1 FROM v1 hier

[0265] WHERE vtc IN (<vtc1>);

[0266] CREATE VIEW “only(v2)”

[0267] AS SELECT vtype2(oid), vc1, vc2

[0268] FROM v1_hier

[0269] WHERE vtc IN (<vtc1>);

[0270] CREATE VIEW “only(v3)”

[0271] AS SELECT vtype3(oid), vc1, vc2, vc3

[0272] FROM v1_hier

[0273] WHERE vte IN (<vtc3>);

[0274] CREATE VIEW “only(v4)”

[0275] AS SELECT vtype4(oid), vc1, vc4

[0276] FROM v1_hier

[0277] WHERE vtc IN (<vtc4>);

[0278] Reference is also made to FIG. 4 which shows in flowchart form a method for building a type map or “typemap” according to the present invention denoted generally by reference 300.

[0279] As shown in FIG. 4, the first step indicated by block 301 comprises getting the first view in the hierarchy and generating an empty case “CASE ELSE NULL END”. The next step in building the type map in block 302 comprises the following operations. Getting the table specified in the view body's FROM statement. This table is a logical table and has an IN predicate which describes its type, for example,

[0280] ONLY(empt)<==>type IN (<emp_type>)

[0281] empt<=>

[0282] type IN (<emp_type<, <mgr_type>)

[0283] The next operations in block 302 involve getting the typecode of the view and also the IN-predicate which describes the view (i.e. same principle as for a logical table). The next step in block 303 involves mapping the table type to the view type and comprises adding a new WHEN to the case expression:

[0284] t1only_qtarget<==>ttc IN (<ttc1>)

[0285] t1_qtarget<==>ttc IN (<ttc1>, <ttc2>, <ttc3>, <ttc4)

[0286] and THEN

[0287] WHEN ttc IN (<ttc>) AND (c12>c11) THEN <vtc1>

[0288] If there are other views, these steps are repeated (blocks 304 and 305), otherwise the type map is complete.

[0289] Next the merging of an object view hierarchy over a single table hierarchy using column overloading is described. The user creates a view type hierarchy having the following form:

[0290] CREATE TYPE vtype1 AS (vc1 INT) REF USING INT;

[0291] CREATE TYPE vtype2 UNDER vtype1 AS (vc2 INT);

[0292] CREATE TYPE vtype3 UNDER vtype2 AS (vc3 INT);

[0293] CREATE TYPE vtype4 UNDER vtype1 AS (vc4 INT);

[0294] CREATE VIEW v1 OF vtype1 (REF IS oid USER GENERATED)

[0295] AS SELECT vtype1(INT(oid)), c11

[0296] FROM ONLY(t1)

[0297] CREATE VIEW v2 OF vtype2 UNDER v1 INHERIT SELECT PRIVILEGES

[0298] AS SELECT vtype2(INT(oid)), c11+c12, c21

[0299] FROM ONLY(t2);

[0300] CREATE VIEW v3 OF vtype3 UNDER v3 INHERIT SELECT PRIVILEGES

[0301] AS SELECT vtype3(INT(oid)), c11, c21 +c31, c31-c32

[0302] FROM ONLY(t3);

[0303] CREATE VIEW v4 OF vtype4 UNDER v1 INHERIT SELECT PRIVILEGES

[0304] AS SELECT vtype4(INT(oid)), c11*c42, c41

[0305] FROM ONLY(t4);

[0306] Internally, the ORDBMS creates the following hierarchy view:

[0307] CREATE VIEW v1_hier

[0308] AS SELECT vtc, oid,

[0309] CASE WHEN vtc IN (<vtc1>, <vtc3>) THEN c11

[0310] WHEN vtc IN (<vtc2>) THEN c11+c12

[0311] WHEN vtc IN (<vtc4>) then c11+c42

[0312] ELSE NULL END AS vc1,

[0313] CASE WHEN vtc IN (<vtc2>) THEN c21

[0314] WHEN vtc IN (<vtc3>) then c21+c31

[0315] ELSE NULL END AS vc2,

[0316] CASE WHEN vtc IN (<vtc3>) THEN c31−c32

[0317] ELSE NML END AS vc3,

[0318] CASE WHEN vtc IN (<vtc4>) THEN c41

[0319] ELSE NULL END AS vc4,

[0320] FROM t1_hier, (SELECT CASE . . . ) AS typemap(vtc)

[0321] WHERE vtc IS NOT NULL;

[0322] Reference is now made to FIG. 5 which shows a method for building a case expression of column vcy in the select list of the hierarchy view described above. In FIG. 5, the method for building a case expression is denoted generally by reference 400.

[0323] As shown in FIG. 5, the first step (block 401) in the method for building a case expression 400 comprises generating an empty case starting with the first view, i.e. the root view. The next step in block 402 involves getting the expression making up the view-column vcy in the view definition at hand. It will be appreciated that not all columns in the view hierarchy are in all the views, e.g. an emp will not have a bonus-column. Next, a check is made in block 403 to determine if an expression exists for this view. if an expression exists for vcy, then in block 404 a search is made to find the expression in the WHEN <pred> THEN <expr> branches of the case expression. If the expression is found (block 405), then only the typecode of this view is added to the IN-predicate of the found WHEN (block 406). Otherwise, there is “column overloading”. The expression is new and gets its own WHEN branch in the CASE expression (block 407). These steps are repeated for all the views (blocks 408 and 409).

[0324] As described above, the present invention is also applicable to multiple underlying table hierarchies, as will now be generalized with reference to the following example and FIG. 6. The user creates the following table type and table hierarchies:

[0325] CREATE TYPE ttype1 AS (c11 INT, c12 INT) REF USING INT;

[0326] CREATE TYPE ttype2 UNDER ttype1 AS (c21 INT, c22 INT);

[0327] CREATE TYPE ttype3 UNDER ttype2 AS (c31 INT, c32);

[0328] CREATE TYPE ttype4 UNDER ttype1 AS (c41 INT, c42);

[0329] CREATE TABLE t1a OF ttype1 (REF IS oid USER GENERATED);

[0330] CREATE TABLE t2a OF ttype2 UNDER t1a INHERIT SELECT PRIVILEGES;

[0331] CREATE TABLE t4a OF ttype4 UNDER t1a INHERIT SELECT PRIVILEGES;

[0332] CREATE TABLE t2b OF ttype2 (REF IS oid USER GENERATED);

[0333] CREATE TABLE t3b OF ttype3 UNDER t2b INHERIT SELECT PRIVILEGES;

[0334] Internally, the following hierarchy table is created by the ORDBMS:

[0335] CREATE TABLE t1a_hier (ttc INT, oid INT c11 INT, c12 INT, c21 INT, c22 INT, c41 INT, C42 INT);

[0336] CREATE TABLE t2b_hier (ttc INT, oid INT c11 INT, c12 INT, c21 INT, c22 INT, c31 INT, C33 INT);

[0337] Internally, the following logical tables are also created;

[0338] CREATE VIEW “t1a_qtarget”

[0339] AS SELECT ttype1(oid), c11, c12 FROM t1a_hier

[0340] WHERE ttc IN (<ttc1>);

[0341] CREATE VIEW “onlyt2a_qtarget”

[0342] AS SELECT ttype2(oid), c11, c12, c21, c22

[0343] FROM t1a_hier

[0344] WHERE ttc IN (<ttc1>);

[0345] CREATE VIEW “t4a_qtarget”

[0346] AS SELECT ttype4(oid), c11, c12, c41, c42

[0347] FROM t1a_hier

[0348] WHERE ttc IN (<ttc4>);

[0349] CREATE VIEW “t2b_qtarget”

[0350] AS SELECT ttype2(oid), c11, c12, c21, c22

[0351] FROM t1b_hier

[0352] WHERE ttc IN (<ttc1>);

[0353] CREATE VIEW “t3b_qtarget”

[0354] AS SELECT ttype3(oid), c11, c12, c21, c22, c31, c32

[0355] FROM t1b_hier

[0356] WHERE ttc IN (<ttc3>);:

[0357] For an object view hierarchy spanning both table hierarchies, the user creates a view hierarchy having the following form:

[0358] CREATE TYPE vtype1 AS (vc1 INT) REF USING INT;

[0359] CREATE TYPE vtype2 UNDER vtype1 AS (vc2 INT);

[0360] CREATE TYPE vtype3 UNDER vtype2 AS (vc3 INT);

[0361] CREATE TYPE vtype4 UNDER vtype1 AS (vc4 INT);

[0362] CREATE VIEW v1 OF vtype1 (REP IS oid USER GENERATED)

[0363] AS SELECT vtype1(INT(oid)), c11

[0364] FROM ONLY (t1a)

[0365] WHERE (c12>c11),

[0366] CREATE VIEW v2 OF vtype2 UNDER v1 INHERIT SELECT PRIVILEGES

[0367] AS (SELECT vtype2(INT(oid)), c11, c21

[0368] FROM ONLY(t2a))

[0369] UNION ALL

[0370] (SELECT vtype2(INT(oid)), c11, c21

[0371] FROM ONLY(t2b));

[0372] CREATE VIEW v3 OF vtype3 UNDER v3 INHERIT SELECT PRIVILEGES

[0373] AS SELECT vtype3(INT(oid)), c11, c21, c31

[0374] FROM ONLY (t3b);

[0375] CREATE VIEW v4 OF vtype4 UNDER v1 INHERIT SELECT PRIVILEGES

[0376] AS SELECT vtype4(INT(oid)), c11, c41

[0377] FROM ONLY (t4a);

[0378] Internally, the following hierarchy view is created:

[0379] CREATE VIEW v1_hier

[0380] AS (SELECT vtc, oid, c11 as vc1, c21 as vc2,

[0381] NULL as vc3, c41 as vc4

[0382] FROM t1a_hier,

[0383] (SELECT CASE WHEN t1a_hier.ttc IN (<ttc1>)

[0384] WHEN t1a_hier.ttc IN (<ttc2>)

[0385] THEN <vtc2>)

[0386] WHEN t1a_hier.ttc IN (<ttc4>)

[0387] THEN (<vtc4>)

[0388] ELSE NULL END FROM VALUES(1))

[0389] AS typemap (vtc)

[0390] WHERE vtc IS NOT NULL)

[0391] UNION ALL

[0392] (SELECT vtc, oid, c11 as vc1, c21 as vc2,

[0393] c31 as vc3, NULL as vc4

[0394] FROM t2b_hier,

[0395] (SELECT CASE WHEN t2b_hier.ttc IN (<ttc2>)

[0396] THEN <vtc2>

[0397] WHEN t2b_hier.ttc IN (<ttc3>)

[0398] THEN <vtc3>)

[0399] ELSE NULL END FROM VALUES(1))

[0400] AS typemap (vtc)

[0401] WHERE vtc IS NOT NULL);

[0402] Internally, the following logical views are also created;

[0403] CREATE VIEW “only (v1)”

[0404] AS SELECT vtype oid), vc1 FROM v1_hier

[0405] WHERE vtc IN (<vtc1>);

[0406] CREATE VIEW “only(v2)”

[0407] AS SELECT vtype2(oid), vc1, vc2

[0408] FROM v1_hier

[0409] WHERE vtc IN (<vtc1>);

[0410] CREATE VIEW “only(v3)”

[0411] AS SELECT vtype3(oid), vc1, vc2, vc3

[0412] FROM v1_hier

[0413] WHERE vtc IN (<vtc3>);

[0414] CREATE VIEW “only(v4)”

[0415] AS SELECT vtype4(oid), vc1, vc4

[0416] FROM v1_hier

[0417] WHERE vtc IN (<vtc4>);

[0418] Reference is made to FIG. 6 which shows in flowchart form a method for dispatching the views over hierarchy branches according to this aspect of the present invention.

[0419] As shown in FIG. 6, the first step indicated by block 501 comprises getting the first SELECT branch of the UNION ALL in the body of the current view. The next step indicated by block 502 involves the following operations: finding first the logical table in the FROM clause of the SELECT-branch; then getting the hierarchy table that the logical table belongs to; and getting the branch in the expanded hierarchy view that ranges over the hierarchy table. The next step in block 503 involves determining if a branch in the hierarchy view exists. If a branch does not exist, then a new branch is created in the hierarchy view with the hierarchy table in the FROM and an empty type-mapping as shown in block 504. Next in block 505, the new branch is added to the UNION ALL of the hierarchy view. If a branch in the hierarchy view exists (block 503), then the steps in blocks 504 and 505 are bypassed. Next in block 506, the SELECT in the view body is merged into the found or created branch in the hierarchy view. The next step in block 507 involves getting the next SELECT branch in the body of the current view If another SELECT branch exists (block 508), then the above merging steps are repeated, otherwise the merging is complete.

[0420] In the operation of the ORDBMS, the query rewrite phase can simplify the query graph by determining branches which do not contain information for the query and also allows the exploitation of indexes in the underlying hierarchy table. This reduces locking and increases query performance in the ORDRMS. The query rewrite simplifies the case-expressions by applying known general rules which minimize the query graph to the nearly optimal case by eliminating the case-expressions and using the resulting predicates to drop the non-accessed table hierarchy from the graph.

[0421] The multi-table hierarchy example from above is used to illustrate the query rewrite phase.

[0422] CREATE VIEW person_v OF person_vt (REP IS oid USER GENERATED)

[0423] AS SELECT person_v(integer(oid), name FROM ONLY(person_a);

[0424] CREATE VIEW emp_v OF emp_vt UNDER person_v INHERIT SELECT PRIVILEGES

[0425] AS (SELECT emp_v(integer(oid), name, room FROM ONLY(emp_a))

[0426] UNION ALL

[0427] (SELECT emp_v(integer(oid), name, room FROM ONLY(emp_b))

[0428] The user invokes the following query:

[0429] SELECT oid, name FROM only(person_v) WHERE name=‘Smith’;

[0430] According to the present invention, the full expanded query including the hierarchy view is as follows (for clarity only the used columns are shown):

[0431] SELECT oid FROM person_v_only_qtarget WHERE name=‘Smith”;

[0432] person_v_only_qtarget:

[0433] SELECT person_vt(oid), name FROM person_vhier

[0434] WHERE vtc IN (<person_vtc>);

[0435] person_vhier;

[0436] (SELECT map_a.vtc, oid,

[0437] CASE WHEN map_a.vtc IN (<person_vtc>, <emp_vtc>)

[0438] THEN name ELSE NULL END,

[0439] FROM person_a_hier, mappa(person_a_hier.typecode))

[0440] UNION ALL

[0441] (SELECT map_b.vtc, oid,

[0442] CASE WHEN map_a.vtc IN (<emp_vtc>) THEN name ELSE NULL END,

[0443] FROM emp_b_hier, map_b (emp_b_hier.typecode));

[0444] mappa(person_a_hier.typecode):

[0445] SELECT CASE WHEN typecode IN (<person_ttc>) THEN <person_vtc>

[0446] WHEN typecode IN (<emp_ttc) THEN <emp_vtc)

[0447] ELSE NULL END AS vtc FROM VALUES(1);

[0448] map_b(emp_b_hier.typecode);

[0449] SELECT CASE WHEN typecode IN (<emp_ttc) THEN <emp_vtc)

[0450] ELSE NULL END AS vtc FROM VALUES(1);

[0451] Step 1: Merge the selects together and push them through the UNION ALL operator:

[0452] (SELECT person_vt(oid),

[0453] CASE WHEN map_a.vtc IN (<person_vtc>, <emp_vtc>)

[0454] THEN name ELSE NULL END,

[0455] FROM person_a_hier, mapa(person_a_hier.typecode)

[0456] WHERE map_a.vtc IN (<person_vtc>) AND

[0457] (CASE WHEN map_a.vtc IN (<person_vtc>, <emp_vtc>)

[0458] THEN name ELSE NULL END=‘Smith’))

[0459] UNION ALL

[0460] (SELECT person_vt(oid),

[0461] CASE WHEN map_a.vtc IN (<emp_vtc>) THEN name ELSE NULL END,

[0462] FROM emp_b_hier, map_b(emp_b_hier.typecode)

[0463] WHERE map_a.vtc IN (<person_vtc>) AND

[0464] (CASE WHEN map_a.vtc IN (<person_vtc>, <emp_vtc>)

[0465] THEN name ELSE NULL END=‘Smith’));

[0466] Step 2: Simplify the case wrapper that was pulled into the where-clause. The WHERE clause claims that map_a.vtc must be <person_vtc>. So the case can be dropped and replaced by the qualifying when-branches expression. If several when predicates would qualify a list OR'ed predicates is generated. If none qualify (as in the second branch of the example) a FALSE is generated. The case wrappers can also be simplified in this example.

[0467] (SELECT person_vt(oid), name,

[0468] FROM person_a_hier, mapa(person_a_hier.typecode)

[0469] WHERE map_a.vtc IN (<person_vtc>) AND name=‘Smith’)

[0470] UNION ALL

[0471] (SELECT person_vt(oid), NULL,

[0472] FROM emp_b_hier, map_b(emp_b_hier.typecode)

[0473] WHERE map_a.vtc IN (<person_vtc>) AND FALSE;

[0474] Step 3: Theorem prover and dropping of branches. The second WHERE clause is FALSE, so the branch of the UNION ALL can be dropped. A UNION ALL with only one branch is a trivial operation and can be omitted

[0475] SELECT person_vt(oid), name,

[0476] FROM person_a_hier, mapa(person_a_hier.typecode)

[0477] WHERE map_a.vtc IN (<person_vtc>) AND name=‘Smith’;

[0478] Step 4: Pull up the correlated subquery

[0479] SELECT person_vt(oid), name,

[0480] FROM person_a_hier, VALUES(1)

[0481] WHERE CASE WHEN typecode IN (<person_ttc>) THEN <person_vtc>

[0482] WHEN typecode IN (<emp_ttc) THEN <emp_vtc)

[0483] A ELSE NULL END IN (<person_vtc>) AND name=‘Smith’;

[0484] Step 5: Simplify the case expression in the WHERE clauses The outer IN predicate can only be true if the case expression resolves to it. Therefore the “typecode IN (<person_ttc>)” must be true to succeed. So the whole predicate can be replaced by the when predicate. If multiple possibilities exist (multiple values in the IN-list), the when-predicates are OR'ed.

[0485] SELECT person_vt(oid), name,

[0486] FROM person_a_hier, VALUES(1)

[0487] WHERE typecode IN (<person_ttc>) AND name=‘Smith’;

[0488] If an index on typecode or: name exists it can be exploited since it is ranging immediately over the physical table. Also check-constraints on name can help to validate if ‘Smith’ can be in the table at all.

[0489] If the branches do not drop away as easily as in this example, e.g. doing a select on the whole hierarchy for a specific oid, then the ORDBMS can still exploit information it may have on the content of the single branches. For example, the definition of range constraints for the oid's in each table hierarchy would cause query rewrite again to drop branches that are out of range for the required oid. The dropping of these branches becomes especially important in an environment that joins different data sources with either lots of interface overhead or a slower connection to a remote data source. Advantageously, the present invention minimizes the access and provides a complete encapsulation of the underlying relational groundwork.

[0490] The present invention may be embodied in other specific forms without departing from the spirit or essential characteristics thereof. Therefore, the presently discussed embodiments are considered to be illustrative and not restrictive, the scope of the invention being indicated by the appended claims rather than the foregoing description, and all changes which come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein.