【oracle11g官方文档阅读】Supporting Documentation上篇

编程入门行业动态更新时间:2024-10-10 06:22:47

【oracle11g官方文档阅读】Supporting Documentation<a href=https://www.elefans.com/category/jswz/34/1765805.html style= 上篇"/>

【oracle11g官方文档阅读】Supporting Documentation上篇

官方文档目录截图附上，如果我的内容有错误的地方还希望大家多多指正，谢谢

文章目录

- Concepts概念
- Administrator's Guide管理员手册
- Reference参考
- SQL Language Reference SQL参考
- Performance Tuning Guide 性能优化向导
- Error Messages 错误信息

Concepts概念

Title and Copyright Information标题和公司信息
Oracle® Database

Concepts

11g Release 2 (11.2)

E40540-04

May 2015

Oracle Database Concepts, 11g Release 2 (11.2)

E40540-04

Primary Authors: Lance Ashdown, Tom Kyte

Contributors: Drew Adams, David Austin, Vladimir Barriere, Hermann Baer, David Brower, Jonathan Creighton, Bjørn Engsig, Steve Fogel, Bill Habeck, Bill Hodak, Yong Hu, Pat Huey, Vikram Kapoor, Feroz Khan, Jonathan Klein, Sachin Kulkarni, Paul Lane, Adam Lee, Yunrui Li, Bryn Llewellyn, Rich Long, Barb Lundhild, Neil Macnaughton, Vineet Marwah, Mughees Minhas, Sheila Moore, Valarie Moore, Gopal Mulagund, Paul Needham, Gregory Pongracz, John Russell, Vivian Schupmann, Shrikanth Shankar, Cathy Shea, Susan Shepard, Jim Stenoish, Juan Tellez, Lawrence To, Randy Urbano, Badhri Varanasi, Simon Watt, Steve Wertheimer, Daniel Wong

翻译：贡献者：Drew Adams、David Austin、Vladimir Barriere、Hermann Baer、David Brower、Jonathan Creighton、BJ_rn Engsig、Steve Fogel、Bill Habeck、Bill Hodak、Yong Hu、Pat Huey、Vikram Kapoor、Feroz Khan、Jonathan Klein、Sachin Kulkarni、Paul Lane、Adam Lee、Yunrui Li、Bryn Llewellyn、Rich Long、BArb Lundhild、Neil Macnaughton、Vineet Marwah、Mughees Minhas、Sheila Moore、Valarie Moore、Gopal Mulagund、Paul Needham、Gregory Pongracz、John Russell、Vivian Schupmann、Shrikanth Shankar、Cathy Shea、Susan Shepard、Jim Stenoish、Juan Tellez、Lawrence To、Randy Urbano、Badhri Varanasi、Simon Watt，Steve Wertheimer，Daniel Wong

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
翻译：本软件和相关文档根据包含使用和披露限制的许可协议提供，受知识产权法保护。除非您的许可协议明确允许或法律允许，否则您不得以任何形式或通过任何方式使用、复制、复制、翻译、广播、修改、许可、传输、分发、展示、执行、发布或展示任何部分。除非法律要求互操作性，否则禁止反向工程、反汇编或反编译本软件

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
翻译：此处所含信息如有更改，恕不另行通知，不保证无误。如果您发现任何错误，请以书面形式向我们报告

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:
翻译：如果这是交付给美国政府或代表美国政府授权的任何人的软件或相关文档，则以下通知适用。

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are “commercial computer software” pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
翻译：美国政府最终用户：根据适用的联邦采购法规和agen，交付给美国政府最终用户的Oracle程序，包括任何操作系统、集成软件、安装在硬件上的任何程序和/或文档都是“商业计算机软件”。CY具体补充规定。因此，程序的使用、复制、披露、修改和改编，包括任何操作系统、集成软件、安装在硬件上的任何程序和/或文档，应遵守适用于程序的许可条款和许可限制。美国政府不享有任何其他权利

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
翻译：此软件或硬件是为在各种信息管理应用程序中的通用而开发的。本发明不适用于任何固有危险的应用，包括可能造成人身伤害的应用。如果您在危险应用中使用此软件或硬件，则您应负责采取所有适当的故障保护、备份、冗余和其他措施，以确保其安全使用。甲骨文公司及其附属公司对在危险应用中使用本软件或硬件造成的任何损害不承担任何责任。

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
翻译：Oracle和Java是Oracle和/或其附属公司的注册商标。其他名称可能是其各自所有者的商标。

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
翻译：Intel和Intel Xeon是Intel Corporation的商标或注册商标。所有SPARC商标均经许可使用，是SPARC International，Inc.的商标或注册商标。AMD、Opteron、AMD徽标和AMD Opteron徽标是Advanced Micro Devices的商标或注册商标。Unix是OpenGroup的注册商标。

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Preface预览
This manual provides an architectural and conceptual overview of the Oracle database server, which is an object-relational database management system. It describes how the Oracle database server functions, and it lays a conceptual foundation for much of the practical information contained in other manuals. Information in this manual applies to the Oracle database server running on all operating systems.
翻译：本手册提供了Oracle数据库服务器（对象关系数据库管理系统）的体系结构和概念概述。它描述了Oracle数据库服务器的功能，并为其他手册中包含的大量实用信息奠定了概念基础。本手册中的信息适用于在所有操作系统上运行的Oracle数据库服务器。

This preface contains these topics:
•Audience
•Documentation Accessibility
•Related Documentation
•Conventions

Audience

Oracle Database Concepts is intended for technical users, primarily database administrators and database application developers, who are new to Oracle Database. Typically, the reader of this manual has had experience managing or developing applications for other relational databases.
翻译：Oracle数据库概念主要面向对Oracle数据库不熟悉的技术用户，主要是数据库管理员和数据库应用程序开发人员。通常，本手册的读者具有管理或开发其他关系数据库应用程序的经验。

To use this manual, you must know the following:
使用本手册，你必须清楚一下内容：

•Relational database concepts in general
一般的关系数据库概念

•Concepts and terminology in Chapter 1, “Introduction to Oracle Database”
第一章“Oracle数据库简介”中的概念和术语

•The operating system environment under which you are running Oracle
运行Oracle的操作系统环境

Documentation Accessibility
文档可访问性

For information about Oracle’s commitment to accessibility, visit the Oracle Accessibility Program website at =acc&id=docacc.
有关Oracle对可访问性的承诺的信息，请访问Oracle可访问性计划网站：？CTX=ACC&ID=DOCACC.

Access to Oracle Support
访问Oracle支持

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit =acc&id=info or visit =acc&id=trs if you are hearing impaired.
翻译：购买支持的Oracle客户可以通过我的Oracle支持访问电子支持。有关信息，请访问？ctx=acc&id=info还是访问？CTX=ACC&ID=TRS，如果您有听力障碍。

Related Documentation
翻译：Related Documentation相关文件

This manual is intended to be read with the following manuals:
本手册将与以下手册一起阅读：

•Oracle Database 2 Day DBA
Oracle数据库2天DBA

•Oracle Database 2 Day Developer’s Guide
Oracle数据库2天开发人员指南

For more related documentation, see “Oracle Database Documentation Roadmap”.
有关更多相关文档，请参阅“Oracle数据库文档路线图”。

Many manuals in the Oracle Database documentation set use the sample schemas of the seed database, which is installed by default when you install Oracle Database. Refer to Oracle Database Sample Schemas for information on how these schemas were created and how you can use them.
Oracle数据库文档集中的许多手册都使用种子数据库的示例模式，在安装Oracle数据库时，默认情况下会安装该模式。有关如何创建这些模式以及如何使用它们的信息，请参阅Oracle数据库示例模式。

Conventions

The following text conventions are used in this manual:
本手册使用以下文本约定

1 Introduction to Oracle Database
Oracle数据库简介

This chapter provides an overview of Oracle Database and contains the following sections:
本章概述了Oracle数据库，并包含以下部分：

•About Relational Databases
关于关系数据库

•Schema Objects

•Data Access
数据访问

•Transaction Management
事务管理

•Oracle Database Architecture
Oracle数据库体系结构

•Oracle Database Documentation Roadmap
Oracle数据库文档路线图

About Relational Databases
关于关系型数据库

Every organization has information that it must store and manage to meet its requirements. For example, a corporation must collect and maintain human resources records for its employees. This information must be available to those who need it. An information system is a formal system for storing and processing information.
每个组织都有必须存储和管理以满足其需求的信息。例如，公司必须为其员工收集和维护人力资源记录。这些信息必须提供给需要它的人。信息系统是存储和处理信息的正式系统。

An information system could be a set of cardboard boxes containing manila folders along with rules for how to store and retrieve the folders. However, most companies today use a database to automate their information systems. A database is an organized collection of information treated as a unit. The purpose of a database is to collect, store, and retrieve related information for use by database applications.
信息系统可以是一组纸板箱，其中包含马尼拉文件夹以及如何存储和检索文件夹的规则。然而，现在大多数公司都使用数据库来自动化他们的信息系统。数据库是作为一个单元处理的有组织的信息集合。数据库的目的是收集、存储和检索相关信息，供数据库应用程序使用

Database Management System (DBMS)

A database management system (DBMS) is software that controls the storage, organization, and retrieval of data. Typically, a DBMS has the following elements:
数据库管理系统（DBMS）是控制数据存储、组织和检索的软件。通常，DBMS具有以下元素

•Kernel code

This code manages memory and storage for the DBMS.
他的代码管理DBMS的内存和存储

•Repository of metadata
元数据存储库

This repository is usually called a data dictionary.
此存储库通常称为数据字典

•Query language
查询语言

This language enables applications to access the data.
此语言使应用程序能够访问数据

A database application is a software program that interacts with a database to access and manipulate data.
数据库应用程序是与数据库交互以访问和操作数据的软件程序。

The first generation of database management systems included the following types:
第一代数据库管理系统包括以下类型

•Hierarchical

A hierarchical database organizes data in a tree structure. Each parent record has one or more child records, similar to the structure of a file system.
层次数据库以树结构组织数据。每个父记录都有一个或多个子记录，类似于文件系统的结构

