Expert
One-on-One Oracle阅读笔记
第 6
章 数据库表
6.1 表的类型
1. 堆组织表
2. 索引组织表
3. 聚簇表
4. 散列聚簇表
5. 嵌套表
6. 临时表
7. 对象表
一张表最多有1000列；表的行数理论上没有限制；表上索引个数可以是列的全排列数，而且一次性能够使用32个；表的数量没有限制。
6.2 术语
高水位标记 High Water Mark
曾经包含数据的最右边的块。在全表扫描时，Oracle将扫描高水标记一下的所有块，即使它们不含数据。TRUNCATE将重新设置高水标记。
自由列表
Freelist
在Oracle中用来跟踪高水标记以下有空闲空间的块对象。保留在高水标记以上的块，只有Freelist为空时才能被用到。
并行更新数据时，配置多个Freelist能提高整体性能，代价是增加了存储空间。
PCTFREE和PCTUSED
INITIAL, NEXT和PCTINCREASE
建议使用Local
Managed表空间并设置Extents大小相等。而在没有使用Local
Managed表空间的情况下，建议总是设置INITIAL=NEXT和PCTINCREASE=0，以模拟Local
Managed表空间的使用。
MINEXTENTS和MAXEXTENTS
LOGGING和NOLOGGING
INITRANS和MAXTRANS
堆组织表
6.3 索引组织表
数据在IOT中根据主键存储和排序。IOT特别适用于IR（信息检索）、空间和OLAP应用程序。
IOT名义上是表，但它们的段实际上是索引段。要显示空间使用等就要先把IOT表的名字转换成潜在的索引名。默认值是SYS_IOT_TOP_<object_id>，object_id是为表分配的内部对象ID。推荐在建表时指定索引名。
主要应用
对只包含主键列的表：使用堆组织表将有100%多的额外开销；
1． 构建自己的索引结构：例如自己实现一个提供大小写不敏感查询的类似函数索引
CREATE
TABLE emp AS SELECT * FORM scott.emp;
CREATE
TABLE upper_name
(x$ename,x$rid,
PRIMARY
KEY(x$ename,x$rid)
)
ORGANIZATION INDEX
AS
SELECT UPPER(ename),ROWID FROM
emp;
CREATE
OR REPLACE TRIGGER upper_ename
AFTER
INSERT OR UPDATE OR DELETE ON emp
FOR EACH
ROW
BEGIN
IF (UPDATING AND
(:OLD.ename||'x'<>:NEW.ename||'x'))
THEN
DELETE FROM
upper_name
WHERE
x$ename=UPPER(:OLD.ename)
AND
x$rid=:OLD.rowid;
INSERT INTO upper_ename(x$ename,x$rid)
VALUES (UPPER(:NEW.ename),:NEW.rowid);
ELSIF
(INSERTING)
THEN
INSERT INTO upper_ename(x$ename,x$rid)
VALUES (UPPER(:NEW.ename),:NEW.rowid);
ELSIF
(DELETING)
THEN
DELETE FROM
upper_name
WHERE
x$ename=UPPER(:OLD.ename)
AND
x$rid=:OLD.rowid;
END IF;
END;
2． 需要加强数据的共同定位或希望数据按特定的顺序物理存储时
对应Sybase和SQL Server用户，这种情况会采用聚簇索引，而这可能达到110%的额外开销，而IOT没有。经常用BETWEEN对主键或者唯一键进行查询，则会降低I/O数量。
主要选项
NOCOMPRESS/COMPRESS N
压缩N列，即对其中前N列相同的值进行压缩。从而能够允许更多数据进入Buffer Cache，代价是略多的CPU能量。
OVERFLOW
PCTTHRESHOLD N/INCLUDING column_name
索引段的存储要密集于普通数据段（每块的行数要多），一般PCTUSED是没有意义的。而OVERFLOW子句允许设置另一个段以允许IOT中的行数据太大时溢出的这个段中。它再次引入PCTUSED，这样PCTUSED和PCTFREE对OVERFLOW段有对于堆组织表中相同的含义。而使用方法是如下中的一种：
PCTTHRESHOLD——当行中数据超出此百分比，该行尾部的列溢出到溢出块；
INCLUDING——指定列之前的列均存入索引块，之后的列存入溢出块。
二次索引
只要主键是IOT，可以在索引中拥有索引。但不像其他一般索引，它不包含真正rowid（物理地址），而是基于主键IOT的逻辑rowid，作用稍小。对于IOT的二次索引访问实际有两个扫描执行（一般表只需一个扫描索引结构），一个在二次结构中，一个在IOT本身中。
6.4 索引聚簇表
Oracle中聚簇是存储一组表的方法，而不是如同SQL
Server、Sybase中那样（那是Oracle中的IOT）。概念上是通过聚簇码列将几张表“预连接”，尽可能将聚簇码列相同的几张表的行放入同一个块中。
CREATE CLUSTER
emp_dept_cluster
(deptno
NUMBER(2))
SIZE 1024;
CREATE INDEX
emp_dept_cluster_idx
ON CLUSTER
emp_dept_cluster;
CREATE TABLE dept
(deptno NUMBER(2) PRIMARY
KEY,
dname
VARCHAR2(14),
loc
VARCHAR2(3)
)
CLUSTER
emp_dept_cluster(deptno);
CREATE TABLE emp
(empno NUMBER PRIMARY
KEY,
ename
VARCHAR2(10),
...
deptno
NUMBER(2) REFERENCES dept(deptno)
)
CLUSTER
emp_dept_cluster(deptno);
BEGIN
FOR x
IN(SELECT * FROM scott.dept)
INSERT
INTO dept VALUES(x.deptno,x.dname,x.loc);
INSERT
INTO emp
SELECT *
FROM scott.emp
WHERE
deptno=x.deptno;
END
END;
注意这里的插入方法，这将尽可能保证每个块中放置尽可能多的聚簇码值，并让可以“预连接”的两个表中的值尽可能在同一个块中。
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)可用于检查rowid所属块。
很容易发现dept和emp有重复的rowid，表和rowid可以唯一确定行，rowid伪列只有在一张表中才是唯一的！
不使用聚簇的情况：
1．聚簇可能消极影响DML性能；
2．全扫描表的性能会受到影响——不仅仅扫描一个表，而是对多个表全扫描；
3．聚簇中的表不能TRUNCATE。
6.5 散列聚簇表
概念类似索引聚簇表，但用散列函数代替了聚簇码索引。Oracle采用行的码值，使用内部函数或者自定义的函数进行散列运算，从而指定数据的存放位置。这样没有在表中增加传统的索引，因此不能Range
Scan散列聚簇中的表，而只能全表扫描（除非单独建立索引）。
CREATE CLUSTER
hash_cluster
(hash_key NUMBER)
HASHKEYS 1000
SIZE 8192;
索引聚簇需要空间时是动态分配，而散列聚簇表在创建时确定了散列码数（HASHKEY）。Oracle采用第一个不小于HASHKEY的质数作为散列码数，将散列码数*SIZE就得到分配的空间（字节），可容纳HASHKEYS/TRUNC(BLOCKSIZE/SIZE)字节的数据。
性能上，散列聚簇表消耗较少I/O，较多CPU，所需执行时间较少，大体取决于CPU时间（当然可能要等待I/O，取决于配置）。
下列情况下使用散列聚簇表较为合适：
1. 在一定程度上精确知道整个过程中表中记录行数或者合理的上限，以确定散列码数；
2. 不大量执行DML，尤其是插入。更新不会产生显著的额外开销，除非更新HASHKEY，这样会导致行迁移；
3. 总是通过HASHKEY值访问数据。
6.6 嵌套表
两种使用嵌套表的方法：
1． PL/SQL代码中作为扩展PL/SQL语言；
2． 作为物理存储机制，以持久地存储集合。
嵌套表语法
创建嵌套表类型：
CREATE TABLE dept
(deptno
NUMBER(2) PRIMARY KEY,
dname
VARCHAR2(14),
loc VARCHAR2(13)
);
CREATE TABLE emp
(empno
NUMBER(4) PRIMARY KEY,
ename
VARCHAR2(10),
job
VARCHAR2(9),
mgr NUMBER(4) REFERENCES
emp,
hiredate DATE,
sal NUMBER(7,
2),
comm NUMBER(7,
2),
deptno NUMBER(2) REFERENCES
dept
);
INSERT INTO dept SELECT * FROM
scott.dept;
INSERT INTO emp SELECT * FROM
scott.emp;
CREATE OR REPLACE TYPE
emp_type
AS OBJECT
(empno
NUMBER(4),
ename
VARCHAR2(10),
job
VARCHAR2(9),
mgr
NUMBER(4),
hiredate DATE,
sal NUMBER(7,
2),
comm NUMBER(7,
2)
);
CREATE OR REPLACE TYPE
emp_tab_type
AS TABLE OF
emp_type;
使用嵌套表：
CREATE TABLE
dept_and_emp
(deptno
NUMBER(2) PRIMARY KEY,
dname
VARCHAR2(14),
loc
VARCHAR2(13),
emps
emp_tab_type
)
NESTED TABLE emps STORE AS
emps_nt;
可以在嵌套表上增加约束：
ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique
UNIQUE(empno) ;
嵌套表不支持参照完整性约束，不能参考任何其他表甚至自己：
ALTER TABLE emps_nt ADD CONSTRAINT
mgr_fk
FOREIGN KEY(mgr) REFERENCES
emps_nt(empno);
会产生错误ORA-30730。
INSERT INTO
dept_and_emp
SELECT dept.*,
CAST(
MULTISET( SELECT empno, ename, job, mgr, hiredate, sal,
comm
FROM
emp
WHERE emp.deptno
= dept.deptno ) AS emp_tab_type )
FROM
dept;
MULTISET用来告诉Oracle子查询返回不止一行，CAST用来告诉Oracle将返回设置为一个集合类型。
查询时，嵌套表中的数据将在同一列中：
SELECT deptno, dname, loc, d.emps AS
employees
FROM dept_and_emp
d
WHERE deptno =
10;
Oracle同样提供方法去掉集合的嵌套，像关系型表一样处理（能够将EMPS列当作一个表，并自然连接且不需要连接条件）：
SELECT d.deptno, d.dname,
emp.*
FROM dept_and_emp D, TABLE(d.emps)
emp;
按照“每行实际是一张表”的思想来更新：
UPDATE