oracle操作语言与解释

1Z031第十一章管理表

Managing Tables

管理表

学习目标

标明存储数据的不同方式

了解Oracle数据类型

分别扩展和限制ROWID类别

描述行的结构

创建普通和临时表

在表中管理存储结构

重新组织，截断，删除表

删除表中的字段

 

Storing User Data

存储用户数据

Using Different Methods for Storing User Data

用不同的方法存储用户数据

普通表

分区表

索引组织的表

群集表

Regular Table

普通表

普通的表是数据库中最常见的存储用户数据的方式。数据库管理员对非群集表中行的分布的控制能力很有限。行在表中可以以任意的顺序存储依赖于表的活动状态。

Partitioned Table

分区表

分区表用于创建可扩展的程序，它有下述特征：

       一个分区表有一个或多个分区，每个分区存储用范围分区法、散列分区法、联合分区法或者列表分区法分区的一部份行。

       分区表中的每个分区是一个段可以位于不同的表空间中。

       分区对大的表的查询和操作有效的降低并发。

       可以用特殊的命令在一个表中管理分区。

Index-Organized Table

索引组织的表

一个索引组织的表和堆表类似在一个或者多个字段上有一个主键索引。但是，不同于操作2个独立的存储空间表和B数索引，索引组织的表操作单一的B数包括表的主键和其他的字段。当PCTTHRESHOLD参数设定后长的行将需要一个溢出(overflow)段存储。

索引组织的表提供快速的基于键值的查询包括精确的查询和范围查询。

同时，存储空间的需求降低因为键字段在表和索引中不再重复。其余的非键字段存储在索引中除非索引实体变得太大；在这种状况下，Oracle提供一个OVERFLOW子句来解决这个问题。

Clustered Table

群集表

群集表提供优化方法存储表数据。一个群集是由一个表或者一组表共享相同的数据块，这些数据块组织在一起因为他们共享一些通常在一起使用的字段。

群集表有下述特征：

       群集表有群集键用于标示行需要存储在一起。

       群集键由一个或者多个字段组成。
       群集中的表有字段与群集键符合。

       群集机制对于使用表的程序是透明的。在群集表中的数据可以看作是存储在普通表中一样。

       更新群集键中的字段的值将导致全部物理地重新定位行。

       群集键和主键完全不依赖。在群集中的表可以有一个主键，这个主键既可以是群集键也可以是其它字段的组合。

       群集通常创建用于提高性能。随机地访问群集数据将更快，但是全表扫描通常会慢一些。

       群集重整表的物理存储，对逻辑结构没有影响。

Oracle Data Types

Oracle 数据类型

Oracle Built-in Data Types

Oracle 内建数据类型

Oracle内建集中数据类型来存放纯量数据，集合，和关系。

Scalar Data Types

纯量数据类型

Character Data

字符数据

定长和变长2种，定长有CHAR和NCHAR，以补齐空格存放。NCHAR是国际化支持数据类型，可以存放定宽或者变宽字符系列。最大的大小取决于存储一个字符所需要的BYTE数，一行最大限制为2000 byte。默认为一个字符1 byte，依赖于字符集。

变长字符数据类型只需要实际存放数据的byte数，每行的大小可以不一样，最大4000 byte。VARCHAR2和NVARCHAR2是变长字符数据类型。

Numeric Data

数字数据

数字在ORACLE中始终是变长类型数据。最大可达38有效位。数字类型需要：

       的指数位一个byte

       尾数每2个有效位一个byte

       一个byte留给负数如果这个数的有效位小于38 byte。

DATE Data Type

日期数据类型

Oracle 服务器用7个byte定长字段存储日期型数据。Oracle date类型的数据始终包括时间。

TIMESTAMP Data Type

时间戳数据类型

这种数据类型存储日期和时间包括辅助的秒到9位小数位。TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE 可以使用时区作为系数来标定夏令时间。TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE 可以用于主键，而TIMESTAMP WITH TIME ZONE 不可以。

 

RAW Data Type

原始数据类型