•Network

A network database is similar to a hierarchical database, except records have a many-to-many rather than a one-to-many relationship.
网络数据库类似于分层数据库，只是记录有多对多关系，而不是一对多关系

The preceding database management systems stored data in rigid, predetermined relationships. Because no data definition language existed, changing the structure of the data was difficult. Also, these systems lacked a simple query language, which hindered application development.
前面的数据库管理系统以严格的预定关系存储数据。由于不存在数据定义语言，因此很难改变数据的结构。此外，这些系统缺乏简单的查询语言，这阻碍了应用程序的开发。

Relational Model
关系模型

In his seminal 1970 paper “A Relational Model of Data for Large Shared Data Banks,” E. F. Codd defined a relational model based on mathematical set theory. Today, the most widely accepted database model is the relational model.
翻译：E.F.Codd在他1970年的论文《大型共享数据银行的数据关系模型》中定义了一个基于数学集理论的关系模型。今天，最广泛接受的数据库模型是关系模型。

A relational database is a database that conforms to the relational model. The relational model has the following major aspects:
关系数据库是符合关系模型的数据库。关系模型主要有以下几个方面

•Structures

Well-defined objects store or access the data of a database.
定义良好的对象存储或访问数据库的数据。

•Operations

Clearly defined actions enable applications to manipulate the data and structures of a database.
明确定义的操作使应用程序能够操作数据库的数据和结构。

•Integrity rules

Integrity rules govern operations on the data and structures of a database.

A relational database stores data in a set of simple relations. A relation is a set of tuples. A tuple is an unordered set of attribute values.
关系数据库以一组简单关系存储数据。关系是一组元组。元组是一组无序的属性值

A table is a two-dimensional representation of a relation in the form of rows (tuples) and columns (attributes). Each row in a table has the same set of columns. A relational database is a database that stores data in relations (tables). For example, a relational database could store information about company employees in an employee table, a department table, and a salary table.
表是以行（元组）和列（属性）的形式表示关系的二维表示。表中的每一行都有相同的列集。关系数据库是在关系（表）中存储数据的数据库。例如，关系数据库可以在员工表、部门表和薪资表中存储有关公司员工的信息。

See Also:
.cfm?id=362685 for an abstract and link to Codd’s paper

Relational Database Management System (RDBMS)

The relational model is the basis for a relational database management system (RDBMS). Essentially, an RDBMS moves data into a database, stores the data, and retrieves it so that it can be manipulated by applications. An RDBMS distinguishes between the following types of operations:
关系模型是关系数据库管理系统（RDBMS）的基础。本质上，RDBMS将数据移动到数据库中，存储数据，并检索数据，以便应用程序对其进行操作。RDBMS区分以下类型的操作

•Logical operations

In this case, an application specifies what content is required. For example, an application requests an employee name or adds an employee record to a table.
在这种情况下，应用程序指定需要什么内容。例如，应用程序请求员工姓名或向表中添加员工记录

•Physical operations

In this case, the RDBMS determines how things should be done and carries out the operation. For example, after an application queries a table, the database may use an index to find the requested rows, read the data into memory, and perform many other steps before returning a result to the user. The RDBMS stores and retrieves data so that physical operations are transparent to database applications.
在这种情况下，RDBMS决定应该如何执行操作。例如，在应用程序查询表之后，数据库可以使用索引查找请求的行，将数据读取到内存中，并在将结果返回给用户之前执行许多其他步骤。RDBMS存储和检索数据，以便物理操作对数据库应用程序透明。

Oracle Database is an RDBMS. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism 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.
Oracle数据库是一个RDBMS。实现面向对象特性（如用户定义的类型、继承和多态性）的RDBMS称为对象关系数据库管理系统（ORDBMS）。Oracle数据库已将关系模型扩展到对象关系模型，使在关系数据库中存储复杂的业务模型成为可能。

Brief History of Oracle Database
Oracle数据库简史

The current version of Oracle Database is the result of over 30 years of innovative development. Highlights in the evolution of Oracle Database include the following:
Oracle数据库的当前版本是30多年创新开发的结果。Oracle数据库发展的亮点包括：

•Founding of Oracle
甲骨文公司的成立
备注：Founding
n. 创办，发起；铸造；溶解
adj. 创办的，发起的
v. 创办（组织）；建立（国家）；以……为基础；熔化（found 的现在分词）

In 1977, Larry Ellison, Bob Miner, and Ed Oates started the consultancy Software Development Laboratories, which became Relational Software, Inc. (RSI). In 1983, RSI became Oracle Systems Corporation and then later Oracle Corporation.
1977年，Larry Ellison、Bob Miner和Ed Oates创立了顾问软件开发实验室，后来成为关系软件公司（RSI）。1983年，RSI成为甲骨文系统公司，后来又成为甲骨文公司。

•First commercially available RDBMS
翻译：第一个商用的RDBMS

In 1979, RSI introduced Oracle V2 (Version 2) as the first commercially available SQL-based RDBMS, a landmark event in the history of relational databases.
翻译：1979年，RSI引入Oracle V2 (Version 2)作为第一个商用的基于sql的RDBMS，这是关系型数据库历史上的一个里程碑事件。

•Portable version of Oracle Database
翻译：可移植版本的Oracle数据库

Oracle Version 3, released in 1983, was the first relational database to run on mainframes, minicomputers, and PCs. The database was written in C, enabling the database to be ported to multiple platforms.
翻译：1983年发布的Oracle Version 3是第一个在大型机、小型计算机和pc上运行的关系数据库。该数据库是用C语言编写的，允许将数据库移植到多个平台。

•Enhancements to concurrency control, data distribution, and scalability
fan增强并发控制、数据分发和可伸缩性

Version 4 introduced multiversion read consistency. Version 5, released in 1985, supported client/server computing and distributed database systems. Version 6 brought enhancements to disk I/O, row locking, scalability, and backup and recovery. Also, Version 6 introduced the first version of the PL/SQL language, a proprietary procedural extension to SQL.
版本4引入了多版本读取一致性。1985年发布的版本5支持客户机/服务器计算和分布式数据库系统。Version 6增强了磁盘I/O、行锁定、可伸缩性以及备份和恢复。此外，Version 6引入了PL/SQL语言的第一个版本，这是SQL的一个专有过程扩展

•PL/SQL stored program units

Oracle7, released in 1992, introduced PL/SQL stored procedures and triggers.
Oracle7于1992年发布，引入了PL/SQL存储过程和触发器

•Objects and partitioning
翻译：对象和分区

Oracle8 was released in 1997 as the object-relational database, supporting many new data types. Additionally, Oracle8 supported partitioning of large tables.
翻译：Oracle8作为对象关系数据库于1997年发布，支持许多新的数据类型。此外，Oracle8支持大型表的分区。

•Internet computing
翻译：网络计算

Oracle8i Database, released in 1999, provided native support for internet protocols and server-side support for Java. Oracle8i was designed for internet computing, enabling the database to be deployed in a multitier environment.
翻译：Oracle8i数据库于1999年发布，提供了对internet协议的本地支持和对Java的服务器端支持。Oracle8i是为internet计算而设计的，使数据库能够部署在多层环境中。

•Oracle Real Application Clusters (Oracle RAC)
翻译：Oracle实际应用程序集群(Oracle RAC)

Oracle9i Database introduced Oracle RAC in 2001, enabling multiple instances to access a single database simultaneously. Additionally, Oracle XML Database (Oracle XML DB) introduced the ability to store and query XML.
翻译：Oracle9i数据库在2001年引入了Oracle RAC，允许多个实例同时访问一个数据库。此外，Oracle XML数据库(Oracle XML DB)引入了存储和查询XML的功能。

•Grid computing
翻译：网格计算

Oracle Database 10g introduced grid computing in 2003. This release enabled organizations to virtualize computing resources by building a grid infrastructure based on low-cost commodity servers. A key goal was to make the database self-managing and self-tuning. Oracle Automatic Storage Management (Oracle ASM) helped achieve this goal by virtualizing and simplifying database storage management.
翻译：Oracle数据库10g于2003年引入网格计算。该版本允许组织通过构建基于低成本商品服务器的网格基础设施来虚拟化计算资源。一个关键的目标是使数据库能够自我管理和自我调优。Oracle Automatic Storage Management (Oracle ASM)通过虚拟化和简化数据库存储管理帮助实现了这一目标

•Manageability, diagnosability, and availability
翻译：可管理性、诊断性和可用性

Oracle Database 11g, released in 2007, introduced a host of new features that enable administrators and developers to adapt quickly to changing business requirements. The key to adaptability is simplifying the information infrastructure by consolidating information and using automation wherever possible.

See Also:
.html for an article summarizing the evolution of Oracle Database

Schema Objects

One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects. A database schema is owned by a database user and 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. Some database objects, such as profiles and roles, do not reside in schemas.

See Also:
“Introduction to Schema Objects”

Tables

A table describes an entity such as employees. You define a table with a table name, such as employees, and set of columns. In general, you give each column a name, a data type, and a width when you create the table.

A table is a set of rows. A column identifies an attribute of the entity described by the table, whereas a row identifies an instance of the entity. For example, attributes of the employees entity correspond to columns for employee ID and last name. A row identifies a specific employee.

You can optionally specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.

See Also:

•Chapter 9, “Data Concurrency and Consistency”

•Oracle Database Advanced Application Developer’s Guide to learn about Flashback Query
racle数据库高级应用程序开发人员指南了解回闪查询

Oracle Database Architecture
Oracle数据库体系结构

A database server is the key to information management. In general, a server reliably manages a large amount of data in a multiuser environment so that users can concurrently access the same data. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
数据库服务器是信息管理的关键。通常，服务器在多用户环境中可靠地管理大量数据，这样用户就可以同时访问相同的数据。数据库服务器还可以防止未经授权的访问，并为故障恢复提供有效的解决方案。

Database and Instance
数据库和实例

