Oracle Essentials Oracle Database 11g (4 page)

Why did relational database technology grow to become the de facto database technology? A look back at previous database technology may help to explain this phenomenon.

Database management systems were first defined in the 1960s to provide a common organizational framework for data formerly stored in independent files. In 1964, Charles Bachman of General Electric proposed a network model with data records linked together, forming intersecting sets of data, as shown on the left in
Figure 1-1.

This work formed the basis of the CODASYL Data Base Task Group. Meanwhile, the North American Aviation’s Space Division and IBM developed a second approach based on a hierarchical model in 1965. In this model, data is represented as tree structures in a hierarchy of records, as shown on the right in
Figure 1-1.
IBM’s product based on this model was brought to market in 1969 as the Information Management System (IMS). As recently as 1980, almost all database implementations used either the network or hierarchical approach. Although several competitors sold similar technologies around 1980, only IMS could still be found in many large organizations 20 years later.

Figure 1-1. Network model (left) and hierarchical model (right)
The Evolution of the Relational Database

|

3

Relational Basics

The relational database uses the concept of linked two-dimensional tables consisting of rows and columns, as shown in
Figure 1-2.
Unlike the hierarchical approach, no predetermined relationship exists between distinct tables. This means that data needed to link together the different areas of the network or hierarchical model need not be defined. Because relational users don’t need to understand the representation of data in storage to retrieve it (and many such users create ad hoc queries), ease of use helped popularize the relational model.

DEPTNO

DEPTNAME

LOCATION

10

Accounting

San Francisco

20

Research

San Francisco

30

Sales

Chicago

40

Operations

Dallas

EMPNO

EMPNAME

TITLE

DEPTNO

71712

Johnson

Clerk

10

83321

Smith

Mgr

20

85332

Stern

SC Mgr

30

88888

Carter

Mgr

10

Figure 1-2. Relational model with two tables

Relational programming is nonprocedural and operates on a set of rows at a time. In a master-detail relationship between tables, there can be one or many detail rows for each individual master row, yet the statements used to access, insert, or modify the data simply describe the set of results. In many early relational databases, data access required the use of procedural languages that worked one record at a time. Because of this set orientation, programs access more than one record in a relational database more easily. Relational databases can be used more productively to extract value from large groups of data.

The contents of the rows in
Figure 1-2
are sometimes referred to as
records
. A column within a row is referred to as a
field
. Tables are stored in a database
schema
, which is a logical organizational unit within the database. Other logical structures in the schema often include the following:

Views

Provide a single view of data derived from one or more tables or views. The view is an alternative interface to the data, which is stored in the underlying table(s) that make up the view.

4

|

Chapter 1: Introducing Oracle

Sequences

Provide unique numbers for column values.

Stored procedures

Contain logical modules that can be called from programs.

Synonyms

Provide alternative names for database objects.

Indexes

Provide faster access to table rows.

Database links

Provide links between distributed databases.

The relationships between columns in different tables are typically described through the use of
keys
, which are implemented through referential integrity constraints and their supporting indexes. For example, in
Figure 1-2,
you can establish a link between the DEPTNO column in the second table, which is called a
foreign key
, to the DEPTNO column in the first table, which is referred to as the
primary key
of that table.

Finally, even if youdefine many different indexes for a table, youdon’t have to understand them or manage the data they contain. Oracle includes a
query optimizer
(described in
Chapter 4)
that chooses whether to use indexes, and the best way to use those indexes, to access the data for any particular query.

The relational approach lent itself to the Structured Query Language (SQL). SQL

was initially defined over a period of years by IBM Research, but it was Oracle Corporation that first introduced it to the market in 1979. SQL was noteworthy at the time for being the only language needed for relational databases since you could use SQL:

• For queries (using a SELECT statement)

• As a Data Manipulation Language or DML (using INSERT, UPDATE, and DELETE statements)

• As a Data Definition Language or DDL (using CREATE or DROP statements when adding or deleting tables)

• To set privileges for users or groups (using GRANT or REVOKE statements) Today, SQL contains many extensions and follows ANSI/ISO standards that define its basic syntax.

How Oracle Grew

In 1983, Relational Software Incorporated was renamed Oracle Corporation to avoid confusion with a competitor named Relational Technologies Incorporated. At this time, the developers made a critical decision to create a portable version of Oracle
The Evolution of the Relational Database

|

5

written in C (version 3) that ran not only on Digital VAX/VMS systems, but also on Unix and other platforms. By 1985, Oracle claimed the ability to run on more than 30 platforms. Some of these platforms are historical curiosities today, but others remain in use. (In addition to VMS, early operating systems supported by Oracle included IBM MVS, HP/UX, IBM AIX, and Sun’s Solaris version of Unix.) Oracle was able to leverage and accelerate the growth of minicomputers and Unix servers in the 1980s. Today, Oracle also leverages this portability to operating systems such as Microsoft Windows and Linux.

In addition to multiple platform support, other core Oracle messages from the mid-1980s still ring true today, including complementary software development and decision support (business intelligence) tools, ANSI standard SQL across platforms, and connectivity over standard networks. Since the mid-1980s, the database deployment model has evolved from dedicated database application servers to client/server to Internet computing implemented using browser-based clients accessing database applications.

