1 Introduction to Oracle Database

Client/Server Architecture

An Oracle database system can easily take advantage of distributed processing by using its client/server architecture. In this architecture, the database system has two parts: a front-end or a client, and a back-end or a server.

Multitier Architecture: Application Servers

A traditional multitier architecture has the following components:

• A client or initiator process that starts an operation

• One or more application servers that perform parts of the operation. An application server contains a large part of the application logic, provides access to the data for the client, and performs some query processing, thus removing some of the load from the database server. The application server can serve as an interface between clients and multiple database servers and can provide an additional level of security.

• An end server or database server that stores most of the data used in the operation

This architecture enables use of an application server to do the following:

• Validate the credentials of a client, such as a Web browser

• Connect to an Oracle Database server

• Perform the requested operation on behalf of the client

If proxy authentication is being used, then the identity of the client is maintained throughout all tiers of the connection.

Multitier Architecture: Service-Oriented Architecture

Service-oriented architecture (SOA) is a multitier architecture in which application functionality is encapsulated in services. SOA services are usually implemented as Web services. Web services can be accessed with the HTTP protocol and are based on a set of XML-based open standards, such as WSDL and SOAP.

Beginning with Oracle Database 11g, Oracle Database can act as a Web service provider in a traditional multitier or SOA environment.

Datafiles

Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

Datafiles have the characteristics:

• A datafile can be associated with only one database.

• Datafiles can be defined to extend automatically when they are full.

• One or more datafiles form a logical unit of database storage called a tablespace.

Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.

Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the background process database writer process (DBWn).

Datafiles that are stored in temporary tablespaces are called tempfiles. Tempfiles are subject to some restrictions, as described in "Temporary Datafiles".

Control Files

Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database, including the following information:

• Database name

• Names and locations of datafiles and redo log files

• Timestamp of database creation

Oracle Database can multiplex the control file, that is, simultaneously maintain a number of identical control file copies, to protect against a failure involving the control file.

Every time an instance of an Oracle database is started, its control file identifies the datafiles, tempfiles, and redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle Database to reflect the change. A control file is also used in database recovery.

Online Redo Log Files

Every Oracle Database has a set of two or more online redo log files. These online redo log files, together with archived copies of redo log files, are collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records), which record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

To protect against a failure involving the redo log itself, Oracle Database lets you create a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.

Archived Redo Log Files

When online redo log files are written to disk, they become archived redo log files. Oracle recommends that you enable automatic archiving of the redo log. Oracle Database automatically archives redo log files when the database is in ARCHIVELOG mode.

Parameter Files

Parameter files contain a list of configuration parameters for that instance and database. Both parameter files (pfiles) and server parameter files (spfiles) let you store and manage your initialization parameters persistently in a server-side disk file. A server parameter file has these additional advantages:

• The file is concurrently updated when some parameter values are changed in the active instance.

• The file is centrally located for access by all instance in a Real Application Services database.

Oracle recommends that you create a server parameter file as a dynamic means of maintaining initialization parameters.

Alert and Trace Log Files

Each server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Support Services. Trace file information is also used to tune applications and instances. The alert file, or alert log, is a special trace file. The alert log of a database is a chronological log of messages and errors.

The following features provide automation and assistance in the collection and interpretation of trace and alert file information:

• The Automatic Diagnostic Repository (ADR) is a system-managed repository for storing and organizing trace files and other error diagnostic data. ADR provides a comprehensive view of all the critical errors encountered by the database and maintains all relevant data needed for problem diagnosis and eventual resolution. When the same type of incident occurs too frequently, ADR performs flood control to avoid excessive dumping of diagnostic information.

• The Incident Packaging Service (IPS) extracts diagnostic and test case data associated with critical errors from the ADR and packages the data for transport to Oracle.

Backup Files

To restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file.

User-managed backup and recovery requires you to actually restore backup files before you can perform a trial recovery of the backups.

Server-managed backup and recovery manages the backup process, such as scheduling of backups, as well as the recovery process, such as applying the correct backup file when recovery is needed.

Oracle Database Data Blocks

At the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to four other block sizes. A database uses and allocates free database space in Oracle Database data blocks.

Extents

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.

Segments

Above extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a table, index, rollback segment, or for temporary use by a session, transaction, or SQL parser. In relation to physical database structures, all extents belonging to a segment exist in the same tablespace, but they may be in different data files.

When the extents of a segment are full, Oracle Database dynamically allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

Tablespaces

A database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.