An Oracle database server consists of a database and at least one database instance (commonly referred to as simply an instance). Because an instance and a database are so closely connected, the term Oracle database is sometimes used to refer to both instance and database. In the strictest sense the terms have the following meanings:
Oracle数据库服务器由一个数据库和至少一个数据库实例（通常简称为实例）组成。因为一个实例和一个数据库是如此紧密地联系在一起，所以有时“Oracle数据库”一词用来指代实例和数据库。在最严格的意义上，这些术语有以下含义

•Database数据库

A database is a set of files, located on disk, that store data. These files can exist independently of a database instance.

•Database instance数据库实例

An instance is a set of memory structures that manage database files. The instance consists of a shared memory area, called the system global area (SGA), and a set of background processes. An instance can exist independently of database files.

Figure 1-1 shows a database and its instance. For each user connection to the instance, the application is run by a client process. Each client process is associated with its own server process. The server process has its own private session memory, known as the program global area (PGA).

Figure 1-1 Oracle Instance and Database

A database can be considered from both a physical and logical perspective. Physical data is data viewable at the operating system level. For example, operating system utilities such as the Linux ls and ps can list database files and processes. Logical data such as a table is meaningful only for the database. A SQL statement can list the tables in an Oracle database, but an operating system utility cannot.
可以从物理和逻辑的角度考虑数据库。物理数据可以在操作系统级别查看。例如，LinuxLS和PS等操作系统实用程序可以列出数据库文件和进程。逻辑数据（如表）仅对数据库有意义。SQL语句可以列出Oracle数据库中的表，但操作系统实用工具不能。

The database has physical structures and logical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting access to logical storage structures. For example, renaming a physical database file does not rename the tables whose data is stored in this file.
数据库有物理结构和逻辑结构。由于物理和逻辑结构是分开的，因此可以在不影响对逻辑存储结构的访问的情况下管理数据的物理存储。例如，重命名物理数据库文件不会重命名其数据存储在此文件中的表。

Database Storage Structures

An essential task of a relational database is data storage. This section briefly describes the physical and logical storage structures used by Oracle Database.

Physical Storage Structures

The physical database structures are the files that store the data. When you execute the SQL command CREATE DATABASE, the following files are created:
物理数据库结构是存储数据的文件。执行SQL命令创建数据库时，将创建以下文件：

•Data files

Every Oracle database has one or more physical data files, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the data files.
每个Oracle数据库都有一个或多个物理数据文件，其中包含所有数据库数据。逻辑数据库结构（如表和索引）的数据物理存储在数据文件中。

•Control files

Every Oracle database has a control file. A control file contains metadata specifying the physical structure of the database, including the database name and the names and locations of the database files.
每个Oracle数据库都有一个控制文件。控制文件包含指定数据库物理结构的元数据，包括数据库名称和数据库文件的名称和位置。

•Online redo log files

Every Oracle Database has an online redo log, which is a set of two or more online redo log files. An online redo log is made up of redo entries (also called redo records), which record all changes made to data.
个Oracle数据库都有一个联机重做日志，它是由两个或多个联机重做日志文件组成的集合。在线重做日志由重做条目（也称为重做记录）组成，记录对数据所做的所有更改。

Many other files are important for the functioning of an Oracle database server. These files include parameter files and diagnostic files. Backup files and archived redo log files are offline files important for backup and recovery.
许多其他文件对于Oracle数据库服务器的运行很重要。这些文件包括参数文件和诊断文件。备份文件和归档的重做日志文件是离线文件，对备份和恢复很重要。

Logical Storage Structures

This section discusses logical storage structures. The following logical storage structures enable Oracle Database to have fine-grained control of disk space use:
本节讨论逻辑存储结构。以下逻辑存储结构使Oracle数据库能够对磁盘空间使用进行细粒度控制

•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 on disk.
在最好的粒度级别上，Oracle数据库数据存储在数据块中。一个数据块对应于磁盘上的特定字节数。

•Extents

An extent is a specific number of logically contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
数据块是在单个分配中获得的逻辑上连续的数据块的特定数目，用于存储特定类型的信息。

•Segments

A segment is a set of extents allocated for a user object (for example, a table or index), undo data, or temporary data.
段是为用户对象（例如表或索引）、撤消数据或临时数据分配的一组区。

•Tablespaces

A database is divided into logical storage units called tablespaces. A tablespace is the logical container for a segment. Each tablespace contains at least one data file.
数据库被划分为称为表空间的逻辑存储单元。表空间是一个段的逻辑容器。每个表空间至少包含一个数据文件

Database Instance Structures
数据库实例结构

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 RDBMS.
Oracle数据库使用内存结构和进程来管理和访问数据库。所有的内存结构都存在于构成RDBMS的计算机的主内存中。

When applications connect to an Oracle database, they are connected to a database instance. The instance services applications by allocating other memory areas in addition to the SGA, and starting other processes in addition to background processes.
当应用程序连接到Oracle数据库时，它们将连接到数据库实例。实例通过在SGA之外分配其他内存区域，以及在后台进程之外启动其他进程来为应用程序提供服务。

Oracle Database Processes

A process is a mechanism in an operating system that can run a series of steps. Some operating systems use the terms job, task, or thread. For the purpose of this discussion, a thread is equivalent to a process. An Oracle database instance has the following types of processes:
进程是操作系统中可以运行一系列步骤的机制。一些操作系统使用术语job、task或thread。在本讨论中，线程等同于进程。Oracle数据库实例具有以下类型的进程：

•Client processes

These processes are created and maintained to run the software code of an application program or an Oracle tool. Most environments have separate computers for client processes.
创建和维护这些过程是为了运行应用程序或Oracle工具的软件代码。大多数环境都有单独的计算机用于客户端进程

•Background processes

These processes consolidate functions that would otherwise be handled by multiple Oracle Database programs running for each client process. Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.
这些进程合并了一些功能，否则将由为每个客户机进程运行的多个Oracle数据库程序处理。后台进程异步执行I/O并监视其他Oracle数据库进程，以提高并行性，从而提高性能和可靠性。

•Server processes

These processes communicate with client processes and interact with Oracle Database to fulfill requests.
这些进程与客户机进程通信，并与Oracle数据库交互以满足请求。

Oracle processes include server processes and background processes. In most environments, Oracle processes and client processes run on separate computers.

Instance Memory Structures

Oracle Database creates and uses memory structures for purposes such as memory for program code, data shared among users, and private data areas for each connected user. The following memory structures are associated with an instance:

•System Global Area (SGA)

The SGA is a group of shared memory structures that contain data and control information for one database instance. Examples of SGA components include cached data blocks and shared SQL areas.

•Program Global Areas (PGA)

A PGA is a memory region that contain data and control information for a server or background process. Access to the PGA is exclusive to the process. Each server process and background process has its own PGA.

Application and Networking Architecture

To take full advantage of a given computer system or network, Oracle Database enables processing to be split between the database server and the client programs. The computer running the RDBMS handles the database server responsibilities while the computers running the applications handle the interpretation and display of data.

Application Architecture应用结构

The application architecture refers to the computing environment in which a database application connects to an Oracle database. The two most common database architectures are client/server and multitier.
应用程序体系结构是指数据库应用程序连接到Oracle数据库的计算环境。两种最常见的数据库体系结构是客户机/服务器和多线程。

In a client/server architecture, the client application initiates a request for an operation to be performed on the database server. The server runs Oracle Database software and handles the functions required for concurrent, shared data access. The server receives and processes requests that originate from clients.
在客户机/服务器体系结构中，客户机应用程序启动对要在数据库服务器上执行的操作的请求。服务器运行Oracle数据库软件并处理并发共享数据访问所需的功能。服务器接收并处理来自客户端的请求

In a traditional multitier architecture, one or more application servers 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 lessening the load on the database. The application server can serve as an interface between clients and multiple databases and provide an additional level of security.
在传统的多层次体系结构中，一个或多个应用服务器执行部分操作。应用服务器包含应用程序逻辑的很大一部分，为客户机提供对数据的访问，并执行一些查询处理，从而减少数据库的负载。应用服务器可以作为客户机和多个数据库之间的接口，并提供额外的安全级别。

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 are accessible through HTTP and are based on XML-based standards such as Web Services Description Language (WSDL) and SOAP.
面向服务的体系结构（SOA）是一种多层次体系结构，其中应用程序功能封装在服务中。SOA服务通常作为Web服务实现。Web服务可以通过HTTP访问，并且基于基于XML的标准，如Web服务描述语言（WSDL）和SOAP。

Oracle Database can act as a Web service provider in a traditional multitier or SOA environment.

Networking Architecture

Oracle Net Services is the interface between the 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.
racle Net Services是数据库和网络通信协议之间的接口，用于促进分布式处理和分布式数据库。通信协议定义在网络上传输和接收数据的方式。Oracle Net Services支持所有主要网络协议上的通信，包括TCP/IP、HTTP、FTP和WebDAV。

Oracle Net, a component of Oracle Net Services, establishes and maintains a network session from a client application to a database server. After 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.
Oracle Net是Oracle Net Services的一个组件，它建立并维护从客户机应用程序到数据库服务器的网络会话。网络会话建立后，Oracle NET充当客户机应用程序和数据库服务器的数据信使，在它们之间交换消息。Oracle网络可以执行这些作业，因为它位于网络中的每台计算机上。

An important component of Net Services is the Oracle Net Listener (called the listener), which is a separate process that runs on the database server or elsewhere in the network. Client applications can send connection requests to the listener, which manages the traffic of these requests to the database server. When a connection is established, the client and database communicate directly.
网络服务的一个重要组件是Oracle网络侦听器（称为侦听器），它是一个单独的进程，运行在数据库服务器上或网络中的其他地方。客户端应用程序可以向侦听器发送连接请求，侦听器管理这些请求到数据库服务器的流量。当建立连接时，客户机和数据库直接通信

The most common ways to configure an Oracle database to service client requests are:

•Dedicated server architecture

Each client process connects to a dedicated server process. The server process is not shared by any other client for the duration of the client’s session. Each new session is assigned a dedicated server process.
每个客户机进程都连接到一个专用的服务器进程。在客户端会话期间，任何其他客户端都不共享服务器进程。每个新会话都被分配一个专用的服务器进程。

•Shared server architecture