Oracle introduced many innovative technical features to the database as computing and deployment models changed (from offering the first distributed database to supporting the first Java Virtual Machine in the core database engine to enabling grid computing). Oracle offered support for emerging standards such as XML, important in deploying a Service-Oriented Architecture (SOA).
Table 1-1
presents a short list of Oracle’s major product introductions.

Table 1-1. History of Oracle introductions

Year

Feature

1977

Software Development Laboratories founded by Larry Ellison, Bob Miner, Ed Oates 1979

Oracle version 2: first commercially available relational database to use SQL

1983

Oracle version 3: single code base for Oracle across multiple platforms 1984

Oracle version 4: with portable toolset, read consistency

1986

Oracle version 5 generally available: client/server Oracle relational database 1987

CASE and 4GL toolset

1988

Oracle Financial Applications built on relational database

1989

Oracle6 generally available: row-level locking and hot backups 1991

Oracle Parallel Server on massively parallel platforms

1993

Oracle7: with cost-based optimizer

1994

Oracle version 7.1 generally available: parallel operations including query, load, and create index 1996

Universal database with extended SQL via cartridges, thin client, and application server 1997

Oracle8 generally available: object-relational and Very Large Database (VLDB) features 1999

Oracle8
i
generally available: Java Virtual Machine (JVM) in the database 2000

Oracle9
i
Application Server generally available: Oracle tools integrated in middle tier 2001

Oracle9
i
Database Server generally available: Real Application Clusters, OLAP, and data mining in the database
6

|

Chapter 1: Introducing Oracle

Table 1-1. History of Oracle introductions (continued)

Year

Feature

2003

Oracle Database 10
g
and Oracle Application Server 10
g
: “grid” computing enabled; Oracle Database 10
g
automates key management tasks

2005

Oracle completes PeopleSoft acquisition and announces Siebel acquisition, thus growing ERP and CRM applications and business intelligence offerings

2007

Oracle Database 11
g
: extension of self-managing capabilities and end-to-end database change management; Hyperion acquisition adds database-independent OLAP and Financial Performance Management applications
The Oracle Database Family

Oracle Database 11
g
is the most recent version of the Oracle Relational Database Management System (RDBMS) family of products that share common source code.

The family of database products includes:

Oracle Enterprise Edition

Flagship database product and main topic of this book, aimed at large-scale implementations that require Oracle’s full suite of database features and options.

For advanced security, only the Enterprise Edition features Virtual Private Database (VPD) support, Fine-Grained Auditing, and options including the Database Vault, Advanced Security, and Label Security. Data warehousing features only in Enterprise Edition include compression of repeating stored data values, cross-platform transportable tablespaces, Information Lifecycle Management (ILM), materialized views query rewrite, and the Partitioning, OLAP, and Data Mining Options. High-availability features unique to the Enterprise Edition include Data Guard and Flashback database, Flashback table, and Flashback transaction query. Added to Oracle Database 11
g
are an Advanced Compression Option for all workloads, including transaction processing, Large Object (LOB) storage, and backups; a database testing option called the Real Application Testing Option that includes Database Replay and SQL Performance Analyzer; and a Total Recall Option used to enable a Flashback Data Archive that retains data for historic queries (where a SQL construct specifies an “AS OF” date in the past).

Oracle Standard Edition

Oracle’s database intended for small and medium-sized implementations. This database can be deployed onto server configurations containing up to 4 CPUs on a single system or on a cluster using Real Application Clusters (RAC).

Oracle Standard Edition One

Designed for small implementations. This database can support up to 2 CPUs and does not support RAC. The feature list is otherwise similar to Oracle Standard Edition.

The Oracle Database Family

|

7

Oracle Personal Edition

Database used by single developers to develop code for implementation on Oracle multiuser databases. It requires a license, unlike Express Edition, but gives you the full Enterprise Edition set of functionality.

Oracle Express Edition

Entry-level database from Oracle available at no charge for Windows and Linux.

This database is limited to 1 GB of memory and 4 GB of disk. It provides a subset of the functionality in Standard Edition One, lacking features such as a Java Virtual Machine, server-managed backup and recovery, and Automatic Storage Management. Although this database is not manageable by Oracle Enterprise Manager, you can deploy it for and manage multiple users through the Oracle Application Express (formerly HTML-DB) administration interface.

Oracle generally releases new versions of the flagship database about every three to four years. New releases typically follow themes and introduce a significant number of new features. In recent releases, these themes are indicated in the product version naming. In 1998, Oracle announced Oracle8
i
, with the “
i
” added to denote new functionality supporting Internet deployment. Oracle9
i
continued using this theme.

In 2003, Oracle released Oracle Database 10
g
, with the “
g
” denoting Oracle’s focus on emerging grid computing deployment models. Oracle has continued that theme in the current database version highlighted in this book. In between major versions, Oracle issues point releases that also add features but are more typically focused on improvements to earlier capabilities.

The terms “Oracle,” “Oracle8,” “Oracle8
i
,” “Oracle9
i,
” “Oracle Database 10
g,
” and

“Oracle Database 11
g
” might appear to be used somewhat interchangeably in this book because Oracle Database 11
g
includes all the features of previous versions.