这种数据类型容许存储小的二进制数据。Oracle 服务器不进行字符集的转换档在RAW 数据通过网络从不同的机器交换或者通过Oracle 使用工具从一个数据库移到另外一个数据库。它们需要的byte数是实际字段的值，并且每行的大小可以不同，最大2000 byte。

Long, Long Raw and Large Object (LOBs) Data Types

长，长原始数据和大对象数据类型

Oracle 提供6种数据类型来存储（LOBs）大对象数据：

       CLOB和LONG用于大的定宽字符数据。

       NCLOG用于大定宽国际字符集数据

       BLOB 和LONG ROW 用于存放非结构化数据

       BFILE 用于在操作系统中存放费结构化数据

LONG 和 LONG RAW 数据类型经常用于非结构化数据。例如二进制的图片，文档，或者地理信息，主要是用于向后兼容。这些数据类型被LOB数据类型取代。LOB类型和LONG，LONG RAW 类型有明显区别，并且他们不可以相互转换。LOB不支持LONG编程接口（API），反过来也一样。

LONG，LONG RAW                      LOB

每个表只能有一个LONG字段              每个表可以有多个LOB字段

最大2 GB                               最大4 GB

数据in-line 存储                         数据in-line 或者 out-of-line存储

没有对象类型支持                        支持对象类型

顺序访问数据片                          随机访问数据片

 

LOB在表中和数据上存储一个指示器，除非在数据的大小小于VARCHAR2数据类型的最大尺寸4000 byte的时候，LONG 把所有的数据内线存放。LOB容许数据存放在独立的段和表空间中，或者在一个主机的文件中。

除NCLOB以外，LOB 支持对象类型和复制；LONG类型不行。

LONG 类型主要是以连接的行片段存储数据，一个行片段在一个数据块中指向下一个行片段存放在另一个数据块中。因此，它们需要顺序的访问，与之对应，LOB 支持随机的片段式通过文件类型的接口访问数据。

 

ROWID and UROWID Data Type

ROWID 和 UROWID 数据类型

ROWID是一种数据类型，可以和其他的字段一起被查询。它有下述特征：

       ROWID 是数据块中每行的唯一标示。

       ROWID 不会直接以一个字段值存储。

虽然ROWID不直接给出行的物理地址，但它可以用于定位行。

ROWID提供最快的访问表中的行的方式。

ROWID 存储在索引中标示给定系列键值的行。

在8.1版，Oracle 服务器提供一个单独的数据类型叫做全局ROWID或者UROWID。它为外部的表提供ROWID并且可以存储所有类型的ROWID。比如：一个UROWID数据类型需要用来存储一个IOT表中行的ROWID。使用UROWID必须把参数COMPATIBLE设置为8.1或者更高。

 

Collections Data Types

集合数据类型

2种类型的集合数据类型可以用于存储数据作为一个表中给定行的复制。Oracle8i前，对象属性需要定义并且使用集合。简要地讨论见下面。

Varying Arrays (VARRAY)

可变数组

可变数组用于存放包含少量因素的列表，例如客户电话号码。

VARRAY 有下述特征：

       一个数组就是一个排序的数据元素的集合。

       所有给定数组的元素都是相同的数据类型。

       每个元素有一个索引，一个对应于元素在数组中位置的数字。

       Oracle 服务器容许数组为可变尺寸的，这也是为什么它们叫做VARRAY，但是最大的大小必须标明当申明数组类型。

       Nested Tables

       嵌套表

嵌套表提供在表中定义一个表作为字段的方法。他们可以用于存放一个有大量记录的数据集合例如订单中的Item

嵌套表有下述特征：

       嵌套表是一个非排序的行的记录集合。

       嵌套表中的行有同样的结构。

       嵌套表中的行与父级表分开存储，并且有一个指针指向父级表中的相应行。

       嵌套表的存储特征可以由数据块管理员定义。

       嵌套表没有预定义的最大大小。

Relationship Data Types (REFs)

关系数据类型（REFs）

关系数据类型用于数据块中的指针。使用这些类型需要对象属性。作为一个例子，每个订购的ITEM应该指向或者引用PRODUCTS表中的一行，而不是必须存储产品代码。