The database uses a pool of shared processes for multiple sessions. A client process communicates with a dispatcher, which is a process that enables many clients to connect to the same database instance without the need for a dedicated server process for each client.
数据库将共享进程池用于多个会话。客户机进程与调度程序通信，调度程序是一个进程，它使许多客户机能够连接到同一个数据库实例，而不需要为每个客户机提供专用的服务器进程。

Oracle Database Documentation Roadmap

This section explains how this manual should be read and where it fits into the Oracle Database documentation set as a whole.
本节将解释如何阅读本手册，以及如何将其作为一个整体装入Oracle数据库文档集。

To a new user, the Oracle Database documentation library can seem daunting. Not only are there are over 175 manuals, but many of these manuals are several hundred pages long. However, the documentation is designed with specific access paths to ensure that users are able to find the information they need as efficiently as possible.
对于一个新用户来说，Oracle数据库文档库可能看起来令人望而生畏。不仅有超过175本手册，而且其中许多手册长达数百页。但是，文档设计有特定的访问路径，以确保用户能够尽可能高效地找到所需的信息。

The documentation set is divided into three layers or groups: basic, intermediate, and advanced. Users begin with the manuals in the basic group (Oracle Database 2 Day DBA, Oracle Database 2 Day Developer’s Guide, or this manual), proceed to the manuals in the intermediate group (the 2 Day + series), and finally to the advanced manuals, which include the remainder of the documentation set.
文档集分为三个层或组：基本层、中间层和高级层。用户从基本组中的手册（Oracle数据库2天DBA、Oracle数据库2天开发人员指南或本手册）开始，进入中间组中的手册（2天+系列），最后进入高级手册，其中包括文档集的其余部分。

Basic Group

Technical users who are new to Oracle Database begin by reading one or more manuals in the basic group from cover to cover. Each manual in this group is designed to be read in two days. In addition to this manual, the basic group includes:
对Oracle数据库不熟悉的技术用户从阅读基本组中的一个或多个手册开始。本组中的每本手册设计为两天内阅读。除本手册外，基本组还包括：

•Oracle Database 2 Day DBA

This manual is a task-based DBA quick start that teaches you how to perform day-to-day database administrative tasks. It teaches you how to perform all common administrative tasks needed to keep the database operational, including how to perform basic troubleshooting and performance monitoring activities.
翻译：
本手册是一个基于任务的DBA快速入门，它教您如何执行日常数据库管理任务。它教您如何执行保持数据库正常运行所需的所有常见管理任务，包括如何执行基本的故障排除和性能监视活动。

•Oracle Database 2 Day Developer’s Guide

This manual is a task-based database developer quick start guide that explains how to use the basic features of Oracle Database through SQL and PL/SQL.
翻译：
本手册是一个基于任务的数据库开发人员快速入门指南，它解释了如何通过SQL和PL/SQL使用Oracle数据库的基本功能。

The manuals in the basic group are closely related, which is reflected in the number of cross-references. For example, Oracle Database Concepts frequently sends users to a 2 Day manual to learn how to perform a task based on a concept. The 2 Day manuals frequently references Oracle Database Concepts for conceptual background about a task.
翻译：基本组中的手册是密切相关的，这反映在交叉引用的数量上。例如，Oracle数据库概念经常向用户发送为期两天的手册，以了解如何根据概念执行任务。为期两天的手册经常参考Oracle数据库概念，以了解任务的概念背景。

Intermediate Group
中间件组

The next step up from the basic group is the intermediate group. The manuals in this group are prefixed with the word 2 Day + because they expand on and assume information contained in the 2 Day manuals. These manuals cover topics in more depth than was possible in the basic manuals, or cover topics of special interest. As shown in Table 1-1, the 2 Day + manuals are divided into manuals for DBAs and developers.
翻译：基本组的下一步是中间组。此组中的手册前面加上单词2day+，因为它们扩展并假定包含在2day手册中的信息。这些手册涵盖的主题比基本手册中可能涉及的更深入，或者涵盖了特别感兴趣的主题。如表1-1所示，2天+手册分为DBA和开发人员手册。

Table 1-1 Intermediate Group: 2 Day + Guides

Advanced Group 高级组

The next step up from the intermediate group is the advanced group. These manuals are intended for expert users who require more detailed information about a particular topic than can be provided by the 2 Day + manuals. Essential reference manuals in the advanced group include:

•Oracle Database SQL Language Reference

This manual is the definitive source of information about Oracle SQL.
本手册是有关Oracle SQL的最终信息来源。

•Oracle Database Reference

The manual is the definitive source of information about initialization parameters, data dictionary views, and dynamic performance views.
手册是有关初始化参数、数据字典视图和动态性能视图的最终信息源。

The advanced guides are too numerous to list in this section. Table 1-2 lists guides that are used by the majority of expert DBAs and developers at one time or another.
高级指南太多，无法在此部分列出。表1-2列出了大多数专家DBA和开发人员一次或另一次使用的指南。

Table 1-2 Advanced Group

Part I Oracle Relational Data Structures
Part I

Oracle Relational Data Structures

This part describes the basic data structures of an Oracle database, including data integrity rules, and the structures that store metadata.

This part contains the following chapters:

•Chapter 2, “Tables and Table Clusters”

•Chapter 3, “Indexes and Index-Organized Tables”

•Chapter 4, “Partitions, Views, and Other Schema Objects”

•Chapter 5, “Data Integrity”

•Chapter 6, “Data Dictionary and Dynamic Performance Views”

2 Tables and Table Clusters
表和表集群

This chapter provides an introduction to schema objects and discusses tables, which are the most common types of schema objects.
这一个章节提供一个schema对象介绍和讨论表，这些是一个schema通用的类型

This chapter contains the following sections:
这一章节包含一下内容

•Introduction to Schema Objects
Schema 对象的介绍

•Overview of Tables
表概述

•Overview of Table Clusters
表集群概述

Introduction to Schema Objects
Schema 对象介绍

A database schema is a logical container for data structures, called schema objects. Examples of schema objects are tables and indexes. Schema objects are created and manipulated with SQL.
一个数据的schema是一个对于数据结构逻辑的容器，被称作为schema对象。schema 的一个例子是表和索引。Schema 对象是用SQL创建和管理的。

A database user has a password and various database privileges. Each user owns a single schema, which has the same name as the user. The schema contains the data for the user owning the schema. For example, the hr user owns the hr schema, which contains schema objects such as the employees table. In a production database, the schema owner usually represents a database application rather than a person.
一个数据库用户有一个密码和各种各样的数据库权限，每一个用户属于单独的一个schema，这个schema的名称就是用户名，这个schema包含了用户自己的数据。例如hr用户有一个hr的schema，这个schema包含了例如雇员表的对象。在一个生产数据库中，模式所有者通常表示一个数据库应用程序，而不是一个人。

Within a schema, each schema object of a particular type has a unique name. For example, hr.employees refers to the table employees in the hr schema. Figure 2-1 depicts a schema owner named hr and schema objects within the hr schema.
在一个schema中，特定类型的每个schema对象都有一个唯一的名称，例如hr.employees指的是Hr模式下的雇员表

Schema Object Types
模式对象类型

The most important schema objects in a relational database are tables. A table stores data in rows.
在一个关系型数据库中最重要的对象就是表了，一个表在每一行上存储数据。

Oracle SQL enables you to create and manipulate many other types of schema objects, including the following:

Oracle SQL允许你创建和管理一些schema 对象，包括以下内容：

•Indexes 索引

Indexes are schema objects that contains an entry for each indexed row of the table or table cluster and provide direct, fast access to rows. Oracle Database supports several types of index. An index-organized table is a table in which the data is stored in an index structure. See Chapter 3, “Indexes and Index-Organized Tables”.
索引是模式对象，它包含表或表集群中每个索引行的一个条目，并提供对行的直接、快速访问。Oracle数据库支持多种类型的索引。索引组织表是将数据存储在索引结构中的表。见第3章“索引和索引组织表”。

•Partitions 分区

Partitions are pieces of large tables and indexes. Each partition has its own name and may optionally have its own storage characteristics. See “Overview of Partitions”.
分区是大型表和索引的分片，每个分区都有自己的名称，并且可以选择具有自己的存储特性。

•Views 视图

Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data. See “Overview of Views”.
视图是一个或多个表或其他视图中数据的自定义表示。您可以将它们视为存储查询。视图实际上不包含数据。参见“视图概述”。

•Sequences 序列

A sequence is a user-created object that can be shared by multiple users to generate integers. Typically, sequences are used to generate primary key values. See “Overview of Sequences”.
序列是用户创建的对象，可以由多个用户共享以生成整数。通常，序列用于生成主键值。参见“序列概述”。

•Dimensions 维

A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. Dimensions are commonly used to categorize data such as customers, products, and time. See “Overview of Dimensions”.
维度定义列集对之间的父子关系，其中列集的所有列必须来自同一个表。维度通常用于对客户、产品和时间等数据进行分类。参见“尺寸概述”。
备注：具体的Oracle维的使用请参考本人转载的一篇文章，地址如下

•Synonyms 同义词

A synonym is an alias for another schema object. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary. See “Overview of Synonyms”.
同义词是另一个模式对象的别名。因为同义词只是一个别名，所以除了在数据字典中定义外，它不需要任何存储。参见“同义词概述”。

•PL/SQL subprograms and packages

PL/SQL is the Oracle procedural extension of SQL. A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters. A PL/SQL package groups logically related PL/SQL types, variables, and subprograms. See “PL/SQL Subprograms” and “PL/SQL Packages”.
pl/sql是SQL的Oracle过程扩展。pl/sql子程序是一个命名的pl/sql块，可以用一组参数调用它。pl/sql包将逻辑相关的pl/sql类型、变量和子程序分组。请参见“pl/sql子程序”和“pl/sql包”。

Other types of objects are also stored in the database and can be created and manipulated with SQL statements but are not contained in a schema. These objects include database users, roles, contexts, and directory objects.
其他类型的对象也存储在数据库中，可以用SQL语句创建和操作，但不包含在架构中。这些对象包括数据库用户、角色、上下文和目录对象。

Schema Object Storage
Schema对象的存储

