|Oracle9i Application Developer's Guide - Object-Relational Features
Release 1 (9.0.1)
Part Number A88878-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 Oracle9i Objects and Extensibility technologies are to:
This book talks about the object-relational technologies. For details about extensibility, see Oracle9i Data Cartridge Developer's Guide.
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.
Oracle9i 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, Oracle relieves application developers from having to write a mapping layer between client-side objects and database objects. Object abstraction and encapsulation also make applications easier to understand and maintain.
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 Oracle data server, objects are managed with the same reliability, availability, and scalability as relational data.
Oracle supports single inheritance of user-defined SQL types. You can derive one or more subtypes from a single supertype. Subtypes can themselves be further specialized, enabling you to construct type hierarchies having any number of levels. Keywords are provided to let you control whether a given type can be subtyped or instantiated.
Oracle also provides support for client-side inheritance through 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 Oracle9i Java VM.
Methods can be implemented in PL/SQL, Java, C or C++. C & C++ support is provided through the external procedure functionality in Oracle, 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. Oracle 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.
In Oracle, 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 computationally 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.
Oracle9i supports two types of collections: variable-length arrays (varrays) and nested tables. Attributes of object types and columns of tables can be of collection types, and so can collections themselves. By using varrays and nested tables, applications can model one-to-many and many-to-many relationships natively in their database schema.
Yes. A varray can contain another varray or a nested table, and a nested table can contain another nested table or a varray. Similarly, you can have a collection of an object type that has an attribute of a collection type. Such multi-level collections can be nested to any number of levels.
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.
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, Oracle 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.
Oracle supports three kinds of large data objects: binary, character-based, and file-based. In addition to providing the ability to create LOBs, Oracle server provides several other improvements in managing binary data. These improvements can be summarized as follows:
For more information about LOBs, see Oracle9i Application Developer's Guide - Large Objects (LOBs).
Oracle 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.
For more information about user-defined operators, see CREATE OPERATOR in the Oracle9i SQL Reference, and the Oracle8 Data Cartridge Developer's Guide.
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.