Overview of Oracle Data Structures in Oracle

One of the shortcomings of the Oracle7 database was that in following the traditional database model, the number of intrinsic data types and data structures was limited. For other object-oriented databases at the time, this was not the case. To remain competitive, Oracle has introduced some new object-oriented features that promise to improve the robustness and the performance of Oracle applications.

With Oracle we see several new and exciting data structures that promise faster performance. These data structure opportunities include:

1. User-defined data types, sometimes called abstract data types or ADT
2. Nested tables
3. Allowing repeating groups in table columns by allowing VARRAY data types in columns
4. Embedding object ID's inside tables and using them to navigate between tables
5. Creating aggregate objects that consist entirely of OID's to other tables

To master the use of these new data types and features, we must intimately understand how each method functions, and how each may help or impede performance.

Different users can write to the same data structures without harming each other's data or coordinating with each other, because Oracle Database locks data structures automatically. To maximize data availability, Oracle Database locks the minimum amount of data for the minimum amount of time. For more information about the Oracle Database locking mechanism, see Oracle Database Concepts. You rarely must write extra code to prevent problems with multiple users accessing data concurrently. However, if you do need this level of control, you can manually override the Oracle Database default locking mechanisms. For more information about manual data locks, see Oracle Database Concepts.

By default, Oracle Database locks data structures automatically, which lets different applications write to the same data structures without harming each other's data or coordinating with each other. If you must have exclusive access to data during a transaction, you can override default locking with these SQL statements:

The LOCK TABLE statement explicitly locks one or more tables in a specified lock mode so that you can share or deny access to them. The lock mode determines what other locks can be placed on the table. For example, many users can acquire row share locks on a table at the same time, but only one user at a time can acquire an exclusive lock. While one user has an exclusive lock on a table, no other users can insert, delete, or update rows in that table.
A table lock never prevents other users from querying a table, and a query never acquires a table lock. Only if two different transactions try to modify the same row does one transaction wait for the other to complete. The LOCK TABLE statement lets you specify how long to wait for another transaction to complete. Table locks are released when the transaction that acquired them is either committed or rolled back.

One of the features of Oracle is the introduction of objects into the relational database paradigm.
Unfortunately, many DBAs continue to use Oracle as a purely relational database, and are not aware of some of the new object-oriented data features, that can be used to dramatically improve the performance of their systems.
For example, we are going to be learning about the insertion of VARRAYS into Oracle tables. Of course, this is a violation of first normal form, but it can dramatically improve the performance of Oracle queries, because the subordinate tables are no longer required to represent one-to-many relationships.

Rather than building the one-to-many relationship, we can represent the one-to-many relationship directly in the master table by implementing these repeating groups. This avoids expensive table joins. In addition, we also have the exciting new concept of object IDs. Object IDs can act like pointers to relational database objects, and allow us to directly address (or de-reference), the contents of a row inside another table. The use of object IDs dramatically reduces the need for SQL joins, and we will be taking a look at some ways you can improve the performance of your Oracle databases by using these new object-oriented features.

Oracle Database is an RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism. These characteristics of OO databases is called an object-relational database management system (ORDBMS). Oracle Database has extended the relational model to an object-relational model, making it possible to store complex business models in a relational database. object relational database management system (ORDBMS): An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism.

One characteristic of an RDBMS is the independence of 1) physical data storage from 2) logical data structures. In an Oracle Database, a database schema is a collection of logical data structures, or schema objects. A database user owns a database schema, which has the same name as the user name. Schema objects are user-created structures that directly refer to the data in the database. The database supports many types of schema objects, the most important of which are tables and indexes. A schema object is one type of database object and several database objects, such as profiles and roles, do not reside in schemas.
The next lesson explores Abstract Data Typing (ADT).