Some schema objects store data in logical storage structures called segments. For example, a nonpartitioned heap-organized table or an index creates a segment. Other schema objects, such as views and sequences, consist of metadata only. This section describes only schema objects that have segments.
一些schema 对象存储数据是在被叫做段的逻辑存储结构，例如非分区堆组织的表或索引创建段。一些schema对象例如视图和序列，元数据的一致性。本节只描述具有段的模式对象

Oracle Database stores a schema object logically within a tablespace. There is no relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a schema can be contained in different tablespaces. The data of each object is physically contained in one or more data files.

Figure 2-2 shows a possible configuration of table and index segments, tablespaces, and data files. The data segment for one table spans two data files, which are both part of the same tablespace. A segment cannot span multiple tablespaces.

Figure 2-2 Segments, Tablespaces, and Data Files

Schema Object Dependencies

Some schema objects reference other objects, creating schema object dependencies. For example, a view contains a query that references tables or other views, while a PL/SQL subprogram invokes other subprograms. If the definition of object A references object B, then A is a dependent object with respect to B and B is a referenced object with respect to A.

Oracle Database provides an automatic mechanism to ensure that a dependent object is always up to date with respect to its referenced objects. When a dependent object is created, the database tracks dependencies between the dependent object and its referenced objects. When a referenced object changes in a way that might affect a dependent object, the dependent object is marked invalid. For example, if a user drops a table, no view based on the dropped table is usable.

An invalid dependent object must be recompiled against the new definition of a referenced object before the dependent object is usable. Recompilation occurs automatically when the invalid dependent object is referenced.

As an illustration of how schema objects can create dependencies, the following sample script creates a table test_table and then a procedure that queries this table:

CREATE TABLE test_table ( col1 INTEGER, col2 INTEGER );CREATE OR REPLACE PROCEDURE test_proc
AS
BEGINFOR x IN ( SELECT col1, col2 FROM test_table )LOOP-- process dataNULL;END LOOP;
END;
/

The following query of the status of procedure test_proc shows that it is valid:

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

After adding the col3 column to test_table, the procedure is still valid because the procedure has no dependencies on this column:

SQL> ALTER TABLE test_table ADD col3 NUMBER;Table altered.SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

However, changing the data type of the col1 column, which the test_proc procedure depends on in, invalidates the procedure:

SQL> ALTER TABLE test_table MODIFY col1 VARCHAR2(20);Table altered.SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   INVALID

Running or recompiling the procedure makes it valid again, as shown in the following example:

SQL> EXECUTE test_procPL/SQL procedure successfully completed.SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

SYS and SYSTEM Schemas

All Oracle databases include default administrative accounts. Administrative accounts are highly privileged and are intended only for DBAs authorized to perform tasks such as starting and stopping the database, managing memory and storage, creating and managing database users, and so on.

The administrative account SYS is automatically created when a database is created. This account can perform all database administrative functions. The SYS schema stores the base tables and views for the data dictionary. These base tables and views are critical for the operation of Oracle Database. Tables in the SYS schema are manipulated only by the database and must never be modified by any user.

The SYSTEM account is also automatically created when a database is created. The SYSTEM schema stores additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to nonadministrative users.

Sample Schemas

An Oracle database may include sample schemas, which are a set of interlinked schemas that enable Oracle documentation and Oracle instructional materials to illustrate common database tasks. The hr schema is a sample schema that contains information about employees, departments and locations, work histories, and so on.

Figure 2-3 is an entity-relationship diagram of the tables in the hr schema. Most examples in this manual use objects from this schema.

Figure 2-3 HR Schema

Overview of Tables

A table is the basic unit of data organization in an Oracle database. A table describes an entity, which is something of significance about which information must be recorded. For example, an employee could be an entity.

Oracle Database tables fall into the following basic categories:

•Relational tables

Relational tables have simple columns and are the most common table type. Example 2-1 shows a CREATE TABLE statement for a relational table.

•Object tables

The columns correspond to the top-level attributes of an object type. See “Object Tables”.

You can create a relational table with the following organizational characteristics:

•A heap-organized table does not store rows in any particular order. The CREATE TABLE statement creates a heap-organized table by default.

•An index-organized table orders rows according to the primary key values. For some applications, index-organized tables enhance performance and use disk space more efficiently. See “Overview of Index-Organized Tables”.

•An external table is a read-only table whose metadata is stored in the database but whose data in stored outside the database. See “External Tables”.

A table is either permanent or temporary. A permanent table definition and data persist across sessions. A temporary table definition persists in the same way as a permanent table definition, but the data exists only for the duration of a transaction or session. Temporary tables are useful in applications where a result set must be held temporarily, perhaps because the result is constructed by running multiple operations.

This section contains the following topics:

•Columns and Rows

•Example: CREATE TABLE and ALTER TABLE Statements

•Oracle Data Types

•Integrity Constraints

•Object Tables

•Temporary Tables

•External Tables

•Table Storage

•Table Compression

Columns and Rows

A table definition includes a table name and set of columns. A column identifies an attribute of the entity described by the table. For example, the column employee_id in the employees table refers to the employee ID attribute of an employee entity.

In general, you give each column a column name, a data type, and a width when you create a table. For example, the data type for employee_id is NUMBER(6), indicating that this column can only contain numeric data up to 6 digits in width. The width can be predetermined by the data type, as with DATE.

A table can contain a virtual column, which unlike a nonvirtual column does not consume disk space. The database derives the values in a virtual column on demand by computing a set of user-specified expressions or functions. For example, the virtual column income could be a function of the salary and commission_pct columns.

After you create a table, you can insert, query, delete, and update rows using SQL. A row is a collection of column information corresponding to a record in a table. For example, a row in the employees table describes the attributes of a specific employee.

Example: CREATE TABLE and ALTER TABLE Statements

The Oracle SQL command to create a table is CREATE TABLE. Example 2-1 shows the CREATE TABLE statement for the employees table in the hr sample schema. The statement specifies columns such as employee_id, first_name, and so on, specifying a data type such as NUMBER or DATE for each column.

Example 2-1 CREATE TABLE employees

CREATE TABLE employees( employee_id    NUMBER(6), first_name     VARCHAR2(20), last_name      VARCHAR2(25)CONSTRAINT     emp_last_name_nn  NOT NULL, email          VARCHAR2(25)CONSTRAINT     emp_email_nn  NOT NULL, phone_number   VARCHAR2(20), hire_date      DATECONSTRAINT     emp_hire_date_nn  NOT NULL, job_id         VARCHAR2(10)CONSTRAINT     emp_job_nn  NOT NULL, salary         NUMBER(8,2), commission_pct NUMBER(2,2), manager_id     NUMBER(6), department_id  NUMBER(4), CONSTRAINT     emp_salary_minCHECK (salary > 0), CONSTRAINT     emp_email_ukUNIQUE (email)) ;

Example 2-2 shows an ALTER TABLE statement that adds integrity constraints to the employees table. Integrity constraints enforce business rules and prevent the entry of invalid information into tables.

Example 2-2 ALTER TABLE employees

ALTER TABLE employees
ADD ( CONSTRAINT     emp_emp_id_pkPRIMARY KEY (employee_id), CONSTRAINT     emp_dept_fkFOREIGN KEY (department_id)REFERENCES departments, CONSTRAINT     emp_job_fkFOREIGN KEY (job_id)REFERENCES jobs (job_id), CONSTRAINT     emp_manager_fkFOREIGN KEY (manager_id)REFERENCES employees) ;

Example 2-3 shows 8 rows and 6 columns of the hr.employees table.

Example 2-3 Rows in the employees Table

EMPLOYEE_ID FIRST_NAME  LAST_NAME      SALARY COMMISSION_PCT DEPARTMENT_ID
----------- ----------- ------------- ------- -------------- -------------100 Steven      King            24000                           90101 Neena       Kochhar         17000                           90102 Lex         De Haan         17000                           90103 Alexander   Hunold           9000                           60107 Diana       Lorentz          4200                           60149 Eleni       Zlotkey         10500             .2            80174 Ellen       Abel            11000             .3            80178 Kimberely   Grant            7000            .15

The output in Example 2-3 illustrates some of the following important characteristics of tables, columns, and rows:

•A row of the table describes the attributes of one employee: name, salary, department, and so on. For example, the first row in the output shows the record for the employee named Steven King.

•A column describes an attribute of the employee. In the example, the employee_id column is the primary key, which means that every employee is uniquely identified by employee ID. Any two employees are guaranteed not to have the same employee ID.

•A non-key column can contain rows with identical values. In the example, the salary value for employees 101 and 102 is the same: 17000.

•A foreign key column refers to a primary or unique key in the same table or a different table. In this example, the value of 90 in department_id corresponds to the department_id column of the departments table.

•A field is the intersection of a row and column. It can contain only one value. For example, the field for the department ID of employee 104 contains the value 60.

•A field can lack a value. In this case, the field is said to contain a null value. The value of the commission_pct column for employee 100 is null, whereas the value in the field for employee 149 is .2. A column allows nulls unless a NOT NULL or primary key integrity constraint has been defined on this column, in which case no row can be inserted without a value for this column.

Oracle Data Types

Each column has a data type, which is associated with a specific storage format, constraints, and valid range of values. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle Database to treat values of one data type differently from values of another. For example, you can multiply values of the NUMBER data type, but not values of the RAW data type.

When you create a table, you must specify a data type for each of its columns. Each value subsequently inserted in a column assumes the column data type.

Oracle Database provides several built-in data types. The most commonly used data types fall into the following categories:

•Character Data Types

•Numeric Data Types

•Datetime Data Types

•Rowid Data Types

•Format Models and Data Types

Other important categories of built-in types include raw, large objects (LOBs), and collections. PL/SQL has data types for constants and variables, which include BOOLEAN, reference types, composite types (records), and user-defined types.

Character Data Types

Character data types store character (alphanumeric) data in strings. The most commonly used character data type is VARCHAR2, which is the most efficient option for storing character data.

The byte values correspond to the character encoding scheme, generally called a character set or code page. The database character set is established at database creation. Examples of character sets are 7-bit ASCII, EBCDIC, and Unicode UTF-8.

