** 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_
1<object_id> ， object_id 是为表分配的内部对象 ID 。推荐在建表时指定索引名。
2
3### 主要应用
4
5对只包含主键列的表：使用堆组织表将有 100% 多的额外开销；
6
71． 构建自己的索引结构：例如自己实现一个提供大小写不敏感查询的类似函数索引
8
9CREATE TABLE emp AS SELECT * FORM scott.emp;
10
11CREATE TABLE upper_name
12
13(x$ename,x$rid,
14
15PRIMARY KEY(x$ename,x$rid)
16
17)
18
19ORGANIZATION INDEX
20
21AS
22
23SELECT UPPER(ename),ROWID FROM emp;
24
25CREATE OR REPLACE TRIGGER upper_ename
26
27AFTER INSERT OR UPDATE OR DELETE ON emp
28
29FOR EACH ROW
30
31BEGIN
32
33IF (UPDATING AND (:OLD.ename||'x'<>:NEW.ename||'x'))
34
35THEN
36
37DELETE FROM upper_name
38
39WHERE x$ename=UPPER(:OLD.ename)
40
41AND x$rid=:OLD.rowid;
42
43INSERT INTO upper_ename(x$ename,x$rid) VALUES (UPPER(:NEW.ename),:NEW.rowid);
44
45ELSIF (INSERTING)
46
47THEN
48
49INSERT INTO upper_ename(x$ename,x$rid) VALUES (UPPER(:NEW.ename),:NEW.rowid);
50
51ELSIF (DELETING)
52
53THEN
54
55DELETE FROM upper_name
56
57WHERE x$ename=UPPER(:OLD.ename)
58
59AND x$rid=:OLD.rowid;
60
61END IF;
62
63END;
64
652． 需要加强数据的共同定位或希望数据按特定的顺序物理存储时
66
67对应 Sybase 和 SQL Server 用户，这种情况会采用聚簇索引，而这可能达到 110% 的额外开销，而 IOT 没有。经常用 BETWEEN 对主键或者唯一键进行查询，则会降低 I/O 数量。
68
69### 主要选项
70
71_ NOCOMPRESS _ /COMPRESS N
72
73压缩 N 列，即对其中前 N 列相同的值进行压缩。从而能够允许更多数据进入 Buffer Cache ，代价是略多的 CPU 能量。
74
75OVERFLOW PCTTHRESHOLD N/INCLUDING column_name
76
77索引段的存储要密集于普通数据段（每块的行数要多），一般 PCTUSED 是没有意义的。而 OVERFLOW 子句允许设置另一个段以允许 IOT 中的行数据太大时溢出的这个段中。它再次引入 PCTUSED ，这样 PCTUSED 和 PCTFREE 对 OVERFLOW 段有对于堆组织表中相同的含义。而使用方法是如下中的一种：
78
79PCTTHRESHOLD—— 当行中数据超出此百分比，该行尾部的列溢出到溢出块；
80
81INCLUDING—— 指定列之前的列均存入索引块，之后的列存入溢出块。
82
83二次索引
84
85只要主键是 IOT ，可以在索引中拥有索引。但不像其他一般索引，它不包含真正 rowid （物理地址），而是基于主键 IOT 的逻辑 rowid ，作用稍小。对于 IOT 的二次索引访问实际有两个扫描执行（一般表只需一个扫描索引结构），一个在二次结构中，一个在 IOT 本身中。
86
87## 6.4 索引聚簇表
88
89Oracle 中聚簇是存储一组表的方法，而不是如同 SQL Server 、 Sybase 中那样（那是 Oracle 中的 IOT ）。概念上是通过聚簇码列将几张表 “ 预连接 ” ，尽可能将聚簇码列相同的几张表的行放入同一个块中。
90
91CREATE CLUSTER emp_dept_cluster
92
93(deptno NUMBER(2))
94
95SIZE 1024;
96
97CREATE INDEX emp_dept_cluster_idx
98
99ON CLUSTER emp_dept_cluster;
100
101CREATE TABLE dept
102
103(deptno NUMBER(2) PRIMARY KEY,
104
105dname VARCHAR2(14),
106
107loc VARCHAR2(3)
108
109)
110
111CLUSTER emp_dept_cluster(deptno);
112
113CREATE TABLE emp
114
115(empno NUMBER PRIMARY KEY,
116
117ename VARCHAR2(10),
118
119...
120
121deptno NUMBER(2) REFERENCES dept(deptno)
122
123)
124
125CLUSTER emp_dept_cluster(deptno);
126
127BEGIN
128
129FOR x IN(SELECT * FROM scott.dept)
130
131LOOP
132
133INSERT INTO dept VALUES(x.deptno,x.dname,x.loc);
134
135INSERT INTO emp
136
137SELECT * FROM scott.emp
138
139WHERE deptno=x.deptno;
140
141END LOOP ;
142
143END;
144
145注意这里的插入方法，这将尽可能保证每个块中放置尽可能多的聚簇码值，并让可以 “ 预连接 ” 的两个表中的值尽可能在同一个块中。
146
147DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) 可用于检查 rowid 所属块。
148
149很容易发现 dept 和 emp 有重复的 rowid ，表和 rowid 可以唯一确定行， rowid 伪列只有在一张表中才是唯一的！
150
151不使用聚簇的情况：
152
1531 ．聚簇可能消极影响 DML 性能；
154
1552 ．全扫描表的性能会受到影响 —— 不仅仅扫描一个表，而是对多个表全扫描；
156
1573 ．聚簇中的表不能 TRUNCATE 。
158
159## 6.5 散列聚簇表
160
161概念类似索引聚簇表，但用散列函数代替了聚簇码索引。 Oracle 采用行的码值，使用内部函数或者自定义的函数进行散列运算，从而指定数据的存放位置。这样没有在表中增加传统的索引，因此不能 Range Scan 散列聚簇中的表，而只能全表扫描（除非单独建立索引）。
162
163CREATE CLUSTER hash_cluster
164
165(hash_key NUMBER)
166
167HASHKEYS 1000
168
169SIZE 8192;
170
171索引聚簇需要空间时是动态分配，而散列聚簇表在创建时确定了散列码数（ HASHKEY ）。 Oracle 采用第一个不小于 HASHKEY 的质数作为散列码数，将散列码数 *SIZE 就得到分配的空间（字节），可容纳 HASHKEYS/TRUNC(BLOCKSIZE/SIZE) 字节的数据。
172
173性能上，散列聚簇表消耗较少 I/O ，较多 CPU ，所需执行时间较少，大体取决于 CPU 时间（当然可能要等待 I/O ，取决于配置）。
174
175下列情况下使用散列聚簇表较为合适：
176
1771\. 在一定程度上精确知道整个过程中表中记录行数或者合理的上限，以确定散列码数；
178
1792\. 不大量执行 DML ，尤其是插入。更新不会产生显著的额外开销，除非更新 HASHKEY ，这样会导致行迁移；
180
1813\. 总是通过 HASHKEY 值访问数据。
182
183## 6.6 嵌套表
184
185两种使用嵌套表的方法：
186
1871． PL/SQL 代码中作为扩展 PL/SQL 语言；
188
1892． 作为物理存储机制，以持久地存储集合。
190
191### 嵌套表语法
192
193创建嵌套表类型：
194
195CREATE TABLE dept
196
197(deptno NUMBER(2) PRIMARY KEY,
198
199dname VARCHAR2(14),
200
201loc VARCHAR2(13)
202
203);
204
205CREATE TABLE emp
206
207(empno NUMBER(4) PRIMARY KEY,
208
209ename VARCHAR2(10),
210
211job VARCHAR2(9),
212
213mgr NUMBER(4) REFERENCES emp,
214
215hiredate DATE,
216
217sal NUMBER(7, 2),
218
219comm NUMBER(7, 2),
220
221deptno NUMBER(2) REFERENCES dept
222
223);
224
225INSERT INTO dept SELECT * FROM scott.dept;
226
227INSERT INTO emp SELECT * FROM scott.emp;
228
229CREATE OR REPLACE TYPE emp_type
230
231AS OBJECT
232
233(empno NUMBER(4),
234
235ename VARCHAR2(10),
236
237job VARCHAR2(9),
238
239mgr NUMBER(4),
240
241hiredate DATE,
242
243sal NUMBER(7, 2),
244
245comm NUMBER(7, 2)
246
247);
248
249CREATE OR REPLACE TYPE emp_tab_type
250
251AS TABLE OF emp_type;
252
253使用嵌套表：
254
255CREATE TABLE dept_and_emp
256
257(deptno NUMBER(2) PRIMARY KEY,
258
259dname VARCHAR2(14),
260
261loc VARCHAR2(13),
262
263emps emp_tab_type
264
265)
266
267NESTED TABLE emps STORE AS emps_nt;
268
269可以在嵌套表上增加约束：
270
271ALTER TABLE emps_nt ADD CONSTRAINT emps_empno_unique
272
273UNIQUE(empno) ;
274
275嵌套表不支持参照完整性约束，不能参考任何其他表甚至自己：
276
277ALTER TABLE emps_nt ADD CONSTRAINT mgr_fk
278
279FOREIGN KEY(mgr) REFERENCES emps_nt(empno);
280
281会产生错误 ORA-30730 。
282
283INSERT INTO dept_and_emp
284
285SELECT dept.*,
286
287CAST( MULTISET( SELECT empno, ename, job, mgr, hiredate, sal, comm
288
289FROM emp
290
291WHERE emp.deptno = dept.deptno ) AS emp_tab_type )
292
293FROM dept;
294
295MULTISET 用来告诉 Oracle 子查询返回不止一行， CAST 用来告诉 Oracle 将返回设置为一个集合类型。
296
297查询时，嵌套表中的数据将在同一列中：
298
299SELECT deptno, dname, loc, d.emps AS employees
300
301<p style="margin: 0cm 0cm 0pt; text-ind</object_id>