Each database is logically divided into two or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Every Oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. Oracle Database creates them automatically when the database is created. The system default is to create a smallfile tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespaces are created as smallfile tablespaces.

Oracle Database also lets you create bigfile tablespaces, which are made up of single large file rather than numerous smaller ones. Bigfile tablespaces let Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. As a result, Oracle Database can scale up to 8 exabytes in size. With Oracle-Managed Files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafiles.

Tables

Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. Each table has columns and rows. A table that has employee information, for example, can have a column called employee_number, and each row in that column is an employee number.

Indexes

Indexes are optional structures associated with tables. You can create indexes to increase the performance of data retrieval. Just as the index in this manual helps you quickly locate specific information, an Oracle database index provides an access path to table data.

When processing a request, Oracle Database can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications frequently query a table for a range of rows (for example, all employees with a salary greater than 1000) or a specific row (for example, the employee with the highest salary).

You create an index on one or more columns of a table. Thereafter, Oracle Database automatically uses and maintains the index. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes.

Views

Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not contain actual data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.

You can query, update, insert into, and delete views as you can with tables, with some restrictions. If the view is updatable, then all operations performed on the view actually affect the base tables of the view.

Views can provide table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

Clusters

Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

Like indexes, clusters do not affect application design. Whether a table is part of a cluster is transparent to users and to applications. SQL statements access data stored in a clustered table in the same way that they access data stored in a nonclustered table.

Synonyms

A synonym is an alias for any table, view, materialized view, sequence, operator, procedure, function, package, Java class schema object, user-defined object type, or another synonym. A synonym is simply an alias, so it requires no storage other than its definition in the data dictionary.

Oracle Database Background Processes

An Oracle database uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database system. A process is a mechanism in an operating system that can run a series of steps. Some operating systems use the terms job or task. Oracle Database server uses three types of processes: Oracle processes—which include server processes and background processes—and user processes. On almost all systems, the Oracle processes and the user processes are on separate computers.

• Oracle Database creates a set of background processes for each instance. The background processes consolidate functions that would otherwise be handled by multiple Oracle Database programs running for each user process. They asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.

  See Also:

  "Oracle Database Background Processes" for more information on some of the most common background processes

• User processes—sometimes called client processes—are created and maintained to run the software code of an application program (such as an OCI or OCCI program) or an Oracle tool (such as Oracle Enterprise Manager). Most environments have separate machines (laptops, desktops, and so forth) for the client processes. User processes also manage communication with the server process through the program interface, which is described in a later section.

• Oracle Database creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle Database to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA.

  Oracle Database can be configured to vary the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a single user process. A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.

Instance Memory Structures

Oracle Database creates and uses memory structures for various purposes. For example, memory stores program code being run, data shared among users, and private data areas for each connected user. Two basic memory structures are associated with an Oracle Database:

• The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

• The Program Global Areas (PGA) are memory regions that contain data and control information for a server or background process. A PGA is nonshared memory created by Oracle Database when a server or background process is started. Access to the PGA is exclusive to the process. Each server process and background process has its own PGA.

Network Connections

Oracle Net Services is the interface between Oracle Database and the network communication protocols that facilitate distributed processing and distributed databases. Communication protocols define the way that data is transmitted and received on a network. Oracle Net Services supports communications on all major network protocols, including TCP/IP, HTTP, FTP, and WebDAV.

Using Oracle Net Services, application developers do not need to be concerned with supporting network communications in a database application. If a new protocol is used, then the database administrator makes some minor changes, and the application requires no modifications and continues to function.

Oracle Net, a component of Oracle Net Services, establishes and maintains a network session from a client application to an Oracle Database server. Once a network session is established, Oracle Net acts as the data courier for both the client application and the database server, exchanging messages between them. Oracle Net can perform these jobs because it is located on each computer in the network.

Starting Up the Database

The three steps to starting an Oracle database and making it available for system-wide use are:

1. Start an instance.

2. Mount the database.

3. Open the database.

A database administrator can perform these steps using Oracle Enterprise Manager, the SQL*Plus STARTUP statement, the srvctl command-line tool, or the Express Edition START command. When Oracle Database starts an instance, it reads the server parameter file (spfile) or initialization parameter file (pfile) to determine the values of initialization parameters. Then, it allocates an SGA and creates background processes.

How Oracle Database Works

The following example describes Oracle Database operations at the most basic level. This illustrates an Oracle Database configuration where the user and associated server process are on separate computers, connected through a network.

1. An instance has started on the computer running Oracle Database, often called the host or database server.

2. A computer running an application (a local computer or client workstation) runs an application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.