The length semantics of character data types can be measured in bytes or characters. Byte semantics treat strings as a sequence of bytes. This is the default for character data types. Character semantics treat strings as a sequence of characters. A character is technically a code point of the database character set.

data type

VARCHAR2 and CHAR Data Types

The VARCHAR2 data type stores variable-length character literals. The terms literal and constant value are synonymous and refer to a fixed data value. For example, ‘LILA’, ‘St. George Island’, and ‘101’ are all character literals; 5001 is a numeric literal. Character literals are enclosed in single quotation marks so that the database can distinguish them from schema object names.

When you create a table with a VARCHAR2 column, you specify a maximum string length. In Example 2-1, the last_name column has a data type of VARCHAR2(25), which means that any name stored in the column can have a maximum of 25 bytes.

For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the maximum length, in which case the database returns an error. For example, in a single-byte character set, if you enter 10 characters for the last_name column value in a row, then the column in the row piece stores only 10 characters (10 bytes), not 25. Using VARCHAR2 reduces space consumption.

In contrast to VARCHAR2, CHAR stores fixed-length character strings. When you create a table with a CHAR column, the column requires a string length. The default is 1 byte. The database uses blanks to pad the value to the specified length.

Oracle Database compares VARCHAR2 values using nonpadded comparison semantics and compares CHAR values using blank-padded comparison semantics.

NCHAR and NVARCHAR2 Data Types

The NCHAR and NVARCHAR2 data types store Unicode character data. Unicode is a universal encoded character set that can store information in any language using a single character set. NCHAR stores fixed-length character strings that correspond to the national character set, whereas NVARCHAR2 stores variable length character strings.

You specify a national character set when creating a database. The character set of NCHAR and NVARCHAR2 data types must be either AL16UTF16 or UTF8. Both character sets use Unicode encoding.

When you create a table with an NCHAR or NVARCHAR2 column, the maximum size is always in character length semantics. Character length semantics is the default and only length semantics for NCHAR or NVARCHAR2.

Numeric Data Types

The Oracle Database numeric data types store fixed and floating-point numbers, zero, and infinity. Some numeric types also store values that are the undefined result of an operation, which is known as “not a number” or NAN.

Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent. The database uses up to 20 bytes to store the mantissa, which is the part of a floating-point number that contains its significant digits. Oracle Database does not store leading and trailing zeros.

NUMBER Data Type

The NUMBER data type stores fixed and floating-point numbers. The database can store numbers of virtually any magnitude. This data is guaranteed to be portable among different operating systems running Oracle Database. The NUMBER data type is recommended for most cases in which you must store numeric data.

You specify a fixed-point number in the form NUMBER(p,s), where p and s refer to the following characteristics:

•Precision

The precision specifies the total number of digits. If a precision is not specified, then the column stores the values exactly as provided by the application without any rounding.

•Scale

The scale specifies the number of digits from the decimal point to the least significant digit. Positive scale counts digits to the right of the decimal point up to and including the least significant digit. Negative scale counts digits to the left of the decimal point up to but not including the least significant digit. If you specify a precision without a scale, as in NUMBER(6), then the scale is 0.

In Example 2-1, the salary column is type NUMBER(8,2), so the precision is 8 and the scale is 2. Thus, the database stores a salary of 100,000 as 100000.00.

Floating-Point Numbers

Oracle Database provides two numeric data types exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. These types support all of the basic functionality provided by the NUMBER data type. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision, which enables faster arithmetic calculations and usually reduces storage requirements.

BINARY_FLOAT and BINARY_DOUBLE are approximate numeric data types. They store approximate representations of decimal values, rather than exact representations. For example, the value 0.1 cannot be exactly represented by either BINARY_DOUBLE or BINARY_FLOAT. They are frequently used for scientific computations. Their behavior is similar to the data types FLOAT and DOUBLE in Java and XMLSchema.

Datetime Data Types

The datetime data types are DATE and TIMESTAMP. Oracle Database provides comprehensive time zone support for time stamps.

DATE Data Type

The DATE data type stores date and time. Although datetimes can be represented in character or number data types, DATE has special associated properties. The hire_date column in Example 2-1 has a DATE data type.

The database stores dates internally as numbers. Dates are stored in fixed-length fields of 7 bytes each, corresponding to century, year, month, day, hour, minute, and second.

The database displays dates according to the specified format model. A format model is a character literal that describes the format of a datetime in a character string. The standard date format is DD-MON-RR, which displays dates in the form 01-JAN-11.

RR is similar to YY (the last two digits of the year), but the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Assume that in 1999 the database displays 01-JAN-11. If the date format uses RR, then 11 specifies 2011, whereas if the format uses YY, then 11 specifies 1911. You can change the default date format at both the instance and the session level.

Oracle Database stores time in 24-hour format—HH:MI:SS. If no time portion is entered, then by default the time in a date field is 00:00:00 A.M. In a time-only entry, the date portion defaults to the first day of the current month.

•Oracle Database SQL Language Reference for information about datetime format codes

TIMESTAMP Data Type

The TIMESTAMP data type is an extension of the DATE data type. It stores fractional seconds in addition to the information stored in the DATE data type. The TIMESTAMP data type is useful for storing precise time values, such as in applications that must track event order.

The DATETIME data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE are time-zone aware. When a user selects the data, the value is adjusted to the time zone of the user session. This data type is useful for collecting and evaluating date information across geographic regions.

Rowid Data Types

Every row stored in the database has an address. Oracle Database uses a ROWID data type to store the address (rowid) of every row in the database. Rowids fall into the following categories:

•Physical rowids store the addresses of rows in heap-organized tables, table clusters, and table and index partitions.

•Logical rowids store the addresses of rows in index-organized tables.

•Foreign rowids are identifiers in foreign tables, such as DB2 tables accessed through a gateway. They are not standard Oracle Database rowids.

A data type called the universal rowid, or UROWID, supports all kinds of rowids.

Use of Rowids

Oracle Database uses rowids internally for the construction of indexes. A B-tree index, which is the most common type, contains an ordered list of keys divided into ranges. Each key is associated with a rowid that points to the associated row’s address for fast access. End users and application developers can also use rowids for several important functions:

•Rowids are the fastest means of accessing particular rows.

•Rowids provide the ability to see how a table is organized.

•Rowids are unique identifiers for rows in a given table.

You can also create tables with columns defined using the ROWID data type. For example, you can define an exception table with a column of data type ROWID to store the rowids of rows that violate integrity constraints. Columns defined using the ROWID data type behave like other table columns: values can be updated, and so on.

ROWID Pseudocolumn

Every table in an Oracle database has a pseudocolumn named ROWID. A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a SQL function without arguments. Functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

Values of the ROWID pseudocolumn are strings representing the address of each row. These strings have the data type ROWID. This pseudocolumn is not evident when listing the structure of a table by executing SELECT or DESCRIBE, nor does the pseudocolumn consume space. However, the rowid of each row can be retrieved with a SQL query using the reserved word ROWID as a column name.

Example 2-4 queries the ROWID pseudocolumn to show the rowid of the row in the employees table for employee 100.

Example 2-4 ROWID Pseudocolumn

SQL> SELECT ROWID FROM employees WHERE employee_id = 100;ROWID
------------------
AAAPecAAFAAAABSAAA

Format Models and Data Types

A format model is a character literal that describes the format of datetime or numeric data stored in a character string. A format model does not change the internal representation of the value in the database.

When you convert a character string into a date or number, a format model determines how the database interprets the string. In SQL, you can use a format model as an argument of the TO_CHAR and TO_DATE functions to format a value to be returned from the database or to format a value to be stored in the database.

The following statement selects the salaries of the employees in Department 80 and uses the TO_CHAR function to convert these salaries into character values with the format specified by the number format model ‘$99,990.99’:

SQL> SELECT last_name employee, TO_CHAR(salary, '$99,990.99')2  FROM   employees3  WHERE  department_id = 80 AND last_name = 'Russell';EMPLOYEE                  TO_CHAR(SAL
------------------------- -----------
Russell                    $14,000.00

The following example updates a hire date using the TO_DATE function with the format mask ‘YYYY MM DD’ to convert the string ‘1998 05 20’ to a DATE value:

SQL> UPDATE employees2  SET hire_date = TO_DATE('1998 05 20','YYYY MM DD')3  WHERE last_name = 'Hunold';

Integrity Constraints

Integrity constraints are named rules that restrict the values for one or more columns in a table. These rules prevent invalid data entry into tables. Also, constraints can prevent the deletion of a table when certain dependencies exist.

If a constraint is enabled, then the database checks data as it is entered or updated. Data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform to the constraint can be allowed to enter the database.

In Example 2-1, the CREATE TABLE statement specifies NOT NULL constraints for the last_name, email, hire_date, and job_id columns. The constraint clauses identify the columns and the conditions of the constraint. These constraints ensure that the specified columns contain no null values. For example, an attempt to insert a new employee without a job ID generates an error.

You can create a constraint when or after you create a table. Constraints can be temporarily disabled if needed. The database stores constraints in the data dictionary.

Object Tables

An Oracle object type is a user-defined type with a name, attributes, and methods. Object types make it possible to model real-world entities such as customers and purchase orders as objects in the database.

An object type defines a logical structure, but does not create storage. Example 2-5 creates an object type named department_typ.

Example 2-5 Object Type

CREATE TYPE department_typ AS OBJECT( d_name     VARCHAR2(100),d_address  VARCHAR2(200) );
/

An object table is a special kind of table in which each row represents an object. The CREATE TABLE statement in Example 2-6 creates an object table named departments_obj_t of the object type department_typ. The attributes (columns) of this table are derived from the definition of the object type. The INSERT statement inserts a row into this table.

Example 2-6 Object Table

CREATE TABLE departments_obj_t OF department_typ;
INSERT INTO departments_obj_t VALUES ('hr', '10 Main St, Sometown, CA');

Like a relational column, an object table can contain rows of just one kind of thing, namely, object instances of the same declared type as the table. By default, every row object in an object table has an associated logical object identifier (OID) that uniquely identifies it in an object table. The OID column of an object table is a hidden column.

Temporary Tables

Oracle Database temporary tables hold data that exists only for the duration of a transaction or session. Data in a temporary table is private to the session, which means that each session can only see and modify its own data.

Temporary tables are useful in applications where a result set must be buffered. For example, a scheduling application enables college students to create optional semester course schedules. Each schedule is represented by a row in a temporary table. During the session, the schedule data is private. When the student decides on a schedule, the application moves the row for the chosen schedule to a permanent table. At the end of the session, the schedule data in the temporary data is automatically dropped.

Temporary Table Creation

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table. The ON COMMIT clause specifies whether the table data is transaction-specific (default) or session-specific.

Unlike temporary tables in some other relational databases, when you create a temporary table in an Oracle database, you create a static table definition. The temporary table is a persistent object described in the data dictionary, but appears empty until your session inserts data into the table. You create a temporary table for the database itself, not for every PL/SQL stored procedure.

Because temporary tables are statically defined, you can create indexes for them with the CREATE INDEX statement. Indexes created on temporary tables are also temporary. The data in the index has the same session or transaction scope as the data in the temporary table. You can also create a view or trigger on a temporary table.

Segment Allocation in Temporary Tables

Like permanent tables, temporary tables are defined in the data dictionary. Temporary segments are allocated when data is first inserted. Until data is loaded in a session the table appears empty. Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.

External Tables

An external table accesses data in external sources as if this data were in a table in the database. You can use SQL, PL/SQL, and Java to query the external data.

External tables are useful for querying flat files. For example, a SQL-based application may need to access records in a text file. The records are in the following form:

100,Steven,King,SKING,515.123.4567,17-JUN-03,AD_PRES,31944,150,90
101,Neena,Kochhar,NKOCHHAR,515.123.4568,21-SEP-05,AD_VP,17000,100,90 
102,Lex,De Haan,LDEHAAN,515.123.4569,13-JAN-01,AD_VP,17000,100,90

You could create an external table, copy the file to the location specified in the external table definition, and use SQL to query the records in the text file.

External tables are also valuable for performing ETL tasks common in data warehouse environments. For example, external tables enable the pipelining of the data loading phase with the transformation phase, eliminating the need to stage data inside the database in preparation for further processing inside the database. See “Overview of Data Warehousing and Business Intelligence”.

External Table Creation

Internally, creating an external table means creating metadata in the data dictionary. Unlike an ordinary table, an external table does not describe data stored in the database, nor does it describe how data is stored externally. Rather, external table metadata describes how the external table layer must present data to the database.

A CREATE TABLE … ORGANIZATION EXTERNAL statement has two parts. The external table definition describes the column types. This definition is like a view that enables SQL to query external data without loading it into the database. The second part of the statement maps the external data to the columns.

External tables are read-only unless created with CREATE TABLE AS SELECT with the ORACLE_DATAPUMP access driver. Restrictions for external tables include no support for indexed columns, virtual columns, and column objects.

External Table Access Drivers

An access driver is an API that interprets the external data for the database. The access driver runs inside the database, which uses the driver to read the data in the external table. The access driver and the external table layer are responsible for performing the transformations required on the data in the data file so that it matches the external table definition. Figure 2-4 represents how external data is accessed.

Figure 2-4 External Tables

Oracle provides the ORACLE_LOADER (default) and ORACLE_DATAPUMP access drivers for external tables. For both drivers, the external files are not Oracle data files.

ORACLE_LOADER enables read-only access to external files using SQL*Loader. You cannot create, update, or append to an external file using the ORACLE_LOADER driver.

The ORACLE_DATAPUMP driver enables you to unload external data. This operation involves reading data from the database and inserting the data into an external table, represented by one or more external files. After external files are created, the database cannot update or append data to them. The driver also enables you to load external data, which involves reading an external table and loading its data into a database.

Table Storage

Oracle Database uses a data segment in a tablespace to hold table data. As explained in “User Segments”, a segment contains extents made up of data blocks.

The data segment for a table (or cluster data segment, when dealing with a table cluster) is located in either the default tablespace of the table owner or in a tablespace named in the CREATE TABLE statement.

Table Organization

By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.

The hr.departments table is a heap-organized table. It has columns for department ID, name, manager ID, and location ID. As rows are inserted, the database stores them wherever they fit. A data block in the table segment might contain the unordered rows shown in Example 2-7.

50,Shipping,121,1500
120,Treasury,,1700
70,Public Relations,204,2700
30,Purchasing,114,1700
130,Corporate Tax,,1700
10,Administration,200,1700
110,Accounting,205,1700

The column order is the same for all rows in a table. The database usually stores columns in the order in which they were listed in the CREATE TABLE statement, but this order is not guaranteed. For example, if a table has a column of type LONG, then Oracle Database always stores this column last in the row. Also, if you add a new column to a table, then the new column becomes the last column stored.

A table can contain a virtual column, which unlike normal columns does not consume space on disk. The database derives the values in a virtual column on demand by computing a set of user-specified expressions or functions. You can index virtual columns, collect statistics on them, and create integrity constraints. Thus, virtual columns are much like nonvirtual columns.

Row Storage

The database stores rows in data blocks. Each row of a table containing data for less than 256 columns is contained in one or more row pieces.

If possible, Oracle Database stores each row as one row piece. However, if all of the row data cannot be inserted into a single data block, or if an update to an existing row causes the row to outgrow its data block, then the database stores the row using multiple row pieces (see “Data Block Format”).

Rows in a table cluster contain the same information as rows in nonclustered tables. Additionally, rows in a table cluster contain information that references the cluster key to which they belong.

Rowids of Row Pieces

A rowid is effectively a 10-byte physical address of a row. As explained in “Rowid Data Types”, every row in a heap-organized table has a rowid unique to this table that corresponds to the physical address of a row piece. For table clusters, rows in different tables that are in the same data block can have the same rowid.

Oracle Database uses rowids internally for the construction of indexes. For example, each key in a B-tree index is associated with a rowid that points to the address of the associated row for fast access (see “B-Tree Indexes”). Physical rowids provide the fastest possible access to a table row, enabling the database to retrieve a row in as little as a single I/O.

Storage of Null Values

A null is the absence of a value in a column. Nulls indicate missing, unknown, or inapplicable data.

Nulls are stored in the database if they fall between columns with data values. In these cases, they require 1 byte to store the length of the column (zero). Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, then no data is stored for these columns.
Table Compression

The database can use table compression to reduce the amount of storage required for the table. Compression saves disk space, reduces memory use in the database buffer cache, and in some cases speeds query execution. Table compression is transparent to database applications.

Basic and Advanced Row Compression

Dictionary-based table compression provides good compression ratios for heap-organized tables. Oracle Database supports the following types of dictionary-based table compression:

•Basic table compression

This type of compression is intended for bulk load operations. The database does not compress data modified using conventional DML. You must use direct path loads, ALTER TABLE . . . MOVE operations, or online table redefinition to achieve basic compression.

•Advanced row compression

This type of compression is intended for OLTP applications and compresses data manipulated by any SQL operation.

For basic and advanced row compression, the database stores compressed rows in row-major format. All columns of one row are stored together, followed by all columns of the next row, and so on (see Figure 12-7). Duplicate values are replaced with a short reference to a symbol table stored at the beginning of the block. Thus, information needed to re-create the uncompressed data is stored in the data block itself.

Compressed data blocks look much like normal data blocks. Most database features and functions that work on regular data blocks also work on compressed blocks.

You can declare compression at the tablespace, table, partition, or subpartition level. If specified at the tablespace level, then all tables created in the tablespace are compressed by default.

The following statement applies OLTP compression to the orders table:

ALTER TABLE oe.orders COMPRESS FOR OLTP;

The following example of a partial CREATE TABLE statement specifies OLTP compression for one partition and basic compression for the other partition:

CREATE TABLE sales (prod_id     NUMBER     NOT NULL,cust_id     NUMBER     NOT NULL, ... )PCTFREE 5 NOLOGGING NOCOMPRESSPARTITION BY RANGE (time_id)( partition sales_2010 VALUES LESS THAN(TO_DATE(...)) COMPRESS BASIC,partition sales_2011 VALUES LESS THAN (MAXVALUE) COMPRESS FOR OLTP );

Hybrid Columnar Compression

With Hybrid Columnar Compression, the database stores the same column for a group of rows together. The data block does not store data in row-major format, but uses a combination of both row and columnar methods.

Storing column data together, with the same data type and similar characteristics, dramatically increases the storage savings achieved from compression. The database compresses data manipulated by any SQL operation, although compression levels are higher for direct path loads. Database operations work transparently against compressed objects, so no application changes are required.

Types of Hybrid Columnar Compression

If your underlying storage supports Hybrid Columnar Compression, then you can specify the following compression types, depending on your requirements:

•Warehouse compression

This type of compression is optimized to save storage space, and is intended for data warehouse applications.

•Online archival compression

This type of compression is optimized for maximum compression levels, and is intended for historical data and data that does not change.

To achieve warehouse or online archival compression, you must use direct path loads, ALTER TABLE . . . MOVE operations, or online table redefinition.

Hybrid Columnar Compression is optimized for Data Warehousing and decision support applications on Exadata storage. Exadata maximizes the performance of queries on tables that are compressed using Hybrid Columnar Compression, taking advantage of the processing power, memory, and Infiniband network bandwidth that are integral to the Exadata storage server.

Other Oracle storage systems support Hybrid Columnar Compression, and deliver the same space savings as on Exadata storage, but do not deliver the same level of query performance. For these storage systems, Hybrid Columnar Compression is ideal for in-database archiving of older data that is infrequently accessed.

Compression Units

Hybrid Columnar Compression uses a logical construct called a compression unit to store a set of rows. When you load data into a table, the database stores groups of rows in columnar format, with the values for each column stored and compressed together. After the database has compressed the column data for a set of rows, the database fits the data into the compression unit.

For example, you apply Hybrid Columnar Compression to a daily_sales table. At the end of every day, you populate the table with items and the number sold, with the item ID and date forming a composite primary key. Table 2-1 shows a subset of the rows in daily_sales.

Table 2-1 Sample Table daily_sales

Assume that the rows in Table 2-1 are stored in one compression unit. Hybrid Columnar Compression stores the values for each column together, and then uses multiple algorithms to compress each column. The database chooses the algorithms based on a variety of factors, including the data type of the column, the cardinality of the actual values in the column, and the compression level chosen by the user.

As shown in Figure 2-5, each compression unit can span multiple data blocks. The values for a particular column may or may not span multiple blocks.

Figure 2-5 Compression Unit

Hybrid Columnar Compression has implications for row locking (see “Row Locks (TX)”). When an update occurs for a row in an uncompressed data block, only the updated row is locked. In contrast, the database must lock all rows in the compression unit if an update is made to any row in the unit. Updates to rows using Hybrid Columnar Compression cause rowids to change.
Overview of Table Clusters

A table cluster is a group of tables that share common columns and store related data in the same blocks. When tables are clustered, a single data block can contain rows from multiple tables. For example, a block can store rows from both the employees and departments tables rather than from only a single table.

The cluster key is the column or columns that the clustered tables have in common. For example, the employees and departments tables share the department_id column. You specify the cluster key when creating the table cluster and when creating every table added to the table cluster.

The cluster key value is the value of the cluster key columns for a particular set of rows. All data that contains the same cluster key value, such as department_id=20, is physically stored together. Each cluster key value is stored only once in the cluster and the cluster index, no matter how many rows of different tables contain the value.

For an analogy, suppose an HR manager has two book cases: one with boxes of employees folders and the other with boxes of departments folders. Users often ask for the folders for all employees in a particular department. To make retrieval easier, the manager rearranges all the boxes in a single book case. She divides the boxes by department ID. Thus, all folders for employees in department 20 and the folder for department 20 itself are in one box; the folders for employees in department 100 and the folder for department 100 are in a different box, and so on.

You can consider clustering tables when they are primarily queried (but not modified) and records from the tables are frequently queried together or joined. Because table clusters store related rows of different tables in the same data blocks, properly used table clusters offer the following benefits over nonclustered tables:

•Disk I/O is reduced for joins of clustered tables.

•Access time improves for joins of clustered tables.

•Less storage is required to store related table and index data because the cluster key value is not stored repeatedly for each row.

Typically, clustering tables is not appropriate in the following situations:

•The tables are frequently updated.

•The tables frequently require a full table scan.

•The tables require truncating.

Overview of Indexed Clusters

An indexed cluster is a table cluster that uses an index to locate data. The cluster index is a B-tree index on the cluster key. A cluster index must be created before any rows can be inserted into clustered tables.

Assume that you create the cluster employees_departments_cluster with the cluster key department_id, as shown in Example 2-8. Because the HASHKEYS clause is not specified, this cluster is an indexed cluster. Afterward, you create an index named idx_emp_dept_cluster on this cluster key.

Example 2-8 Indexed Cluster

CREATE CLUSTER employees_departments_cluster(department_id NUMBER(4))
SIZE 512;CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;

You then create the employees and departments tables in the cluster, specifying the department_id column as the cluster key, as follows (the ellipses mark the place where the column specification goes):

CREATE TABLE employees ( ... )CLUSTER employees_departments_cluster (department_id);CREATE TABLE departments ( ... )CLUSTER employees_departments_cluster (department_id);

Finally, you add rows to the employees and departments tables. The database physically stores all rows for each department from the employees and departments tables in the same data blocks. The database stores the rows in a heap and locates them with the index.

Figure 2-6 shows the employees_departments_cluster table cluster, which contains employees and departments. The database stores rows for employees in department 20 together, department 110 together, and so on. If the tables are not clustered, then the database does not ensure that the related rows are stored together.

The B-tree cluster index associates the cluster key value with the database block address (DBA) of the block containing the data. For example, the index entry for key 20 shows the address of the block that contains data for employees in department 20:

Overview of Hash Clusters

A hash cluster is like an indexed cluster, except the index key is replaced with a hash function. No separate cluster index exists. In a hash cluster, the data is the index.

With an indexed table or indexed cluster, Oracle Database locates table rows using key values stored in a separate index. To find or store a row in an indexed table or table cluster, the database must perform at least two I/Os:

•One or more I/Os to find or store the key value in the index

•Another I/O to read or write the row in the table or table cluster

To find or store a row in a hash cluster, Oracle Database applies the hash function to the cluster key value of the row. The resulting hash value corresponds to a data block in the cluster, which the database reads or writes on behalf of the issued statement.

Hashing is an optional way of storing table data to improve the performance of data retrieval. Hash clusters may be beneficial when the following conditions are met:

•A table is queried much more often than modified.

•The hash key column is queried frequently with equality conditions, for example, WHERE department_id=20. For such queries, the cluster key value is hashed. The hash key value points directly to the disk area that stores the rows.

•You can reasonably guess the number of hash keys and the size of the data stored with each key value.

Hash Cluster Creation

The cluster key, like the key of an indexed cluster, is a single column or composite key shared by the tables in the cluster. The hash key values are actual or possible values inserted into the cluster key column. For example, if the cluster key is department_id, then hash key values could be 10, 20, 30, and so on.

Oracle Database uses a hash function that accepts an infinite number of hash key values as input and sorts them into a finite number of buckets. Each bucket has a unique numeric ID known as a hash value. Each hash value maps to the database block address for the block that stores the rows corresponding to the hash key value (department 10, 20, 30, and so on).

To create a hash cluster, you use the same CREATE CLUSTER statement as for an indexed cluster, with the addition of a hash key. The number of hash values for the cluster depends on the hash key. In Example 2-9, the number of departments that are likely to exist is 100, so HASHKEYS is set to 100.

Example 2-9 Hash Cluster

CREATE CLUSTER employees_departments_cluster(department_id NUMBER(4))
SIZE 8192 HASHKEYS 100;

After you create employees_departments_cluster, you can create the employees and departments tables in the cluster. You can then load data into the hash cluster just as in the indexed cluster described in Example 2-8.
Hash Cluster Queries

The database, not the user, determines how to hash the key values input by the user. For example, assume that users frequently execute queries such as the following, entering different department ID numbers for p_id:

SELECT *
FROM   employees
WHERE  department_id = :p_id;SELECT * 
FROM   departments 
WHERE  department_id = :p_id;SELECT * 
FROM   employees e, departments d 
WHERE  e.department_id = d.department_id
AND    d.department_id = :p_id;

If a user queries employees in department_id=20, then the database might hash this value to bucket 77. If a user queries employees in department_id=10, then the database might hash this value to bucket 15. The database uses the internally generated hash value to locate the block that contains the employee rows for the requested department.

Figure 2-7 depicts a hash cluster segment as a horizontal row of blocks. As shown in the graphic, a query can retrieve data in a single I/O.

Figure 2-7 Retrieving Data from a Hash Cluster

A limitation of hash clusters is the unavailability of range scans on nonindexed cluster keys (see “Index Range Scan”). Assume that no separate index exists for the hash cluster created in Example 2-9. A query for departments with IDs between 20 and 100 cannot use the hashing algorithm because it cannot hash every possible value between 20 and 100. Because no index exists, the database must perform a full scan.

Hash Cluster Variations

A single-table hash cluster is an optimized version of a hash cluster that supports only one table at a time. A one-to-one mapping exists between hash keys and rows. A single-table hash cluster can be beneficial when users require rapid access to a table by primary key. For example, users often look up an employee record in the employees table by employee_id.

A sorted hash cluster stores the rows corresponding to each value of the hash function in such a way that the database can efficiently return them in sorted order. The database performs the optimized sort internally. For applications that always consume data in sorted order, this technique can mean faster retrieval of data. For example, an application might always sort on the order_date column of the orders table.

Hash Cluster Storage

Oracle Database allocates space for a hash cluster differently from an indexed cluster. In Example 2-9, HASHKEYS specifies the number of departments likely to exist, whereas SIZE specifies the size of the data associated with each department. The database computes a storage space value based on the following formula:

HASHKEYS * SIZE / database_block_size

Thus, if the block size is 4096 bytes in Example 2-9, then the database allocates at least 200 blocks to the hash cluster.

Oracle Database does not limit the number of hash key values that you can insert into the cluster. For example, even though HASHKEYS is 100, nothing prevents you from inserting 200 unique departments in the departments table. However, the efficiency of the hash cluster retrieval diminishes when the number of hash values exceeds the number of hash keys.

To illustrate the retrieval issues, assume that block 100 in Figure 2-7 is completely full with rows for department 20. A user inserts a new department with department_id 43 into the departments table. The number of departments exceeds the HASHKEYS value, so the database hashes department_id 43 to hash value 77, which is the same hash value used for department_id 20. Hashing multiple input values to the same output value is called a hash collision.

When users insert rows into the cluster for department 43, the database cannot store these rows in block 100, which is full. The database links block 100 to a new overflow block, say block 200, and stores the inserted rows in the new block. Both block 100 and 200 are now eligible to store data for either department. As shown in Figure 2-8, a query of either department 20 or 43 now requires two I/Os to retrieve the data: block 100 and its associated block 200. You can solve this problem by re-creating the cluster with a different HASHKEYS value.

3 Indexes and Index-Organized Tables
索引和索引组织表

4 Partitions, Views, and Other Schema Objects
分区、视图和其他架构对象

5 Data Integrity
数据完整性

6 Data Dictionary and Dynamic Performance Views
数据字典和动态性能视图

Part II Oracle Data Access
7 SQL
8 Server-Side Programming: PL/SQL and Java
服务器端编程：PL/SQL和Java

Part III Oracle Transaction Management
9 Data Concurrency and Consistency
数据并发性和一致性

10 Transactions
事务

Part IV Oracle Database Storage Structures
11 Physical Storage Structures
物理存储结构

12 Logical Storage Structures
逻辑存储结构

Part V Oracle Instance Architecture
13 Oracle Database Instance
14 Memory Architecture
15 Process Architecture
16 Application and Networking Architecture

Part VI Oracle Database Administration and Development
17 Topics for Database Administrators and Developers
18 Concepts for Database Administrators
19 Concepts for Database Developers