|Oracle8i Application Developer's Guide - Object-Relational Features
Release 2 (8.1.6)
Part Number A76976-01
Here are some questions and answers that new users often have about Oracle's object-relational features:
You can use this chapter as introductory information, or refer here if you still have questions after reading the rest of the book.
Not anymore. As of Version 8.1, they are part of the base server product.
The design goals of Oracle8i Objects and Extensibility technologies are to:
This book talks about the object-relational technologies. For details about extensibility, see Oracle8 Data Cartridge Developer's Guide.
Building on the standard features of the Oracle data server, the Oracle8i Objects is an additional set of features that enable the creation and manipulation of user-defined object types:
Oracle8i provides the capability to define new types in the database via a new meta-model, simply called the Oracle8i Type System. This type system extends the Oracle relational types to allow users to define types and methods that model objects. Oracle8i stores the meta-data for user-defined types in a schema that is available to SQL, PL/SQL, Java and other published interfaces. A user can create a new object type by using any built-in database types, and/or any known object types, object references and collection types.
In addition to natively storing object data in the server, Oracle8i allows the creation of an object abstraction over existing relational data via the object view mechanism. Objects belonging to an object view are accessed in the same manner as row objects via SQL or other call interfaces. To support such data access, Oracle8i server materializes objects of user-defined types from data stored in relational schemas and tables. This view mechanism supports the development of new object-oriented applications without having to modify existing database schemas.
To support the new features of the Objects Option, extensions have been made to SQL -- including new DDL -- to create, alter, or drop object types; to store object types in tables; and to create, alter, or drop object views. There are DML and query extensions to support object types, references, and collections.
PL/SQL is Oracle's database programming language that is tightly integrated with SQL. With the addition of user-defined types and other SQL types introduced in Oracle8i, PL/SQL has been enhanced to operate on user-defined types seamlessly. Thus, application developers can use PL/SQL to implement logic and operations on user-defined types that execute in the database server.
Oracle8i's Java VM is tightly integrated with the RDBMS and supports access to Oracle8i Objects via object extensions to JDBC (dynamic SQL) and SQLJ (static SQL). Thus, application developers can use the Java to implement logic and operations on user-defined types that execute in the database server.
Database functions, procedures, or member methods of an object type can be implemented in PL/SQL, Java, or C as external procedures. External procedures are best suited for tasks that are more quickly or easily done in a low-level language such as C, which is more efficient at machine-precision calculation. External procedures are always run in a safe mode outside the address space of the RDBMS server.
The Object Type Translator (OTT) available with the Objects Option provides client-side mappings to object type schema by generating header files containing Java classes and C structs and indicators, using schema information from the Oracle data dictionary. These generated header files can be used in host-language applications for transparent access to database objects.
Oracle8i provides an object cache for efficient access to persistent objects stored in the database. Copies of objects can be brought into the object cache. Once the data has been cached in the client, the application can traverse through these at memory speed. Any changes made to objects in the cache can be committed to the database by using the object extensions to Oracle Call Interface programmatic interfaces.
Oracle8i also provides support for efficient complex object retrieval. That is, a single request to fetch an object from the server can be used to retrieve other objects, which are connected to the object being fetched via object references, in a single round-trip between the client and the server. Such functionality facilitates the fetching and manipulation of a set of related objects as a single unit.
Oracle8i Oracle Call Interface provides a comprehensive application programming interface for application and tool developers seeking to use the object capabilities of Oracle8. Oracle Call Interface provides a run-time environment with functions to connect to an Oracle8 server and control transactions that access objects in the server. It allows application developers to access and manipulate objects and their attributes in the client-side object cache either "navigationally" by traversing a graph of inter-connected objects or "associatively" by specifying the nature of the data via declarative SQL DML. Oracle Call Interface also provides a number of functions for accessing meta-data information at run-time about object types defined in the server. Such a set of functions facilitates dynamic access to the object meta-data and the actual object data stored in the database.
Oracle8i Pro*C precompiler provides an embedded SQL application programming interface and offers a higher level of abstraction than Oracle Call Interface. Like Oracle Call Interface, the Pro*C precompiler allows application developers to use the Oracle8i client-side object cache and the Object Type Translator Utility. Pro*C supports the use of "C" bind variables for Oracle8i object types. Furthermore, Pro*C provides new simplified syntax to allocate and free objects of SQL types and access them by either SQL DML, or via the "navigational" interface. Thus, it provides application developers many benefits, including compile-time type checking of (client-side) bind variables against the schema in the server, automatic mapping of object data in an Oracle8i server to program bind variables in the client, and simple ways to manage and manipulate database objects in the client process.
Oracle8i Objects continues to support standard relational database functionality such as queries (SELECT...FROM...WHERE), fast commits, backup and recovery, scalable connectivity, row-level locking, read consistency, partitioned tables, parallel queries, parallel server, export/import, loader etc.
Oracle8i provides the foundation for modeling complex objects. Here is a list of object-relational features, with the new 8i features in italics:
Scalars, LOBs, Objects, References, Collections
PL/SQL methods, External Procedures, Java Methods
Triggers, Constraints, Object Views, User-Defined Operators
Sorted, Hash, Bitmap, Index-Organized Tables, Extensible Indexing
Object Query Optimization, Extensible Optimizer
Object Support in Export/Import, Loader, Parallel Query, Partitioning
Object Support in OCI, C++ (ODD), Pro*C, JDBC, OO4O
The SQL 92 standard defines the 19 atomic datatypes that are used in most database programming. We refer to these kinds of data as "simple structured".
Oracle Objects introduces the ideas of REFs and collections. We refer to these kinds of data as "complex structured".
LOBs provide another way to store information. We refer to them as "unstructured".
The Oracle equivalent of a user-defined type or an abstract data type is an object type.
The Oracle equivalent of a user-defined function is an object type method.
We use these terms because their semantics are different from the common industry usage. For example, an Oracle object can be null, while an object of an abstract data type cannot.
Oracle8i supports a form of user-defined data types called object types. Object types are abstractions of real-world entities. An object type is a schema object with the following components:
An object type is similar to the class mechanism supported by C++ and Java. Object reusability provides faster and more efficient database application development. Object support makes it easier to model complex, real-world business entities and logic. By supporting object types natively in the database, Oracle8i eliminates the impedance mismatch between object-oriented programming languages and the database, and relieves application developers from the need to write a mapping layer between client-side objects and database objects. Object abstraction and encapsulation also make it easier to understand and maintain applications, an important consideration in enterprise database application development.
Objects are managed natively by the data server. Object types can be used as the type of a column (column objects) or as type of each row in an object table (row objects). When used as column objects, object types serve as classical relational domains. Each row object has a unique identity, called an object identifier (OID).
Objects are first-class citizens and are fully integrated with the database components. They can be indexed and partitioned. For example, queries involving objects can be parallelized and are optimized by the cost-based optimizer using statistics.
By building on the proven foundation of the Oracle8i data server, objects are managed with the same reliability, availability, and scalability as relational data.
Not directly. Because each language has its own semantics for inheritance -- for example, single inheritance versus multiple inheritance -- Oracle uses techniques that mimic the inheritance behavior of each language.
Oracle8i provides support for client-side inheritance via its C++ and Java mappings. For C++, use the Object Modelling Option of Oracle Designer to produce DDL and C++ code based on diagrams in the Universal Modelling Language (UML). For Java, use the "custom datum" feature of the Oracle JDBC driver.
Server-side method inheritance is provided in Java by the Oracle8i Java VM. Oracle8i does not currently support inheritance in SQL, the ability to store and query instances of a type and its subtypes.
Methods can be implemented in PL/SQL, Java, C or C++. C & C++ support is via the external procedure functionality in Oracle8i, whereas PL/SQL and Java methods run within the address space of the server. De-coupling of the specification of a method in SQL from its implementation provides a uniform way to invoke methods on object types, even though these object types can be implemented in various programming languages. Oracle8i provides a safe and secure environment for invoking PL/SQL methods, Java methods, and external C procedures from the server. Programming errors in user methods will not crash the server or corrupt the database, thus ensuring the reliability and availability of the server in a mission critical environment.
With Oracle8i, PL/SQL and Java can be used interchangeably as a server programming language. PL/SQL is a seamless extension of SQL and is the language of choice for doing SQL intensive operations in the database. Java is the emerging language of choice for writing portable applications that run in multiple tiers, including the database server.
External procedures are typically used for compute intensive operations that are best written in a low-level language such as C. External procedures are also useful for invoking routines in some existing libraries that cannot be easily rewritten in Java or PL/SQL to run in the data server.
The IPC (inter-process communication) overhead of invoking an external procedure is an order of magnitude higher than that of invoking PL/SQL or Java procedure. However, the overhead of invoking an external procedure become insignificant if the computation done in the external procedure is complex and is in the order of tens of thousands of instructions.
The distinction between definer and invoker rights applies to more than just objects. You may find invoker rights especially useful for object-oriented programs because they typically contain reusable modules.
An object method can be executed with the privileges of its owner (definer rights) or with the privileges of the current user (invoker rights), based on the method definition. Invoker rights are useful for writing reusable objects because users of these objects do not have to grant access privileges to their tables to the implementor of the objects. Definer rights are useful when the as part of the object implementation, the object methods need to access some meta-data maintained by the object implementor. Methods that access the meta-data are executed using the definer rights so that the object implementor does not have to expose the proprietary meta-data to the users.
An object reference (REF) uniquely identify a row object stored in an object table or an object constructed from an object view. Typically, a REF value is comprised of the object's unique identifier, the unique identifier associated with the object table, and the ROWID of the row in the object table in which the object is stored. The optional ROWID is used as a hint to provide fast access to the object.
Object references, like foreign keys, are useful in modeling complex relationships. Object references are more flexible than foreign keys for modeling complex relationships because:
Yes, object references can be constructed based on foreign keys to reference objects in:
In general, a column may contain references to objects of a particular declared type regardless of the object table(s) in which the objects are stored. However, a REF type column may be scoped (constrained) to only contain references to objects from a specified object table. One should use scoped REFs whenever possible because scoped REFs are smaller in size than regular REFs on disk because the system does not have to store the table identifier with the scoped REFs. Also, queries containing navigation of scoped REFs can be optimized into joins when appropriate.
Yes, both PL/SQL and Java support object references. In PL/SQL, an object can be retrieved and updated using the UTL_REF package given its object references. In Java, object references are mapped to reference classes with get and set methods to retrieve and update the objects.
Oracle8i supports two types of collections: varying arrays (varrays) and nested tables. Attributes of object types and columns of tables can be of collection types. By using varying arrays and nested tables, applications can model one-to-many and many-to-many relationships natively in their database schema.
Varrays are useful when you need to maintain ordering of your collection elements. Varrays are very efficient when you always manipulate the entire collection as a unit, and that you don't require querying on individual elements of the collections. Varrays are stored inline with the containing row if it is small and automatically stored as a LOBs by the system when its size is beyond a certain threshold.
Nested tables are useful when there is no ordering among the collection elements and that efficient querying on individual elements are important. Elements of a collection type column are stored in a separate table, similar to parent-child tables in a relational schema.
A collection cannot contain another collection attribute. However, nested collections can be modeled using a reference to an object which has a collection attribute. Therefore applications can model nested collections with REF indirection.
Collection locators allow applications to retrieve large collections without materializing the collections in memory. This allows for efficient transfer of large collections across interfaces. A collection will be transparently materialized when the application first accesses its elements. Also, applications can query and retrieve subsets of the collection using its locator.
The specification of retrieval of collection locators is done in CREATE and ALTER TABLE DDL. Since access to a collection is encapsulated, applications will use the same interface to retrieve a nested table specified to be returned as a locator as one specified to be returned as a value.
Collection unnesting allows applications to efficiently query over a set of collections in some specified rows, similar to query on the child rows in a relational schema for some specified parent rows. Collection unnesting allows applications the flexibility to view one-to-many relationships in the collection form or in the flat parent-child form.
Like the similarity between relational views and tables, an object view has properties similar to an object table:
It is easy to update an object view where every attribute maps back to a real column in a table. For views that derive some attributes by more complex techniques, such as CAST-MULTISET, INSTEAD-OF triggers can be used to do the updates. When such a view is updated (or inserted into or deleted from), rather than attempting to implicitly modify any base tables, the system simply invokes the INSTEAD-OF trigger specified for the view. You can encapsulate whatever update semantics you want in the trigger body.
The object cache gives applications the following benefits:
The object cache supports both a pessimistic locking scheme and an optimistic locking scheme.
Support for multimedia data types like text, images, audio, and video requires robust support for binary and character data. The data in these domains tends to be large and requires direct access to different pieces of the binary data. To address this need, Oracle8i provides significantly improved support for large-scale binary and character data. It introduces the Large Object type (LOB) which can be used to store large, domain-specific data from various domains, including images, audio files, text and spatial data.
Oracle8i supports three kinds of large data objects: binary, character-based, and file-based. In addition to providing the ability to create LOBs, Oracle8i server provides several other improvements in managing binary data. These improvements can be summarized as follows:
For more information about LOBs, see Oracle8i Application Developer's Guide - Large Objects (LOBs).
Oracle8i allows developers of object-oriented applications to extend the list of built-in relational operators (for example, +, -, /, *, LIKE) with domain specific operators (for example, Contains, Within_Distance, Similar) called user-defined operators. A user-defined operator can be used anywhere built-in operators can be used, for example, in the select list or the where clause. Similar to built-in operators, a user-defined operator may support arguments of different types, and that it may be evaluated using an index.
Similar to built-in operators, user-defined operators allow efficient content-based querying and sorting on object data. For example, to find a resume containing certain qualifications, one may specify the Contains operator as part of the SQL where clause. The optimizer may choose to use a Text index on the resume column to perform the query efficiently, similar to using a B-tree index to evaluate a relational operator.