3. The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the user process.

4. The user runs a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.

5. The server process receives the statement and checks the shared pool (an SGA component) for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the user's access privileges to the requested data, and the existing shared SQL area is used to process the statement. If not, then a new shared SQL area is allocated for the statement, so it can be parsed and processed.

6. The server process retrieves any necessary data values, either from the actual datafile (table) or those stored in the SGA.

7. The server process modifies data in the system global area. The database writer process (DBWn) writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the log writer process (LGWR) immediately records the transaction in the redo log file.

8. If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.

9. Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.

Database Replay

Database Replay enables realistic testing of system changes by essentially recreating the production workload environment on a test system. It does this by capturing a workload on the production system and then replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results, new contention points, and performance regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergences.

With Database Replay, businesses can rapidly test changes and adopt new technologies with a high degree of confidence in the overall success of the effort and at significantly lower risk.

Database Replay can be used to assess the impact of the following types of system changes:

• Database upgrades, patches, parameter, and schema changes

• Configuration changes, such as conversion from a single instance to Oracle Real Application Clusters and Automatic Storage Management

• Storage, network, and interconnect changes

• Operating system patches, upgrades, and parameter changes and hardware migrations

SQL Performance Analyzer

Changes that affect SQL execution plans can severely impact system performance and availability. As a result, DBAs spend considerable time in identifying and fixing SQL statements that have regressed due to a change.

SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each statement. A report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, appropriate execution plan details, along with recommendations to tune them, is also provided. As a result, DBAs can remedy any negative outcome before their end users are affected and can confirm, with significant time and cost savings, that the system change to the production environment will, in fact, result in net improvement.

You can use the SQL Performance Analyzer to analyze the SQL performance impact of any type of system change. Examples of common system changes include:

• Database upgrades

• Configuration changes to the operating system, hardware, or database

• Database initialization parameter changes

• Schema changes, such as adding new indexes or materialized views

• Gathering optimizer statistics

• SQL tuning actions, such as creating SQL profiles

Concurrency

A primary feature of a multiuser database management system is concurrency, which is the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity.

One way to manage data concurrency is to make each user wait for a turn. The goal of a database management system is to reduce that wait so it is either nonexistent or not noticeable to users. Data manipulation language operations (inserts, updates, and deletes) should proceed with as little interference as possible, and destructive interactions between concurrent transactions must be prevented. A destructive interaction is one that incorrectly updates data or incorrectly alters underlying data structures. Neither performance nor data integrity can be sacrificed.

Oracle Database resolves these issues by using various types of locks and a multiversion consistency model. These features are based on the concept of a transaction.

The transaction is key to the Oracle Database strategy for providing read consistency. This unit of committed (or uncommitted) SQL statements:

• Dictates the start point for read-consistent views generated on behalf of readers

• Controls when modified data can be seen by other transactions of the database for reading or updating

It is the application designer's responsibility to ensure that transactions fully exploit these concurrency and consistency features.

Read Consistency

Read consistency, as provided by Oracle Database, achieves the following goals:

• Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution (statement-level read consistency)

• Ensures that readers of database data do not wait for writers or other readers of the same data

• Ensures that writers of database data do not wait for readers of the same data

• Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions

In the Oracle Database implementation of read consistency, it is as if each user operates a private copy of the database. This is sometimes called a multiversion consistency model.

Caching Mechanisms

Oracle Database optimizes database performance by caching in memory user data, log data, dictionary data, and other types of data.

Oracle Database also caches query results, so that if a query is repeated, the database can return results from the cache instead of reprocessing the query and reading data from storage. The cached results are stored in a dedicated portion of the shared pool. Query retrieval from the query result cache is faster than rerunning the query. The query result cache enables explicit caching of results in database memory. Frequently executed queries especially see performance improvements when using the query result cache.

Locking Mechanisms

Oracle Database also uses locks to control concurrent access to data. When updating information, the data server holds that information with a lock until the update is submitted or committed. Until that happens, no one else can make changes to the locked information. This ensures the data integrity of the system.

Oracle Database provides unique nonescalating row-level locking. Unlike other data servers that escalate locks to cover entire groups of rows or even the entire table, Oracle Database always locks only the row of information being updated. Because the database includes the locking information with the actual rows themselves, it can lock an unlimited number of rows so users can work concurrently without unnecessary delays.

Self-Managing Database

Oracle Database provides a high degree of self-management by automating routine DBA tasks and reducing complexity of space, memory, and resource administration. Oracle Database self-managing features include the following: automatic undo management, automatic server memory management, Oracle-managed files, free space management, and Recovery Manager (RMAN).