Oracle User-Defined Data Types

Oracle 用户定义的数据类型

Oracle 容许用户定义一个抽象的数据类型并且用于他们的程序中。

 

ROWID Format

ROWID 格式

 

Extended ROWID Format

扩展ROWID格式

OOOOOO     FFF         BBBBBB      RRR

数据对象号       关联文件号     数据块号          行号

 

Restricted ROWID Format

限制ROWID格式

BBBBBBBB .   RRRR . FFFF

数据块号      .   行号   .  文件号

 

ROWID Format

ROWID 格式

一个扩展ROWID在硬盘上需要10 byte存储空间并且用18个字符来显示。它包含下述组件：

       Data object number

       数据对象号分配给每个数据对象，比如创建表表或者索引时，并且在数据库内唯一。

       Relative file number

       关联文件号在一个表空间内唯一。

       Block number

       数据块号代表在数据文件内包含行的数据块的位置。

       Row number

       数据行号标示数据块头中行直接接口。

内部，数据对象号需要32 bit，管理文件号需要10 bit，数据块号需要22 bit，行号需要16 bit，加起来一共80 bit或则10 byte。

       一个扩展ROWID是用64位编码结构来表现，数据对象号6 位，关联文件号3位，数据块号6位，3位行号。64位编码结构用符号“A-Z”，“a-z”，“0-9”“+”，和“/”总共64位字符，如同下面的例子：

 

 

SQL> SELECT department_id, rowid FROM hr.departments;

DEPARTMENT_ID ROWID

------------- ------------------

10 AAABQMAAFAAAAA6AAA

20 AAABQMAAFAAAAA6AAB

30 AAABQMAAFAAAAA6AAC

40 AAABQMAAFAAAAA6AAD

50 AAABQMAAFAAAAA6AAE

60 AAABQMAAFAAAAA6AAF

 

在这个例子中

       AAABQM 是数据对象号

       AAF 是关联文件号

       AAAAA6 是数据块号

       AAA 是department为10的数据行的行号

Restricted ROWID in Oracle7 and Earlier

Oracle 7 和以前版本中的限制ROWID

Oracle 8以前版本用限制ROWID格式。一个限制ROWID只用6个byte内存并且不包含数据对象号。Oracle 7 或者以前的版本使用这种ROWID因为在一个数据库中文件号是唯一的，因此，早期版本不容许超过1022个数据文件。现在这个是对表空间的限制。

虽然Oracle 8 取消了这个限制加上了表空间关联文件号，但是限制ROWID仍然用于非分区表上的非分区索引，所有的索引实体和它们指向行在一个段中。

 

Locating a Row Using ROWID

用ROWID来定位行

因为一个段只能在一个表空间中，使用数据对象号，Oracle 服务器可以判定包含相应行的表空间。

表空间中的关联文件号用于定位文件，数据块号用于定位包含行的数据块，行号用于定位相应行的行目录入口。

行目录入口可以用于定位行的开始。

因此，ROWID 可以用来定位一个表空间中的任何行。

 

Structure of a Row

行的结构

行的数据以变长记录形式存储在数据库数据块中。行的字段通常以它们定义的顺序存储任何结尾的NULL 字段都不会存储。注意：每个非NULL字段有一个byte用于字段的长度。表中的每个行可能有不同树立的字段。每个行在表中有：

       一个行头部：用于存储行中的字段数，链接信息，和行锁定状态。

       行数据：对每个字段，Oracle 存储字段长度和值（如果一个字段没有超过250 byte需要一个 byte 存放字段长度，更长的字段需要3个长度 byte。字段值紧跟字段长度byte后存放。）

       相邻的行之间不需要任何的空间。数据块中的每个行在行目录中都有一个插口。目录中的插口指向行的起始点。

      

Creating a Table

创建一个表

CREATE TABLE hr.employees(

employee_id NUMBER(6),

first_name VARCHAR2(20),

last_name VARCHAR2(25)

email VARCHAR2(25)

phone_number VARCHAR2(20)

hire_date DATE DEFAULT SYSDATE

job_id VARCHAR2(10)

salary NUMBER(8,2)

commission_pct NUMBER (2,2)

manager_id NUMBER(6)

department_id NUMBER(4)

);

 

