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
数据也不需要重新创建索引和约束。
删除字段将需要大量的时间,因为这个字段的所有数据要从表中删除。
Oracle 8i
前,不能从表中删除字段。
Using a Checkpoint When Dropping a Column
在删除字段前使用检查点
删除字段需要考虑时间同时需要大量的重做空间。当从大表中删除字段时,检查点可以减少重做空间的使用。在下述例子中,每
1000
行将出现一个检查点。表置于
INVALID
状态直到操作完成。如果在操作过程中实例失败,启动后标保持
INVALID
状态,操作必须完成。使用下面的语句可以继续一个中断的删除操作:
ALTER TABLE hr.employees
DROP COLUMNS CONTINUE;
使用这个语句将产生一个错误如果表在
VALID
状态。
Using the UNUSED Option
使用
UNUSED
参数
使一个字段
UNUSED
ALTER TABLE hr.employees
SET UNUSED COLUMN comments CASCADE CONSTRAINTS;
删除
UNUSED
字段
ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;
继续删除字段操作
ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;
Marking a Column as Unused
将一个字段置于
UNUSED
替代从表中移除一个字段,字段可以置于
UNUSED
并在稍后删除。这样相对要快速,因为这样不需要改变磁盘空间数据并没有删除。字段标记为
UNUSED
可以在以后表活动不太频繁的时候删除。
UNUSED
字段表现为不是表的一部份。查询看不到
UNUSED
字段中的数据。甚至,在使用
DESCRIBE
命令时也看不到字段的名字和数据类型。用户可以增加和
UNUSED
字段名字一样的字段。
把字段设置为
UNUSED
再删除的一个例子是你想删除一个表中的
2
个字段。如果删除
2
个字段,表中的所有行将更新
2
次,通过设置字段为
UNUSED
然后删除字段数据行只需要更新一次。
Identifying Tables with Unused Columns
标明有
UNUSED
字段的表
你可以查询视图
dba_unused_col_tabs
。它可以获取有
UNUSED
字段的表名和表中标明为
UNUSED
的字段数目。例如:
SELECT * FROM dba_unused_col_tabs;
OWNER TABLE_NAME COUNT
----- -------------- ------
HR EMPLOYEES 1
标明表部分完成删除字段操作可以通过查询
dba_partial_drop_tabs
。
SELECT * FROM dba_partial_drop_tabs;
OWNER TABLE_NAME COUNT
----- -------------- ------
no rows selected
Restrictions on Dropping a Column
删除字段的限制
你不可以做以下的事情:
从对象类型的表中删除字段
从嵌套表中删除字段
删除一个表中的所有字段
删除分区键值字段
删除
SYS
所有表中的字段
删除一个父键字段
删除一个
IOT
表中的主键字段
LONG
或者
LONG RAW
字段设置为
UNUSED
但是没有删除的情况下不可以增加
LONG
或者
LONG RAW
字段。(尽管
DESCIRBE
这个表并没有看到
LONG
或者
LONG RAW
字段)。
Obtaining Table Information
获取表的信息
通过查询数据字典
DBA_TABLES
DBA_OBJECTS
SELECT table_name FROM dba_tables WHERE owner = 'HR';
TABLE_NAME
-------------------
COUNTRIES
DEPARTMENTS
DEPARTMENTS_HIST
EMPLOYEES
EMPLOYEES_HIST
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
SQL> select object_name, created
2 from DBA_OBJECTS
3 where object_name like 'EMPLOYEES'
4 and owner = 'HR';
OBJECT_NAME CREATED
----------- ---------
EMPLOYEES 16-APR-01