Automatic Maintenance Tasks

Oracle Database automatically schedules periodic maintenance tasks such as statistics collection and space recovery. These tasks run in a set of Oracle Scheduler windows known as maintenance windows. You can control the start time and duration of these maintenance windows, and limit the amount of CPU and I/O resources that they consume.

Oracle Enterprise Manager

Oracle Enterprise Manager is a system management tool that provides central management of your database environment. Combining a graphical console, Oracle Management Servers, Oracle Intelligent Agents, common services, and administrative tools, Oracle Enterprise Manager provides a comprehensive systems management platform for managing Oracle products.

From the client interface, the Oracle Enterprise Manager Console, you can perform the following tasks:

• Administer the entire Oracle environment, including databases, Oracle Application Server servers, applications, and services

• Diagnose, modify, and tune multiple databases

• Schedule tasks on multiple systems at varying time intervals

• Monitor database conditions throughout the network

• Administer multiple network nodes and services from many locations

• Share tasks with other administrators

• Group related targets together to facilitate administration tasks

• Launch integrated Oracle and third-party tools

• Customize the display of an Oracle Enterprise Manager administrator

SQL Developer and SQL*Plus

Oracle SQL Developer is a graphical development tool that provides a convenient way to perform these tasks:

• Browse, create, edit, and delete (drop) database objects

• Edit and debug PL/SQL code

• Run SQL statements and scripts

• Manipulate and export data

• Create and view reports

With SQL Developer, you can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database. You can also connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, and Microsoft Access, view metadata and data in these databases, and migrate these databases to Oracle.

SQL*Plus is a basic command-line tool for entering and running ad hoc database statements. It lets you run SQL statements and PL/SQL blocks, and perform many additional tasks as well.

Automatic Memory Management

Beginning with Oracle Database 11g, Release 1, Oracle Database can manage the System Global Area (SGA) memory and instance Program Global Area (PGA) memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. In this memory management mode, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.

Automatic Storage Management

Automatic Storage Management automates and simplifies the management of all types of database files. Database files are automatically distributed across all available disks, and database storage is rebalanced automatically whenever the storage configuration changes. Automatic Storage Management also provides redundancy through the mirroring of database files.

Oracle Database has built-in support for the network file system (NFS) and does not depend on OS support for NFS. This improves manageability and diagnosability of network attached storage accessed with NFS.

Automatic Database Diagnostic Monitor

The Automatic Database Diagnostic Monitor (ADDM) lets you conduct performance analyzes over any time period defined by a pair of Automatic Workload Repository (AWR) snapshots taken on a particular instance. Analysis is performed top down, first identifying symptoms and then refining them to reach the root causes of performance problems. ADDM also documents non-problem areas of the system. For example, wait event classes that are not significantly affecting the performance of the system are identified and removed from the tuning consideration at an early stage, saving time and effort that would be spent on items with little or no impact on overall system performance.

In addition to problem diagnostics, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions for the DBA to choose from. ADDM considers a variety of changes to a system while generating its recommendations, which include hardware changes, database configuration changes, modification of schema objects, modification of applications, and referrals to other advisors.

SQL Tuning Advisor

Oracle Database provides a server utility called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as input and invokes the Automatic SQL Tuning Advisor to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendation, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. Users can choose whether or not to accept the recommendation to complete the tuning of the SQL statements.

SQL Access Advisor

The SQL Access Advisor makes schema modification recommendations. It can recommend that you create access structures such as indexes and materialized views to optimize SQL queries. It can also recommend that you partition tables, indexes, or materialized views to improve query performance.

The SQL Access Advisor takes a SQL workload as input. You can select your workload from various sources, including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment. The advisor then recommends changes to improve the performance of the workload as a whole.

Streams Tuning Advisor

A Streams topology is a representation of the databases in a Streams environment, the Streams components configured in these databases, and the flow of messages between these components. The Streams Performance Advisor reports performance measurements for a Streams topology, including throughput and latency measurements. The Streams Performance Advisor also identifies bottlenecks in a Streams topology so that they can be corrected. In addition, the Streams Performance advisor examines the Streams components in a Streams topology and recommends ways to improve their performance.

The Scheduler

To help simplify management tasks, as well as providing a rich set of functionality for complex scheduling needs, Oracle Database provides a collection of functions and procedures in the DBMS_SCHEDULER package. Collectively, these functions are called the Scheduler, and they are callable from any PL/SQL program.

