Designing the types

Structured and Unstructured Data

Structured data is one whose type is expressible to Oracle in the form of an Object Type. Unstructured data is one which is un-interpretable by Oracle, that is, whose type is a RAW or a BLOB. The choice of modeling cartridge data as structured or unstructured depends on the following considerations:

1. Structured data can be shared by different applications since the structure is published in Oracle.

2. Structured types provide strong type checking whereas unstructured data does not.

3. Structured data is easily queried whereas unstructured data is not. One has to publish user-defined functions to facilitate querying the unstructured data.

4. Constraints are easily supported on structured data but not on unstructured data.

5. Indexes are easily supported on structured data, whereas, on unstructured data indices on user-defined functions would need to be created, or extensible indexes would need to be defined.

6. Structured data needs to be marshalled by Oracle to be retrieved to client as a value, whereas, unstructured data is easily retrievable as a value.

Using Nested Tables or VARRAYs

In deciding whether to use a nested table or a VARRAY type to model a collection, it is important to understand how these are implemented in Oracle to make the right design choice. Logically, nested tables differ from VARRAYs in one fundamental way: VARRAYs represent ordered set of items whereas nested tables do not. But, physically, nested tables can only be represented as tables, whereas VARRAYs can only be represented as raw columns or LOB columns. The implications of their physical representation is the following:

Nested Tables

• Nested tables are better suited for querying since elements are represented as rows.

• Indices may be created on columns of nested tables for faster searches.

• Constraints may be specified for nested tables.

• Clustering of nested elements belonging to a common parent row is possible when the storage table is specified as an Index Organized Table, furthermore, specifying key compression reduces the overhead of the system assigned NESTED_TABLE_ID values.

• When stored as a Heap Organized Table, creating an index on the NESTED_TABLE_ID column enhances retrieval of nested tables.

• Retrieving the nested table as a value for a given parent incurs the overhead of selecting and marshalling the individual rows to form the collection value.

• Even though parent tables may be partitioned, storage tables corresponding to their nested tables cannot be partitioned.

VARRAYs

• A VARRAYs is better suited for retrieval as a value since that is s how it is stored.

• Support for indexing, specification of constraints on VARRAYs is not available.

• Querying of VARRAYs is sub-optimal since rows have to materialized from collection value.

• Partitioning of VARRAYs stored as LOBs is permitted when the parent table is partitioned.

Based on the above implications, if the ability to query of update individual collection elements is important, then nested tables are a better choice to model your collection data. On the other hand, if your application is requires fetching the entire collection as a whole and then operating on it, modeling the collection data as a VARRAY will yield better retrieval performance.

Working Around Inheritance

Inheritance is a technique used in object-oriented development to create objects that contain generalized attributes and behavior for groups of related objects. The more general objects created using inheritance are referred to as a super-types. The objects that "inherit" from the super-types (i.e. are more specific cases of the super-type) are called subtypes.

A common case of inheritance is that of Person and Employee. Some instances of person are employees. The more general case, Person, is the super-type and the special case, Employee, the sub-type. Another example could involve a Vehicle as super-type and Car, Truck as its sub-types.

Figure 10-1 Class Diagram: Vehicle as Super-type, Car and Truck as Subtypes

Inheritance Implementation Consequences

Inheritance can imply various levels of encapsulation for super-types. In cases where the super-type should not be exposed to other objects, a subtype should contain the methods and attributes necessary to make the super-type invisible. To understand the implementation consequences of the inheritance, it is also important to remember that Oracle8i is a strongly-typed system. A strongly-typed system requires that the type of an attribute is declared when the attribute is declared. Only values of the declared type may be stored in the attribute. For example, the Oracle8i collections are strongly-typed. Oracle8i does not allow the implementation of heterogeneous collections (collections of multiple types).

See Also: Working with User-Defined Types in Oracle8i Application Developer's Guide - Fundamentals

Simulating Inheritance

Inheritance can be implemented in Oracle8i using one of the following three techniques:

• Subtype Contains Super-type

• Super-type Contains or References All Subtypes

• Dual Subtype / Super-type Reference.

Subtype Contains Super-type

Figure 10-2 Object-Relational Schema -- Subtype Contains Super-type

The Subtype Contains Super-type technique hides the implementation of the abstractions/generalizations for a subtype. Each of the subtypes are exposed to other types in the object model. The super-types are not exposed to other types. To simulate inheritance, the super-type in the design object model is created as an object type. The subtype is also created as an object type. The super-type is defined as an embedded attribute in the subtype. All of the methods that can be executed for the subtype and it's super-type must be defined in the subtype.

