7
Frequently Asked Questions About Using Oracle Objects

Here are some questions and answers that new users often have about Oracle's object-relational features:

• General Questions about Oracle Objects
• Object Types
• Object Methods
• Object References
• Collections
• Object Views
• Object Cache
• Large Objects (LOBs)
• User-Defined Operators

You can use this chapter as introductory information, or refer here if you still have questions after reading the rest of the book.

General Questions about Oracle Objects

Are the object-relational features a separate option?

Not anymore. As of Version 8.1, they are part of the base server product.

What are the design goals of Oracle9i Object-Relational & Extensibility technologies?

The design goals of Oracle9i Objects and Extensibility technologies are to:

• Provide users with the ability to model their business objects in the database by enhancing the type system to support user-defined types. These types are meant to closely model application objects and are treated as built-in types, such as number and character, by the database server.
• Provide an infrastructure to facilitate object-based access to data stored in an Oracle database and minimize the potential mismatch between the data model used in an application and the data model supported by a database.
• Provide built-in support for new data types needed in multimedia, financial and spatial applications.
• Provide a framework for database extensibility so that new multimedia and complex data types can be supported and managed natively in the database. This framework provides the infrastructure needed to allow extensions of the data server by third parties, using data cartridges.

This book talks about the object-relational technologies. For details about extensibility, see Oracle9i Data Cartridge Developer's Guide.

Object Types

What is structured data?

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".

Where are the user-defined types, user-defined functions, and abstract data types?

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.

What is an object type?

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:

• A name, which identifies the object type uniquely within a schema
• Attributes, which model the structure and state of the real-world entity
• Methods, which implement the behavior of the real-world entity

Why are object types useful?

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.

How is object data stored and managed in Oracle9i?

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.

Is inheritance supported in Oracle9i?

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.

Object Methods

What language can I use to write my object methods?

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 cause the server to fail or corrupt the database, thus ensuring the reliability and availability of the server in a mission critical environment.

How do I decide between using PL/SQL and Java for my object methods?

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.

When should I use external procedures?

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.

What are definer and invoker rights?

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 metadata maintained by the object implementor. Methods that access the metadata are executed using the definer rights so that the object implementor does not have to expose the proprietary metadata to the users.

Object References

What is an object reference?

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.

When should I use object references? How are they different from foreign keys?

Object references, like foreign keys, are useful in modeling complex relationships. Object references are more flexible than foreign keys for modeling complex relationships because:

• Object references are strongly typed and this provides better compile-time type checking
• One-to-many relationships can be modeled using a collection of object references
• Application can easily navigate and retrieve objects using object references without having to construct SQL statements
• REF navigation in SQL avoids the need to do complicated multitable joins
• Object references allow applications to retrieve objects connected by REFs in a single request to the server

Can I construct object references based on primary keys?

Yes, object references can be constructed based on foreign keys to reference objects in:

• Object views: When constructing objects from relational tables using an object view, the OIDs of the constructed objects are typically based on the primary keys on the underlying relational tables.
• Object tables with primary key-based OIDs: When defining an object table, Oracle provides the option of specifying the primary keys as the OIDs of the row objects instead of using the system generated OIDs.

What is a scoped REF and when should I use it?

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.

Can I manipulate objects using object references in PL/SQL and Java?

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.

Collections

What kinds of collections are supported by Oracle9i?

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.

Do Oracle Objects support collections within collections?

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 multilevel collections can be nested to any number of levels.

How do I decide between using varrays and nested tables for modeling collections?

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.

What is a collection locator?

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.

What is collection unnesting?

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.

Object Views

What are the differences between object views and object tables?

Like the similarity between relational views and tables, an object view has properties similar to an object table:

• It contains objects in rows. The columns of the view map to top-level attributes of the object type.
• Each object has an identifier associated with it. The identifier is specified by the view definer; in most cases, the primary key of the base table serves as the identifier.

Are object views updatable?

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.

Object Cache

Why do we need the object cache?

The object cache gives applications the following benefits:

• Transparent mapping of database objects to host language objects in memory.
• Transparent, efficient memory management for persistent objects. Applications do not have to worry about allocation of memory for accessing database objects.
• Transactional semantics for client-side objects. Modified persistent objects in the object cache can be propagated (flushed) to the database in a single round-trip between the client and the server.
• Navigational object access. The object cache allows for navigational style object access. Using OCI's object functions, objects can be fetched into the object cache by pinning object REFs. Navigational object access may be more suitable when operating on a graph of objects that are inter-connected through object REFs.
• 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 through REFs to the object being fetched, in a single round-trip between the client and the server.

Does the object cache support object locking?

The object cache supports both a pessimistic locking scheme and an optimistic locking scheme.

• In the pessimistic locking scheme, objects are locked up-front in the server prior to modifying the object in the cache. This ensures no other user can modify the object until the transaction owning the lock commits/rollbacks.
• In the optimistic locking scheme, an object is fetched and modified in the cache without acquiring a lock. The lock is acquired only when the object is flushed to the server. Optimistic locking allows for a higher degree of concurrent access than pessimistic locking. To use optimistic locking effectively, the object cache provides the ability for detecting if an object was changed by any other user since it was fetched into the cache. By turning on the "object change detection mode", object modifications will be made persistent if the nobody else has changed the object since it was fetched into the cache.

Large Objects (LOBs)

How can I manage large objects using Oracle?

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:

• Support for defining more than one LOB column in a table
• Random, piece-wise access to LOB data
• Support for transferring LOB data as a single stream
• Support for disabling logging and caching for LOB data
• Support for transparently moving LOBs from "in-line" row storage to "out-of-line" storage in another segment or even another tablespace

For more information about LOBs, see Oracle9i Application Developer's Guide - Large Objects (LOBs).

User-Defined Operators

What is a user-defined operator?

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.

Why are user-defined operators useful?

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.