The Scheduler lets database administrators and application developers control when and where various tasks take place in the database environment. For example, database administrators can schedule and monitor database maintenance jobs such as backups or nightly data warehousing loads and extracts.

Database Resource Manager

Traditionally, operating systems regulated resource management among various applications, including Oracle databases, that run on a system. The Database Resource Manager controls the distribution of resources among various sessions by controlling the execution schedule inside the database. By controlling which sessions run and for how long, the Database Resource Manager can ensure that resource distribution matches the plan directive and hence, the business objectives.

Data Warehousing

A data warehouse is a relational database designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

In addition to a relational database, a data warehouse environment includes an extraction, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

Extraction, Transformation, and Loading (ETL) You must load your data warehouse regularly so that it can serve its purpose of facilitating business analysis. To do this, data from one or more operational systems must be extracted and copied into the warehouse. The process of extracting data from source systems and bringing it into the data warehouse is commonly called ETL, which stands for extraction, transformation, and loading.

Bitmap Indexes in Data Warehousing The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle Database stores each key value repeatedly with each stored rowid. Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space, because the indexes can be several times larger than the data in the table.

In a bitmap index, the database stores a bitmap for each key value instead of a list of rowids. Bitmap indexes are typically only a fraction of the size of the indexed data in the table. Data warehousing environments typically have large amounts of data and ad hoc queries, but a low level of concurrent database manipulation language (DML) transactions. For such applications, bitmap indexing provides several advantages:

• Reduced response time for large classes of ad hoc queries

• Reduced storage requirements compared with other indexing techniques

• Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory

• Efficient maintenance during parallel DML and loads

In addition, bitmap join indexes improve query performance for typical data warehouse queries—which often include dimension/fact table joins—with about the same space usage as regular bitmap indexes.

Materialized Views

A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. Query response time is improved because the query accesses the materialized view instead of executing against the base tables. A materialized view can be stored in the same database as its base tables or in a different database.

Materialized views stored in the same database as their base tables can further improve query performance through query rewrite. Query rewrite is a mechanism that automatically rewrites a SQL query to use a materialized view instead of its base tables. With query rewrite, developers need not rewrite applications to take advantage of materialized views. Query rewrite is particularly useful in a data warehouse environment.

Table Compression

Oracle provides comprehensive data compression capabilities to compress all types of data, backups, and network traffic in an application transparent manner. These capabilities include table compression targeted at OLTP workloads, resulting in reduced storage consumption and improved query performance while incurring minimal write performance overhead. Table compression can be used to compress any relational data. To compress unstructured content use SecureFiles compression. Deduplication provides the ability to automatically eliminate redundant copies of SecureFiles data. A new faster compression algorithm is included to speed up RMAN backups. Data Pump exports can now be compressed to reduce disk space requirements. Finally, Data Guard can compress redo data resulting in reduced network traffic and faster gap resolution.

Parallel Execution

When Oracle Database runs SQL statements in parallel, multiple processes work together simultaneously to run a single SQL statement. By dividing the work necessary to run a statement among multiple processes, Oracle Database can run the statement more quickly than if only a single process ran it. This is called parallel execution or parallel processing. Parallel execution dramatically reduces response time for data-intensive operations on large databases.

Analytic SQL

Oracle Database has many SQL operations for performing analytic operations in the database. These include ranking, moving averages, cumulative sums, ratio-to-reports, and period-over-period comparisons.

OLAP Capabilities

Oracle online analytical processing (OLAP) provides native multidimensional storage and speed-of-thought response times when analyzing data across multiple dimensions. The database provides rich support for analytics such as time series calculations, forecasting, advanced aggregation with additive and nonadditive operators, and allocation operators. These capabilities make the Oracle database a complete analytical platform, capable of supporting the entire spectrum of business intelligence and advanced analytical applications. Oracle OLAP is fully integrated in the database, so that you can use standard SQL administrative, querying, and reporting tools.

Data Mining

With Oracle Data Mining, data never leaves the database — the data, data preparation, model building, and model scoring results all remain in the database. This enables Oracle Database to provide an infrastructure for application developers to integrate data mining seamlessly with database applications. Some typical examples of the applications that data mining are used in are call centers, ATMs, E-business relational management (ERM), and business planning applications. Data mining functions such as model building, testing, and scoring are provided through a Java API.

Very Large Databases (VLDB)

Partitioning is a critical feature for managing very large databases (VLDB). Growth is the basic challenge that partitioning addresses, and partitioning allows a database to scale for very large datasets while maintaining consistent performance, without unduly increasing administrative or hardware resources. Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces called partitions. No modifications to applications are necessary when accessing a partitioned table using SQL DML statements.