The Subtype Contains Super-type technique is used when each subtype has specific relationships to other objects in the object model. For example, a super-type of Customer may have subtypes of Private Customer and Corporate Customer. Private Customers have relationships with the Personal Banking objects, while Corporate Customers have relationships with the Commercial Banking objects. In this environment, the Customer super-type is not visible to the rest of the object model.

In the Vehicle-Car/Truck example, the Vehicle (super-type) is embedded in the sub-types Car and Truck.

Super-type Contains All Subtypes

Figure 10-3 Object-Relational Schema -- Super-type Contains All Subtypes

The Super-type Contains All Subtypes technique hides the implementation of the subtypes and only exposes the super-type. To simulate inheritance, all of the subtypes for a given super-type in the design object model are created as object types. The super-type is created as an object type as well. The super-type declares an attribute for each subtype. The super-type also declares the constraints to enforce the one-and-only-one rules for the subtype attributes. All of the methods that can be executed for the subtype must defined in the super-type.

The Super-type Contains All Subtypes technique is used when objects have relationships with other objects that are predominately one-to-many in multiplicity. For example, a Customer can have many Accounts and a Bank can have many Accounts. The many relationships require a collection for each subtype if the Subtype Contains Super-type technique is used. If the Account is a super-type and Checking and Savings are subtypes, both Bank and Customer must implement a collection of Checking and Savings (4 collections). Adding a new account subtype requires that both Customer and Bank add the collection to support the new account subtype (2 collections per addition). Using the Super-type Contains All Subtypes technique means that the customer and bank have a collection of Account. Adding a subtype to Accounts means that only account changes.

In the case of the Vehicle-Car/Truck, the Vehicle is created with Car and Truck as embedded attributes of Vehicle.

Dual Subtype / Super-type Reference

Figure 10-4 Object-Relational Schema -- Dual Subtype / Super-type Reference

In cases where the super-type is involved in multiple object-relationships with many for a multiplicity and the subtypes have specific relationships in the object model, the implementation of Inheritance is a combination of the two inheritance techniques. The super-type is implemented as an object type. Each subtype is implemented as an object type. The super-type implements a referenced attribute for each subtype (zero referenced relationship). The super-type also implements an or-association for the group of subtype attributes. Each subtype implements a referenced attribute for the super-type (one referenced relationship). In this way, both the super-type and sub-type are visible to the rest of the object model.

In the case of the Vehicle-Car/Truck, the Vehicle is created as an type. The Car and Truck are created as types. The Vehicle type implements a reference to both Car and Truck, with the or-constraint on the Car and Truck attributes. The Car implements an attribute that references Vehicle. The Truck implements an attribute that references Vehicle.

Writing Methods: PL/SQL, C or Java?

When writing methods for object types, you have multiple implementation choices - PL/SQL, C/C++ and Java. Of these, PL/SQL and Java methods run within the address space of the server. C/C++ methods are dispatched as external procedures and run outside the address space of the server.

The best implementation choice varies from situation to situation. The following rules of thumb might be of help.

1. A callout involving C or C++ is, in general, the fastest if the nature of processing is substantially CPU-bound. However, callouts incur the cost of dispatch, and if the amount of processing in C/C++ is not large then the cost of dispatch does not amortize very well.

2. PL/SQL tends to offer the best price-performance for methods that are not computation-intensive. The other implementation options are typically favored over PL/SQL if you have a large body of code already implemented in another language that you want to use a part of the data cartridge

3. Java is a relatively open implementation choice. The interpreted nature of Java implies that for high performance applications, some sort of compilation of methods written in Java will be needed.

Invokers Rights -- Why, When, How

Until release 8.1.5, stored procedures and SQL methods could only execute with the privileges of the definer. Such definer-rights routines are bound to the schema in which they reside, and this remains the default. Under this condition, a routine executes with the rights of the definer of the function, not the user invoking it. However, this is a limitation if the function statically or dynamically issues SQL statements.

For example, if the function had a static cursor that performs a SELECT from USER_TABLES, the USER_TABLES it would retrieve would be that of the definer irrespective of which user was using the function. For the function to be used against data not owned by the definer, explicit GRANTs had to be issued from the owner to the definer, or the function needed to be defined in the same schema where the data resided. The former course creates security and administration problems; the latter forces the function to be redefined in each schema that needs to use it.

The invoker-rights mechanism, introduced in Orace8i release 8.1.5, permits a function to execute with the privileges of the invoker. This permits cartridges to live within a schema dedicated to the cartridge and to be used by other schemas without requiring privileges be granted to operate on objects in the schema where the cartridge resides.