CREATE TABLE 命令用于创建关系表或者对象表。

关系表：基本的存储用户数据的结构。

对象表：用对象类型定义字段的表。

本章中不详细讲解对象表。

Creating Table Guidelines

创建表方针

       把表放置到表空间

       使用本地管理的表减少页分裂。

CREATE TABLE 命令详细定义间 Oracle9i SQL Reference。在自己的schema中创建表需要有create table的权限，在别人的schema中创建表需要有create any table的权限。

 

CREATE TABLE hr.departments(

department_id NUMBER(4),

department_name VARCHAR2(30),

manager_id NUMBER(6)

location_id NUMBER(4))

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)

TABLESPACE data;

STORAGE Clause

存储子句

存储子句标明表的存储特征。第一个扩展分配的空间是200K，当需要第二个扩展时空间也是200K。第三个扩展也是200K，因为PCTINCREASE设置为0。最大的扩展数量是5，最小的数量是1。

       MINEXTENTS:最小的扩展的数量。

   MAXEXTENTS:最大的扩展数量。

    PCTINCREASE:在NEXT扩展后的扩展大小增加的百分比。

块参数也可以在创建表的物理特征子句中标明。

   • PCTFREE:标明表中的每个块的空间百分比。PCTFREE的值必须从0到99。0表明整个数据块都可以用于插入新数据。默认的值是10。

   • PCTUSED:标明表中每个数据块最小的可以使用的空间百分比。数据块已经使用了的空间低于PCTUSED时成为候选的可以插入行的块。默认为40。PCTFREE和PCTUSED决定新的行是插入已经存在的数据块中还是新的数据块中。2个参数加起来应该等于或者小于100。这2个参数可以用来使表在使用空间上更有效。

• INITRANS:标明每个数据块中事务入口的数量。值从1到255默认的是1，这个参数保证最少的并发事务可以更新数据块。通常这个值不要改变。

• MAXTRANS:最大的并发更新数据块事务数。这个限制不会应用在查询上。值从1到255默认的值是数据块大小的函数值。

TABLESPACE Clause

表空间子句标明表应该在哪个表空间中创建。如果你不写表空间，Oracle把对象创建到所有者默认的表空间。

 

Creating Temporary Tables

创建临时表

用 GLOBAL TEMPORARY字句创建

CREATE GLOBAL TEMPORARY TABLE

hr.employees_temp

AS SELECT * FROM hr.employees;

 

表只在事务或者SESSION期间保留数据

数据上不需要DML锁

DML操作不产生重做日志

可以在临时表上创建索引，视图，触发器

Temporary Tables

临时表

临时表可以创建用来保留只在事务或者线程运行期间线程私有的数据。CREATE GLOBAL TEMPORARY TABLE 命令创建的临时表可以是事物专有或者线程专有。对于事物专有的临时表，数据只在事务运行期间存在。同样对于线程专有临时表，数据只在线程运行期间存在。每个线程只能看到和修改自己的数据。DML锁对于临时表数据来说是不需要的。可以控制行的生命周期的子句是：

       ON COMMIT DELETE ROWS 标明行只能在事务内可见。

   ON COMMIT PRESERVE ROWS 标明行在整个线程期间可见。

你可以在临时表上创建索引，视图，和触发器并且你也可以用EXPORT和IMPORT工具来导入导出临时表的定义。但是没有数据可以导出，就算你使用ROWS选项。临时表的定义对于所有的线程均可见。

 

 

 

 

Changing Storage Parameters

改变存储参数

 

ALTER TABLE hr.employees

PCTFREE 30

PCTUSED 50

STORAGE(NEXT 500K

MINEXTENTS 2

MAXEXTENTS 100);

 

 

Syntax

ALTER TABLE [schema.]table

{[ storage-clause ]

[ INITRANS integer ]

[ MAXTRANS integer]}

一些存储参数可以通过ALTER TABLE命令来修改：

       • NEXT:当Oracle分配新的扩展的时候，新的值将被使用。下面的扩展的大小将增加PCTINCREASE。