Partitioning can provide tremendous benefit to a wide variety of applications by improving availability, manageability, and performance.

XML in Oracle Database

Oracle XML DB is a set of Oracle Database technologies related to high-performance XML storage and retrieval. It provides native XML support by encompassing both SQL and XML data models in an interoperable manner. Oracle XML DB includes the following features:

• Support for the World Wide Web Consortium (W3C) XML and XML Schema data models and standard access methods for navigating and querying XML. The data models are incorporated into Oracle Database.

• The ability to store, query, update, and transform XML data while accessing it using SQL.

• The ability to perform XML operations on SQL data.

• A simple, lightweight XML repository where you can organize and manage database content, including XML, using a file/folder/URL metaphor.

• An infrastructure independent of storage format, content, and programming language for storing and managing XML data. This infrastructure provides new ways of navigating and querying XML content stored in the database. For example, Oracle XML DB Repository facilitates this by managing XML document hierarchies.

• Industry-standard access to and update of XML. The standards include the W3C XPath recommendation and the ISO-ANSI SQL/XML standard. FTP, HTTP(S), and WebDAV can be used to move XML content into and out of Oracle Database. Industry-standard APIs provide programmatic access and manipulation of XML content using Java, C, and PL/SQL.

• XML-specific memory management and optimizations.

• Enterprise-level Oracle Database features for XML content: reliability, availability, scalability, and security.

Oracle XML DB can be used in conjunction with Oracle XML Developer's Kit (XDK) to build applications that run in the middle tier in either Oracle Application Server or Oracle Database.

LOBs

The LOB datatypes BLOB, CLOB, NCLOB, and BFILE enable you to store and manipulate large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format. They provide efficient, random, piece-wise access to the data.

SecureFiles

SecureFiles is a new feature in Oracle Database 11g that offers the best solution for storing file content, such as images, audio, video, PDFs, and spreadsheets. Traditionally, relational data is stored in a database, while unstructured content—both semi-structured and unstructured—is stored as files in file systems. SecureFiles is a major paradigm shift in the choice of files storage. SecureFiles is specifically engineered to deliver high performance for file data comparable to that of traditional file systems, while retaining the advantages of Oracle Database. SecureFiles offers the best database and file system architecture attributes for storing unstructured content.

Key Technical Advantages SecureFiles includes advanced features, typically found in high-end file systems, such as:

• Deduplication: Oracle Database automatically detects multiple, identical SecureFiles data and stores only one copy, thereby saving storage space. In addition to storing only one copy, SecureFiles maintains references to other duplicates. Deduplication is completely transparent to applications and, in addition to simplifying storage management, it also results in significantly better performance, especially for copy operations. Duplicate detection happens within a LOB segment. The lob_storage_clause allows for specifying deduplication at a partition level so that duplicate detection does not span across partitions or subpartitions for partitioned SecureFiles columns.

  See Also:

  Oracle Database SecureFiles and Large Objects Developer's Guide for more information about deduplication

  SecureFiles deduplication is part of the Advanced Compression option.

• Compression: SecureFiles data is compressed using industry standard compression algorithms. Compression not only results in significant savings in storage but also improved performance by reducing I/O, buffer cache requirements, redo generation, and encryption overhead. If the compression does not yield any savings or if the data is already compressed, SecureFiles automatically turns off compression for such columns. Compression is performed on the server side and allows for random reads and writes to SecureFiles data. SecureFiles provides for varying degrees of compression: MEDIUM (default) and HIGH, which represent a tradeoff between storage savings and latency.

  See Also:

  Oracle Database SecureFiles and Large Objects Developer's Guide for more information about compression

  SecureFiles compression is part of the Advanced Compression option.

• Encryption: In Oracle Database 11g, Oracle has extended the encryption capability to SecureFiles and uses the Transparent Data Encryption (TDE) syntax. Oracle Database supports automatic key management for all SecureFiles columns within a table and transparently encrypts and decrypts data, backups, and redo log files. Applications require no changes and can take advantage of Oracle Database 11g SecureFiles using TDE semantics. SecureFiles supports the following encryption algorithms:

  • 3DES168: Triple Data Encryption Standard with a 168-bit key size

  • AES128: Advanced Encryption Standard with a 128 bit key size

  • AES192: Advanced Encryption Standard with a 192-bit key size (default)

  • AES256: Advanced Encryption Standard with a 256-bit key size

  See Also:

  Oracle Database SecureFiles and Large Objects Developer's Guide for more information about encryption

  SecureFiles encryption is part of the Advanced Security option.

