Lesson 2	Overview of Oracle data structures
Objective	Describe the new Oracle data structures.

Overview of Oracle Data Structures

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.

Advantages of the Object-oriented Database over the traditional Relational Model:
New object-oriented databases provided some stiff competition for Oracle's relational model. Several features in object-oriented database allowed users to:
1. Define their own data types
2. Directly represent aggregate objects
3. Navigate between tables with pointers
4. Create non-first normal form tables with repeating groups
All of the object-oriented programming (OOP) features of Oracle shown in this course have shared two characteristics:
1. they are embedded objects, and
2. they are column objects.
An embedded object is one that is completely contained within another. For example, a nested table is contained within a table, so it is an embedded object. Although a nested table's data is stored apart from the main table, its data can only be accessed via the main table. A column object is one that is represented as a column in a table. For example, a (varray) varying array is represented as a column in a table, so it is a column object. To take advantage of OOP capabilities, a database must also support row objects, which are objects that are represented as rows instead of columns. The row objects are not embedded objects. Instead, they are referenced objects, accessible by means of references from other objects. This module discusses how row objects are created and referenced. Row objects can be extended for use within some of the objects discussed previously (such as object views). In addition to row objects, this module covers the application of objects to PL/SQL, by means of creating Object PL/SQL.

Data Warehouses and Different Models

Relational Model: The relational model is too granular and introduces granularity by removing duplication. The result is a database model nearly always highly effective for front-end application performance and OLTP databases. OLTP databases involve small amounts of data accessed frequently and concurrently by many users. On the other hand, data warehouses require throughput of huge amounts of data by a small user population. OLTP databases (the relational database model) need lightning-quick response to many people and small chunks of data. Data warehouses perform enormous amounts of I/O activity, over millions of records. It is acceptable for data warehouse reports to take hours to run.
Object Model: The object model is even more granular than the relational model, just in a different way, even if it does appear more realistic to the naked eye. Highly granular normalized relations (the relational model), or uniquely autonomous objects (the object model), can cause serious inefficiencies in a data warehouse. Data warehouses perform lots of big queries, with lots of data in many records in many tables. The fewer tables there are in a data warehouse, the better the data repository from a performance perspective. Query joins on large sets of records can become completely unmanageable and even totally useless.

Object-Oriented Database System

The 1989 Object-Oriented Database System Manifesto proposed thirteen mandatory features for an OODBMS, based on two criteria: it should be 1) an object-oriented system and 2) database management system.

Complex objects must be supported: It must be possible to build complex objects by applying constructors to basic objects. The minimal set of constructors are SET, TUPLE, and LIST (or ARRAY). The first two are important because they have gained widespread acceptance as object constructors in the relational model. The final one is important because it allows order to be modeled. Furthermore, the manifesto requires that object constructors must be orthogonal: any constructor should apply to any object. For example, we should be able to use not only
```
SET(TUPLE()) and LIST(TUPLE())
```
but also
```
TUPLE(SET()) and TUPLE(LIST()).		
```
Object identity must be supported: All objects must have a unique identity that is independent of its attribute values.
Encapsulation must be supported. In an OODBMS, proper encapsulation is achieved by ensuring that programmers have access only to the interface specification of methods, and the data and implementation of these methods are hidden in the objects. However, there may be cases where the enforcement

New Data Types and Structures in Oracle

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

User-defined data types, sometimes called abstract data types or ADT
Nested tables
Allowing repeating groups in table columns by allowing VARRAY data types in columns
Embedding object ID's inside tables and using them to navigate between tables
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.

Oracle Database locks Data Structures 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.

Overriding Default Locking

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:

LOCK TABLE, which explicitly locks entire tables.
SELECT with the FOR UPDATE clause (SELECT FOR UPDATE), which explicitly locks specific rows of a table.

LOCK TABLE Statement:
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.

Represent the one-to-many relationship directly in the Master Table

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 RDBMS implements OO 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.

Schema Objects

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