• PCTINCREASE:对PCTINCREASE的修改将在数据字典中注册。它将用于重新计算NEXT当下一个扩展分配的时候。比如当一个表有2个扩展next = 10K PCTINCREASE = 0 。如果PCTINCREASE 改变为100，第三个扩展将还是10K，第四个扩展将是20K，第5个将是40K如此类推。

• MINEXTENTS: 最少扩展的值可以更改为比当前表中扩展数小或者相等的任何值。这并不会立即影响表，但是会在表截断的时候起作用。

• MAXEXTENTS: 最大扩展数的值可以设为等于或者大于当前表中扩展的任何值，也可以设置为UNLIMITED。

   限制：

   一个表的INITIAL的值不可以修改。

   NEXT 的值将取整为数据块大小的倍数大于或者等于标明值。

 

 

Manually Allocating Extents

手工分配扩展

ALTER TABLE hr.employees

ALLOCATE EXTENT(SIZE 500K

DATAFILE ‘/DISK3/DATA01.DBF’);

 

Manually Allocating Extents

手工分配扩展

控制表的扩展在文件之间的分布

在批量装载数据的时候消除表的动态扩张。

Syntax

ALTER TABLE [schema.]table

ALLOCATE EXTENT [ ([SIZE integer [K|M]]

[ DATAFILE ‘filename’ ]) ]

 

如果SIZE不写，Oracle使用DBA_TABLES 中的NEXT_EXTENT的大小来分配扩展。文件子句中的文件必须属于表所在表空间。不然，这个语句将产生错误。如果DATAFILE子句不写，Oracle将在包含表的一个文件中分配扩展。

注意：DBA_TABLES中的NEXT_EXTENT参数将不会影响手工分配的扩展。运行这个命令的时候，Oracle服务器不会重新计算下一个EXTENT的大小。

 

Nonpartitioned Table Reorganization

非分区表重新组织

ALTER TABLE hr.employees

MOVE TABLESPACE data1;

移动数据到新的段并且保存表中的索引，约束，权限，等。

用于在不同的表空间中移动表或者重新组织扩展。

 

Relocating or Reorganizing a Table

重新放置或者重新组织表

一个非分区表可以不使用EXPORT和IMPORT来移动。甚至可以容许改变存储参数。这在下述情况小有用：

       将表从一个表空间移动到另外一个

       重新组织表防止行的分裂

移动表后，你必须重建索引以防止下述错误：

SQL> select * from employee where id=23;

select * from employee where id=23

*

ERROR at line 1:

ORA-01502: index 'SUMMIT.EMPLOYEE_ID_PK' or partition of such

index is in unusable state

 

Truncating a Table

截断表

截断表将删除所有的表中的行，并且释放使用了的空间。

       相关的索引也将截断

 

Syntax

TRUNCATE TABLE [schema.] table

[{DROP | REUSE} STORAGE]

这个命令的影响如下：

       所有在表中的行将删除

       没有UNDO数据产生并且这个命令自动提交因为TRUNCATE TABLE是DDL命令。

       相关的索引同时截断。

       表有外键参照的不能截断。

       删除的触发器在整个命令运行时不起作用。

 

Dropping a Table

删除表

DROP TABLE hr.department

CASCADE CONSTRAINTS;

 

Syntax

Use the following command to drop a table:

DROP TABLE [schema.] table

[CASCADE CONSTRAINTS]

删除表的时候表使用的扩展同时释放掉。CASCADE CONSTRAINTS参数在表作为父表被外键参照的时候删除表必需使用这个选项。

 

Dropping a Column

删除行

ALTER TABLE hr.employees

DROP COLUMN comments

CASCADE CONSTRAINTS CHECKPOINT 1000;

 

在每行中删除字段的长度和数据，在数据块中释放空间。

在一个大表中删除行将需要较长的时间。

删除字段清除未使用的和潜在的空间需求不需要EXPORT 或者IMPORT数据也不需要重新创建索引和约束。

删除字段将需要大量的时间，因为这个字段的所有