• File System-like Logging: Modern file systems have the ability to keep a running log of the file system metadata. Putting this metadata into a running log (called a journal) that is flushed in a lazy fashion increases performance and removes the need for file system checking operations like fsck. SecureFiles' file system-like logging provides this same high performance journaling. File system-like logging also allows for soft corruptions, so that if an error is found on a block, SecureFiles returns a block with the LOB fill character. This allows the application to detect the error by seeing known invalid data and to recover either through deletion of the LOB (something that is not possible with the original implementation of LOBs) or by other means.

In addition to the aforementioned advanced file system features, SecureFiles can take advantage of several advanced Oracle Database capabilities, including:

• Transactions, read consistency, and flashback

• 100% backward compatibility with LOB interfaces

• Readable standby, consistent backup, and point-in-time recovery

• Fine-grained auditing and label security

• XML indexing, XML queries, and XPath

• Oracle Real Application Clusters

• Automatic Storage Management

• Partitioning and ILM

• Search across metadata and file content

High Performance SecureFiles is designed from the ground up for high performance and scalability. SecureFiles delivers comparable file system-like performance for basic read and write operations. The optimized algorithms with SecureFiles make it up to 10 times faster than LOBs. The scalability associated with SecureFiles goes far beyond what is offered in file systems. Organizations can scale-up using large SMP systems, or scale-out using Oracle Real Application Clusters to hundreds of computers while still preserving a single system image. Scaling of CPUs and disks can be done independently and transparently. With Oracle Database 11g, organizations can store all types of content and scale to store petabytes or exabytes of data.

Oracle Text

Oracle Text indexes any document or textual content to add fast, accurate retrieval of information. Oracle Text lets you combine text searches with regular database searches in a single SQL statement. The ability to find documents based on their textual content, metadata, or attributes, makes the Oracle Database the single point of integration for all data management.

The Oracle Text SQL API makes it simple and intuitive for application developers and DBAs to create and maintain Text indexes and run Text searches.

Oracle Ultra Search

Oracle Ultra Search lets you index and search Web sites, database tables, files, mailing lists, Oracle Application Server Portals, and user-defined data sources. This search capability lets you can use Oracle Ultra Search to build different kinds of search applications.

Oracle Multimedia

Oracle Multimedia provides an array of services to simplify the development of applications that include images, audio, and video. Oracle Multimedia objects are accessed as columns in tables, like other more typical relational data. Multimedia content can be stored and managed internally in the database, or externally by storing references to the content in the database. Java and PL/SQL APIs provide metadata extraction, image format conversion, and thumbnail image generation to greatly reduce application development and maintenance costs. Excellent integration with application development tools such as Oracle JDeveloper, Application Express, and Oracle Application Server Portal enable application developers to create and maintain media-rich applications with ease. In addition, Oracle Multimedia provides similar support for Digital Imaging and Communications in Medicine (DICOM) content such as single-frame and multiframe images, waveforms, slices of 3-D volumes, video segments, and structured reports.

Oracle Spatial

Oracle Database includes built-in spatial features that let you store, index, and manage location content—assets, buildings, roads, land parcels, sales regions, and so on—and query location relationships using the power of the database. The Oracle Spatial option adds advanced spatial features such as linear reference support and coordinate systems.

Security Mechanisms

Oracle Database provides discretionary access control, which is a means of restricting access to information based on privileges. The appropriate privilege must be assigned to a user in order for that user to access a schema object. Appropriately privileged users can grant other users privileges at their discretion.

Oracle Database manages database security using several different facilities:

• Authentication to validate the identity of the entities using your networks, databases, and applications

• Authorization processes to limit access and actions, limits that are linked to user's identities and roles

• Access restrictions on objects such as tables or rows

• Security policies

• Database auditing

  See Also:

  Chapter 20, "Database Security" for more information on security mechanisms

Integrity Constraints

An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about table data that is always true and that follows these rules:

• If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, then the constraint cannot be enforced.

• After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, then the statement is rolled back, and an error is returned.

Integrity constraints are stored as part of the table's definition in the data dictionary, so that all database applications adhere to the same set of rules. When a rule changes, you define it only once at the database level and not once for each application. A key is the column or set of columns included in the definition of certain types of integrity constraints. Keys describe the relationships between the different tables and columns of a relational database. Individual values in a key are called key values.

The following integrity constraints are supported by Oracle Database:

• A not null constraint disallows nulls (empty entries) in a table's column.

• A unique constraint disallows duplicate values in a column or set of columns. The unique key is the column or set of columns included in the definition of a unique constraint.

• A primary key constraint disallows duplicate values and nulls in a column or set of columns. The primary key is the column or set of columns included in the definition of a table's primary key constraint. The primary key values uniquely identify the rows in a table. You can define only one primary key for each table.

• A foreign key constraint—sometimes called a referential integrity constraint—requires each value in a column or set of columns to match a value in another table's unique key or primary key. Foreign key constraints also define referential integrity actions that dictate what Oracle Database should do with dependent data if the data it references is altered. The foreign key is the column or set of columns included in the definition of the foreign key constraint. The referenced key is the unique key or primary key of the same or a different table referenced by a foreign key.

• A check constraint disallows values that do not satisfy the logical expression of the constraint.

Triggers

Triggers are procedures written in PL/SQL, Java, or C that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.

Triggers supplement the standard capabilities of Oracle Database to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours.

Distributed SQL

A homogeneous distributed database system is a network of two or more Oracle databases that reside on one or more computers. Distributed SQL enables applications and users to simultaneously access or modify the data in several databases as easily as they access or modify a single database.

A distributed Oracle database system can appear as though it is a single Oracle database. Companies can use this distributed SQL feature to make all its Oracle databases look like one and thus reduce some of the complexity of the distributed system.

Oracle Database uses database links to enable users on one database to access objects in a remote database. A local user can access a link to a remote database without having to be a user on the remote database.

Oracle Streams

Oracle Streams enables the propagation and management of data, transactions, and events in a data stream either within a database or from one database to another. The stream conveys published information to subscribed destinations.

Oracle Streams lets users control what information is put into a stream, how the stream flows or is routed from node to node, what happens to events in the stream as they flow into each node, and how the stream terminates. By specifying the configuration of the elements acting on the stream, a user can address specific requirements, such as message queuing or data replication.

Oracle Database Gateways and Generic Connectivity

Oracle Database Gateways and Generic Connectivity extend distributed Oracle database features to non-Oracle systems. Generic Connectivity is a generic solution. Oracle Database Gateways are tailored solutions, specifically coded for a particular non-Oracle system. Oracle Database can work with non-Oracle data sources, non-Oracle message queuing systems, and non-SQL applications, ensuring interoperability with other vendors' products and technologies.

Oracle Database Gateways and Generic Connectivity can be used for synchronous access, using distributed SQL, and for asynchronous access, using Oracle Streams. Introducing a Transparent Gateway into an Oracle Streams environment enables replication of data from an Oracle database to a non-Oracle database.

Oracle Database Gateways and Generic Connectivity translate third-party SQL dialects, data dictionaries, and datatypes into Oracle Database formats, thus making the non-Oracle data store appear as a remote Oracle database. These features enable companies to seamlessly integrate the different systems and provide a consolidated view of the company as a whole.

SQL Statements

All operations on the information in an Oracle database are performed using SQL statements. A SQL statement is a string of SQL text. A statement must be the equivalent of a complete SQL sentence, as in:

SELECT last_name, department_id FROM employees; 

Only a complete SQL statement can run successfully. A sentence fragment like the following one generates an error indicating that more text is required:

SELECT last_name 

A SQL statement can be thought of as a very simple but powerful computer program or instruction. SQL statements are divided into the following categories:

Data definition language (DDL) statements create, alter, maintain, and drop schema objects. DDL statements also include statements that permit a user to grant other users the privileges to access the database and specific objects within the database.

Data manipulation language (DML) statements manipulate data. Querying, inserting, updating, and deleting rows of a table are all DML operations. The most common SQL statement is the SELECT statement, which retrieves data from the database. Locking a table or view and examining the execution plan of a SQL statement are also DML operations.

Transaction control statements manage the changes made by DML statements. They enable a user to group changes into logical transactions. Examples include COMMIT, ROLLBACK, and SAVEPOINT.

Session control statements let a user control the properties of the current session, including enabling and disabling roles and changing language settings. The two session control statements are ALTER SESSION and SET ROLE.

System control statements changes the properties of the Oracle database instance. ALTER SYSTEM is the only system control statement. It lets you change settings, such as the minimum number of shared servers. It also lets you kill a session and perform other system-wide tasks.

Embedded SQL statements incorporate DDL, DML, and transaction control statements in a procedural language program, such as those used with the Oracle precompilers. Examples include OPEN, CLOSE, FETCH, and EXECUTE.