Expert One-on-One Oracle阅读笔记

最新推荐文章于 2021-04-15 18:15:38 发布

ern

最新推荐文章于 2021-04-15 18:15:38 发布

阅读量9.1k

点赞数

分类专栏：数据库文章标签： oracle table query session sql 数据库

本文链接：https://blog.csdn.net/ern/article/details/371373

版权

数据库专栏收录该内容

26 篇文章 0 订阅

订阅专栏

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

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)

LOOP

INSERT INTO dept VALUES(x.deptno,x.dname,x.loc);

INSERT INTO emp

SELECT * FROM scott.emp

WHERE deptno=x.deptno;

END LOOP ;

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

TABLE( SELECT emps

FROM dept_and_emp

WHERE deptno = 10

)

SET comm = 100;

但如果返回SELECT emps FROM dept_and_emp WHERE deptno = 10少于一行，更新将失败（普通情况下更新0行是许可的），并返回ORA-22908错误——如同更新语句没有写表名一样；如果返回多于一行，更新也会失败，返回ORA-01427错误。这说明Oracle在使用了嵌套表后认为每一行指向另一个表，而不是如同关系型模型那样认为是另一个行集。

插入与删除的语法：

INSERT INTO TABLE

(SELECT emps FROM dept_and_emps WHERE deptno=10)

VALUES

(1234,'NewEmp','Clerk',7782,SYSDATE,1200,NULL);

DELETE FROM TABLE

(SELECT emps FROM dept_and_emps WHERE deptno=20)

WHERE ename='SCOTT';

一般而言，必须总是连接，而不能单独查询嵌套表（如EMPS）中的数据，但是如果确实需要，是可以的。提示NESTED_TABLE_GET_REFS被用于EXP和IMP处理嵌套表。

SELECT /*+NESTED_TABLE_GET_REFS+*/

NESTED_TABLE_ID, SYS_NC_ROWINFO$

FROM "TKYTE"."EMPS_NT";

而我们察看EMPS_NT的表结构是看不到NESTED_TABLE_ID,SYS_NC_ROWINFO$两列的。对父表DEPT_AND_EMP来说NESTED_TABLE_ID是一个外键。

使用这个提示就可以直接操作嵌套表了：

UPDATE /*+NESTED_TABLE_GET_REFS+*/ emps_nt

SET ename=INITCAP(ename);

嵌套表存储

上例中，现实产生了两张表：

DEPT_AND_EMP
deptno	NUMBER(2)
dname	VARCHAR2(14)
loc	VARCHAR2(13)
SYS_NC0000400005$	RAW(16)

EMPS_NT
SYS_NC_ROWINFO$
NESTED_TABLE_ID	RAW(16)
empno	NUMBER(4)
ename	VARCHAR2(10)
job	VARCHAR2(9)
mgr	NUMBER(4)
hiredate	DATE
sal	NUMBER(7,2)
comm	NUMBER(7,2)

默认情况下，每个嵌套表列都产生一个额外的RAW(16)隐藏列，并在其上创建了唯一约束，用以指向嵌套表。而嵌套表中有两个隐藏列：SYS_NC_ROWINFO$是作为一个对象返回所有标量元素的一个伪列；另一个NESTED_TABLE_ID的外键回指向父表。

可以看到真实代码：

CREATE TABLE TKYTE.DEPT_AND_EMP

(DEPTNO NUMBER(2,0),

DNAME VARCHAR2(14),

LOC VARCHAR2(13),

EMPS EMP_TAB_TYPE)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

STORAGE(INITIAL 131072 NEXT 131072

MINEXTENTS 1 MAXEXTENTS 4096

PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1

BUFFER_POOL DEFAULT)

TABLESPACE USER

NESTED TABLE EMPS

STORE AS EMPS_NT

RETURN BY VALUE;

RETURN BY VALUE用来描述嵌套表如何返回到客户应用程序中。

NESTED_TABLE_ID列必须是索引的，那么较好的解决办法就是使用IOT存储嵌套表。

CREATE TABLE TKYTE.DEPT_AND_EMP

(DEPTNO NUMBER(2,0),

DNAME VARCHAR2(14),

LOC VARCHAR2(13),

EMPS EMP_TAB_TYPE)

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

STORAGE(INITIAL 131072 NEXT 131072

MINEXTENTS 1 MAXEXTENTS 4096

PCTINCREASE 0 FREELISTS 1 FREELIST GROUP 1

BUFFER_POOL DEFAULT)

TABLESPACE USER

NESTED TABLE EMPS

STORE AS EMPS_NT

((empno NOT NULL,

UNIQUE(empno),

PRIMARY KEY(nested_table_id,empno))

ORGANIZATION INDEX COMPRESS 1)

RETURN BY VALUE;

这样与最初默认的嵌套表相比，使用了较少的存储空间并有最需要的索引。

不使用嵌套表作为永久存储机制的原因

1．增加了RAW(16)列的额外开销，父表和子表都将增加这个额外的列；

2．当通常已经有唯一约束时，父表上的唯一约束是额外开销；

3．没有使用不支持的结构（NESTED_TABLE_GET_REFS），嵌套表不容易使用。

一般推荐在编程结构和视图中使用嵌套表。如果要使用嵌套表作为存储机制，确保嵌套表是IOT，以避免NESTED_TABLE_ID和嵌套表本身中索引的额外开销。

6.7 临时表

Oracle的临时表与其他数据库中的不同，其定义是“静态”的。以事务（ON COMMIT DELETE ROWS）或者会话（ON COMMIT PRESERVE ROWS）为基础，只是说明数据的生命期，而在数据库中创建临时表一次，其结构总是有效的，被作为对象存在数据字典中了，这样也就允许对临时表建立视图、存储过程中用静态SQL引用临时表等等。

在实际开发中，考虑到DDL是消耗较大的操作，应该避免在运行时操作，而是将应用程序需要的临时表在程序安装时就创建，而只是在存储过程中简单的INSERT、SELECT。

临时表不支持的永久表的特性有：

1．不能用参照完整性约束，也不能被参照完整性约束所引用；

2．不能有VARRAY或者NESTED TABLE类型的列；

3．不能是IOT；

4．不能是索引或者散列聚簇；

5．不能分区；

6．通过ANALYZE命令不能产生统计信息，也即是说优化器在临时表上没有真正的统计功能。

由于缺少统计功能，那么CBO（基于成本的优化器）的性能将受到极大的影响，因此应当尽可能使用INLINE VIEW。

要让临时表拥有正确的统计信息，CBO产生正确的决策，可以先建立一张结构与临时表完全相同的普通表：

CREATE TABLE temp_all_objects

SELECT * FROM all_objects WHERE 1=0;

CREATE INDEX temp_all_objects_idx ON temp_all_objects(object_id);

选择插入代表性数据后进行分析：

...

ANALYZE TABLE temp_all_objects COMPUTE STATISTICS FOR ALL INDEX;

BEGIN

DBMS_STATS.CREATE_STAT_TABLE(ownname => USER,

stattab => 'STATS');

DBMS_STATS.EXPORT_TABLE_STATS(ownname => USER,

tabname => 'TEMP_ALL_OBJECTS',

stattab => 'STATS');

DBMS_STATS.EXPORT_INDEX_STATS(ownname => USER,

tabname => 'TEMP_ALL_OBJECTS_IDX',

stattab => 'STATS');

END;

建立临时表：

DROP TABLE temp_all_objects;

CREATE GLOBAL TEMPORARY TABLE temp_all_objects

SELECT * FROM all_objects WHERE 1=0;

导入正确的信息后CBO将使用这些信息决定执行模式：

CREATE INDEX temp_all_objects_idx ON temp_all_objects(object_id);

BEGIN

DBMS_STATS.IMPORT_TABLE_STATS(ownname => USER,

tabname => 'TEMP_ALL_OBJECTS',

stattab => 'STATS');

DBMS_STATS.IMPORT_INDEX_STATS(ownname => USER,

tabname => 'TEMP_ALL_OBJECTS_IDX',

stattab => 'STATS');

END;

6.8 对象表

基于类型（Type）创建的表，而不是作为列的集合。创建语法：

CREATE TABLE t OF some_type;

对于下例：

CREATE OR REPLACE TYPE address_type

AS OBJECT

(city VARCHAR2(30),

street VARCHAR2(30),

state VARCHAR2(2),

zip NUMBER

);

CREATE OR REPLACE TYPE person_type

AS OBJECT

(name VARCHAR2(30),

dob DATE,

home_address address_type,

work_address address_type

);

CREATE TABLE people OF person_type;

通过执行如下语句，可以看到数据库中实际存放的结构：

SELECT name,segcollength

FROM SYS.COL$

WHERE obj#=(SELECT object_id

FROM user_objects

WHERE object_name='PEOPLE')；

PEOPLE
SYS_NC_OID$	16
SYS_NC_ROWINFO$	1
NAME	30
DOB	7
HOME_ADDRESS	1
SYS_NC00006$	30
SYS_NC00007$	30
SYS_NC00008$	2
SYS_NC00009$	22
WORK_ADDRESS	1
SYS_NC00011$	30
SYS_NC00012$	30
SYS_NC00013$	2
SYS_NC00014$	22

SYS_NC_OID$是系统为表产生的Object ID，RAW(16)，其上有唯一性索引。它是一主键为基础，并不是系统产生的，是一个伪列，且没有在硬盘上真正消耗空间；

SYS_NC_ROWINFO$类似于嵌套表中，可作为单独一列返回整行；

NAME, DOB是表中原有标量；

HOME_ADDRESS, WORK_ADDRESS可作为单个对象，返回所代表的列的集合；

SYS_NCnnnnn$是内嵌对象类型的标量实现。

第 7 章索引

7.1 索引类别

B*树索引——传统索引，从码转向行

索引组织表

B*树聚簇索引——从聚簇码转向包含与行相关的聚簇码的块

反向码索引——更均匀的分配索引条目

降序索引——允许数据在索引中降序排列

位图索引

基于函数的索引

应用程序域索引

interMedia文本索引

7.2 B*树索引

索引中的叶结点实际上是双向链表，这样不必经过上级结点可以直接Range Scan。在B*树中实际不会出现不唯一的索引，对不唯一索引只要加上ROWID就唯一了。对于排序，不唯一索引先根据索引值排序，然后根据ROWID排序；唯一索引根据索引值排序。

B*树一般2-3层，且自动平衡。

反向码索引

实际就是将索引值的内部表示（高低位）反转，这样相邻的索引值之间距离变远，便于并发。缺点是不能支持所有正常索引的功能，例如无法支持WHERE x>5这样的谓词。

降序索引

使用环境

1. 处理表中很多行，但索引就能替代表；

如对一张已经建立了索引的表COUNT(*)

2. 访问表中极少的行，一般2-3%（如果表中有多列或较宽的列，则此百分比可升至20-25%）。

否则效率不及全表扫描。原因在于由索引访问块，则访问顺序几乎随机，每个块可能要访问多次，则不及全表的块一次性扫描效率高。但这同样需要考虑表中特定的数据，若数据在表中基本按主键顺序排列，则使用索引又会效率很高——每个块不会或很少访问多次。

视图USER_INDEXES中CLUSTERING_FACTOR列说明了数据存放的随机程度：

若CLUSTERING_FACTOR接近块的数量，则表较易排序，单个叶块上的索引条目趋向于指向同一个块上的列；

若CLUSTERING_FACTOR接近行数，则表是随机排序的。

7.3 位图索引

在7.3版本中加入，而8i标准版不支持。为数据仓库等特定查询环境设计，不应用在OLTP系统或许多并行会话经常更新数据的系统。使用的另一个基本原则是基数（字段不同的取值）较低。总体上适合集中读取的环境，而极不适合集中写入的环境。原因在于任何需要更新同一个位图索引条目的修改都将锁定整个位图，严重抑制了并发性。通常建立位图索引的时间短过B*树索引。

7.4 基于函数的索引

在 8.1.5 版加入，8i标准版不支持。

实现前提

1. 在自己的模式中的表上创建基于函数的索引，必须具有系统特权QUERY REWRITE；对其他用户，必须具有系统特权GLOBAL QUERY REWRITE；

2. 使用CBO；

3. 必须设置如下会话或系统变量：

QUERY_REWRITE_ENABALED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED

这些参数可以通过ALTER SESSION或者ALTER SYSTEM来修改，或者直接修改init.ora。其中QUERY_REWRITE_INTEGRITY=TRUSTED说明系统可以信任函数，不会产生二义性结果；

4. 使用SUBSTR来限定从用户定义的函数中返回的VARCHAR2或RAW类型的值。

注意

索引项的大小应在数据块的1/3以内，对于普通8K而言就是3218字节（否则会报ORA-01450错误代码）。因此，对于返回VARCHAR2或RAW类型值的函数应用SUBSTR来限制。为了掩藏其复杂性并提高灵活性（允许以后更改SUBSTR的大小），我们可以使用视图来掩盖（即将相应字段更换为SUBSTR后的值），系统同样会识别出基于函数的索引。

对于 8.1.7 之前的系统要注意，使用to_date作为建立索引的函数时会报ORA-01743错误代码，解决方法是自己实现一个to_date的外壳。

对于用户自定义的函数建立索引后，不能Direct Path导入，而对系统提供的函数不受影响。

鉴于每次插入或更新，对应函数都执行一次，其修改的效率降低了很多，但带来很多查询的效率提高。

7.5 应用程序域索引

又称为“可扩展的索引”，允许创建自己的索引结构，如同系统提供的一样工作。

7.6 常见问题解答

索引能否在视图中使用

系统是用视图的定义来访问数据，较好的索引基表，就能够提高视图效率。

索引和NULL

除了B*树索引聚簇的特殊情况，B*树索引不存储NULL的条目，而位图索引和聚簇索引存储所有NULL条目。

利用这一特性，若表中某列大部分取值一致，则可将其修改为NULL，将极大的缩小索引占用空间。

外键上的索引

非索引的外键是导致死锁的主要原因。父表修改时将全表锁定子表，若关联着的子表外键无索引，则每次删除父表中的一行就会对子表全表扫描一次。

不需要外键索引的情况：

不从父表中删除；

不更新父表主键或唯一键的值；

不连接父表和子表。

未用到索引的原因

1. 一张T(x,y)上有索引的表T，查询SELECT * FROM T WHERE y=5，由于谓词未包含X，则必须全扫描索引条目，则优化器通常选择对T全表扫描；查询SELECT x,y FROM T WHERE y=5，则优化器会注意到为得到x和y，不必进入表，且一般索引小于表，则CBO会选择快速全扫描索引。

2. 查询SELECT COUNT(*) FROM T，表上有一个B*树索引，则考虑到索引可能在一系列包含空值的列上，优化器选择全扫描表。

3. 对列使用了函数，而索引只是基于列的。

4. 一个字符列上有索引，但谓词是indexed_column=5，这里系统隐含使用了to_number函数，同3，不会使用索引。

5. 使用索引实际会降低速度。

6. 很长时间没有分析表了，表的增长较快，这样CBO会作出错误的判断。

索引中空间重用

只要出现的行可重用，索引块上的空间就能重用；

当一个索引块为空时将加入FREELIST，从而可以重用，但和堆组织表不同，即便只有一个索引，也会占据一个块。

第 8 章导入和导出

8.1 IMP/EXP的工作原理

大量导出

EXP和一般文件一样，在支持搜索的设备（即文件系统？）上能产生的文件大小是有限制的，它使用一般OS文件的API，在32位系统中限制的文件大小为2GB。已知以下4种解决方法。

1. 使用FILESIZE参数

该参数在8i中引入。设置后将限制每个导出的DMP文件的大小，问题在于必须大致估计文件个数（即总导出量），并提供FILE参数列表，否则将产生交互式会话，请求提供文件名，而对于定时无人值守的操作，长时间无响应会产生错误。另一方面，将多个DMP文件导入时可以一次性提供多个文件名，即便实际文件不存在，只会提示警告而不会出错。

2. 导出较小的数据文件

比如按方案导出等

3. 导出到OS管道

此方法目前仅应用于UNIX。

#!/bin/csh -f

# Set this to the userid you want to perform the export as I always use OPS$ (os

# authenticated) accounts for all jobs that will be run in the background. In that

# way a password never appears in a script file or in the ps output.

setenv UID /

# This is the name of the export file. SPLIT will use this to name the pieces of

# the compressed DMP file.

setenv FN exp.`date +%j_%Y`.dmp

# This is the name of the named pipe we will use.

setenv PIPE /tmp/exp_tmp_ora8i.dmp

# Here I limit the size of the compressed files to 500 MB each. Anything less

# than 2 GB would be fine.

setenv MAXSIZE 500m

# This is what we are going to export. By default I am doing a full database

# export.

setenv EXPORT_WHAT "full=y COMPRESS=n"

# This is where the export will go to.

cd /nfs/atc-netapp1/expbkup_ora8i

# Clear out the last export.

rm expbkup.log export.test exp.*.dmp* $PIPE

# Create the named pipe.

mknod $PIPE p

# Write the datetime to the log file.

date > expbkup.log

# Start a gzip process in the background. Gzip will read the pipe and put the

# compressed data out to split. Split will then create 500 MB files out of the

# input data adding .aa, .ab, .ac, .ad, ... file extensions to the template name

# found in $FN.

( gzip < $PIPE ) | split -b $MAXSIZE - $FN. &

# Now, start up export. The Gzip above is waiting for export to start filling the

# pipe up.

exp userid=$UID buffer=20000000 file=$PIPE $EXPORT_WHAT >>& expbkup.log

date >> expbkup.log

# Now the export is done, this is how to IMP. We need to sort the filenames and

# then simply cat their contents into gunzip. We write that into the pipe. IMP

# will then read that pipe and write what it would do to stderr. The >>& in the

# csh redirects both stdout and stderr for us.

date > export.test

cat `echo $FN.* | sort` | gunzip > $PIPE &

imp userid=$UID file=$PIPE show=y full=y >>& export.test

date >> export.test

# Clean up the pipe, we don't need it anymore.

rm -f $PIPE

4. 导出到一个不支持搜索的设备

仅用于UNIX。指定设备名，可以直接将数据导出到磁带设备中。

数据子集

即设置QUERY参数。但条件中往往含有各个OS上的保留字符，那么用par（参数）文件的形式更通用一点。如：

Windows下：

C:/exp>exp userid=… tables=t query=”””where object_id< 5000” ””

UNIX下：

$exp userid=… tables=t query=/ ”where object_id/<5000/”

使用参数文件：

exp.par: query=”where object_id< 5000”

exp userid=… tables=t parfile=exp.par

数据传输

即直接将一个数据库上的数据文件附加到另一个数据库中。

规则：

1. 源数据库与目标数据库必须运行在相同的硬件平台上；

2. 源数据库与目标数据库必须使用相同的字符集；

3. 源数据库一定不能有与目标表空间同名的表空间；

4. 源数据库与目标数据库的块大小一样；

5. 被传输的表空间必须是完备的，如其含有索引等但不包含对应的表；

6. 源数据库在导出元数据和复制数据文件过程中必须将导出的表空间设为只读模式；

7. SYS拥有的对象无法传输；

8. 不能传输的对象有：快照/物化视图、基于函数的索引、区域索引、领域引用(Scoped Refs)和多个接收者的AQ。

检测表空间是否完备：

exec sys.dbms_tts.transport_set_check( 'tb1', TRUE );

select * from sys.transport_set_violations;

exec sys.dbms_tts.transport_set_check( 'tb2', TRUE );

select * from sys.transport_set_violations;

exec sys.dbms_tts.transport_set_check( 'tb1, tb2', TRUE );

select * from sys.transport_set_violations;

无返回则说明该（套）表空间完备。

开始传输：

alter tablespace tts_ex1 read only;

alter tablespace tts_ex2 read only;

host exp userid="""sys/change_on_install as sysdba""" transport_tablespace=y tablespaces=(tts_ex1,tts_ex2)

host XCOPY c:/oracle/oradata/tkyte816/tts_ex?.dbf c:/temp

alter tablespace tts_ex1 read write;

alter tablespace tts_ex2 read write;

imp file=expdat.dmp userid="""sys/change_on_install as sysdba""" transport_tablespace=y "datafiles=(c:/temp/tts_ex1.dbf,c:/temp/tts_ex2.dbf)"

alter tablespace tts_ex1 read write;

alter tablespace tts_ex2 read write;

注意到使用的用户，在 8.1.6 后必须使用SYSDBA帐户才能执行传输，之前则DBA即可。

获得DDL

SHOW=Y和INDEXFILE=文件名两种选项均可显示DDL，但前者的显示断行不合理、语句均加上了双引号，因此倾向使用INDEXFILE选项；但IMP在INDEXFILE选项中不显示触发器和视图的DDL。

1．获取程序包、函数和存储过程代码：@getcode procedure_name

REM getcode.sql

SET feedback OFF

SET heading OFF

SET termout OFF

SET linesize 1000

SET trimspool ON

SET verify OFF

SPOOL &1.sql

PROMPT SET DEFINE OFF

SELECT DECODE(type||'-'||TO_CHAR(line,'fm99999'),'PACKAGE BODY-1','/'||CHR(10),NULL)||DECODE(line,1,'create or replace','')||text text

FROM user_source

whre name=UPPER('&&1')

ORDER BY TYPE,line;

PROMPT /

PROMPT SET DEFINE ON

SPOOL OFF

SET feedback ON

SET heading ON

SET termout ON

SET linesize 100

2．获得视图DDL：@getaview view_name

REM getaview.sql

SET feedback OFF

SET heading OFF

SET termout OFF

SET linesize 1000

SET trimspool ON

SET verify OFF

SET LONG 99999999

SET embedded ON

SPOOL &1.sql

PROMPT CREATE OR REPLACE VIEW &1(SELECT DECODE(column_id,1,'',',')||column_name column_name FROM user_tab_columns WHERE TABLE table_name=UPPER('&1') ORDER BY column_id;

PROMPT AS SELECT text FROM user_views WHERE view_name=UPPER('&1')

PROMPT /

SPOOL OFF

SET feedback ON

SET heading ON

SET termout ON

SET linesize 100

3. 触发器DDL：@gettrig trigger_name

REM gettrig.sql

SET feedback OFF

SET heading OFF

SET termout OFF

SET trimspool ON

SET verify OFF

SET LONG 99999999

SPOOL &1.sql

SELECT 'CREATE OR REPLACE TRIGGER('''||trigger_name||'''||chr(10)||decode(substr(trigger_type,1,1),'A','AFTER','B','BEFORE','T',INSTEAD OF')||CHR(10)||triggering_event||CHR(10)||'ON'''||table_owner||'''.'''||table_name||''''||CHR(10)||DECODE(INSTR(trigger_type,'EACH ROW'),0,NULL,'FOR EACH ROW')||CHR(10),trigger_body FROM user_triggers WHERE trigger_name=UPPER('&1')

PROMPT /

SPOOL OFF

SET feedback ON

SET heading ON

SET termout ON

导入到不同结构

1. 增加了列

不需要额外工作，通常将增加的列设为NULL或其他指定的默认值。

2. 减少了列

将修改后的表改名，并用原表名建立视图，对视图建立INSTEAD OF INSERT…的触发器。

3. 改变了列的数据类型

同上2的方法。

直接路径导出

即DIRECT=Y。此模式绕过了SQL评估缓冲区，QUERY选项失效，但可以节省10%的处理。

8.2 警告和错误

克隆

使用EXP/IMP克隆用户时要注意，完整性约束（特别是显式声明参照同一方案下表的外键，如REFERENCES FROMUSER.TABLE）将自动根据FROMUSER和TOUSER更改所有者，而参照其他方案下表的外键、显式声明基于同一方案下表的视图（如… AS SELECT * FROM FROMUSER.TABLE）和触发器将不会改变所有者，这样如果IMP入的库中有与FROMUSER同名的用户或TOUSER权限不足，将产生错误。因此在运行前，应检查所有的DDL、触发器和过程等：

imp userid=… from user=tkyte touser=a indexfile=….sql

imp userid=… from user=tkyte touser=a show=y

跨版本使用IMP/EXP

规则：IMP采用导入数据库的版本；EXP采用两个数据库中较低的版本。

索引的丢失

EXP/IMP后，系统命名的“冗余”索引将会丢失，原因有二：

1. 系统设定的索引名可能与导入数据库中已有的系统分配的索引名重复；

2. 对象的创建本身可能已经创建了索引，即再导入隐式索引可能重复。

第2点即说明所谓“冗余”：在创建表时隐式创建了主键，随后创建一个第一字段就是主键列的索引，那么该表上实际有两个索引；但变换顺序，先创建后面的索引，再显式创建主键，结果是系统用该索引来加强主键，并不创建新索引。后者的情况就如同EXP/IMP的选择，是正确的。

重复导入导致增加冗余约束

对于含有系统命名约束（如check等）的表，导出后如果多次执行导入，虽然会提示约束名已被占用而不会重复导入约束，但仍会每次添加一些重复约束，从而导致性能的下降。因此综合之前几点，还是应该使用显式命名的约束。

NLS问题

在导出和导入时看到possible charset conversion的提示就应当注意字符集问题，应将系统NLS_LANG设置与数据库字符集一致。

表跨越多个表空间

对于单个表空间的表，导入时未找到表空间或配额不足，IMP将重写SQL，使用导入库的缺省表空间来导入。而对跨越多个表空间的表（如分区表等），IMP不会如此。唯一方法是事先建立类似表空间结构的表再导入。

第 9 章数据装载

9.1 SQL Loader简介

9.2 如何装载

装载定界数据

TERMINATED BY WHITESPACE通过查找下一个非空格字符（即不是制表符、空格或换行）位置来定位。

装载固定格式数据

POSITION(*:..)中*指示控制文件在最后字段停止位置重新开始，并可用+、-进行相对位置移动。另外POSITION子句可以使用重叠位置，并在记录中往返。

装载日期

使用序列和其他函数装载数据

更新现有的行和插入新行

装载报表类型的输入数据

装载文件到一个长RAW或LONG字段中

装载含换行符的数据

1. 使用其它字符代替换行符，导入时替换

如果控制了数据文件的产生，可用例如“/n”类的字符来替代换行符，在导入时用

字段名 “replace(:字段名,’/n’,chr(10))”（Win平台）和字段名 “replace(:字段名,’//n’,chr(10))”（UNIX平台）

来替代。

2. 使用FIX属性

使用形如INFILE ….DAT “fix nnn”的选项，则指定数据文件每行长度nnn。必须注意的是，在UNIX平台下，换行仅为”/n”，而在Win平台下为”/r/n”，这样用这种方法最好确保生成数据文件的平台和导入的平台一致，否则由于每行长度不同容易出错。

3. 使用VAR属性

同前，使用INFILE ….DAT “var n”，即数据文件每行的前n个字符说明该行长度。

4. 使用STR属性

为数据文件设立新的分隔符，使用INFILE ….DAT “str X’ooo’”，即ooo作为分隔符，而不是换行符。其中ooo是如下获得的16进制数：

SELECT UTL_RAW.CAST_TO_RAW(…) FROM DUAL;

5. 内嵌的换行符换行

卸载数据

CREATE OR REPLACE PACKAGE UNLOADER

FUNCTION RUN( P_QUERY IN VARCHAR2,

P_TNAME IN VARCHAR2,

P_MODE IN VARCHAR2 DEFAULT 'REPLACE',

P_DIR IN VARCHAR2,

P_FILENAME IN VARCHAR2,

P_SEPARATOR IN VARCHAR2 DEFAULT ',',

P_ENCLOSURE IN VARCHAR2 DEFAULT '"',

P_TERMINATOR IN VARCHAR2 DEFAULT '|' )

RETURN NUMBER;

END;

CREATE OR REPLACE PACKAGE BODY UNLOADER

G_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;

G_DESCTBL DBMS_SQL.DESC_TAB;

G_NL VARCHAR2(2) DEFAULT CHR(10);

FUNCTION TO_HEX( P_STR IN VARCHAR2 ) RETURN VARCHAR2

BEGIN

RETURN TO_CHAR( ASCII(P_STR), 'FM0X' );

END;

PROCEDURE DUMP_CTL( P_DIR IN VARCHAR2,

P_FILENAME IN VARCHAR2,

P_TNAME IN VARCHAR2,

P_MODE IN VARCHAR2,

P_SEPARATOR IN VARCHAR2,

P_ENCLOSURE IN VARCHAR2,

P_TERMINATOR IN VARCHAR2 )

L_OUTPUT UTL_FILE.FILE_TYPE;

L_SEP VARCHAR2(5);

L_STR VARCHAR2(5);

L_PATH VARCHAR2(5);

BEGIN

IF ( P_DIR LIKE '%/%' )

THEN

-- WINDOWS PLATFORMS --

L_STR := CHR(13) || CHR(10);

IF ( P_DIR NOT LIKE '%/' AND P_FILENAME NOT LIKE '/%' )

THEN

L_PATH := '/';

END IF;

ELSE

L_STR := CHR(10);

IF ( P_DIR NOT LIKE '%/' AND P_FILENAME NOT LIKE '/%' )

THEN

L_PATH := '/';

END IF;

L_OUTPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME || '.CTL', 'W' );

UTL_FILE.PUT_LINE( L_OUTPUT, 'LOAD DATA' );

UTL_FILE.PUT_LINE( L_OUTPUT, 'INFILE ''' || P_DIR || L_PATH ||

P_FILENAME || '.DAT'' "STR X''' ||

UTL_RAW.CAST_TO_RAW( P_TERMINATOR ||

L_STR ) || '''"' );

UTL_FILE.PUT_LINE( L_OUTPUT, 'INTO TABLE ' || P_TNAME );

UTL_FILE.PUT_LINE( L_OUTPUT, P_MODE );

UTL_FILE.PUT_LINE( L_OUTPUT, 'FIELDS TERMINATED BY X''' ||

TO_HEX(P_SEPARATOR) ||

''' ENCLOSED BY X''' ||

TO_HEX(P_ENCLOSURE) || ''' ' );

UTL_FILE.PUT_LINE( L_OUTPUT, '(' );

FOR I IN 1 .. G_DESCTBL.COUNT

LOOP

IF ( G_DESCTBL(I).COL_TYPE = 12 )

THEN

UTL_FILE.PUT( L_OUTPUT, L_SEP || G_DESCTBL(I).COL_NAME ||

' DATE ''DDMMYYYYHH24MISS'' ');

ELSE

UTL_FILE.PUT( L_OUTPUT, L_SEP || G_DESCTBL(I).COL_NAME ||

' CHAR(' ||

TO_CHAR(G_DESCTBL(I).COL_MAX_LEN*2) ||' )' );

END IF;

L_SEP := ','||G_NL ;

END LOOP ;

UTL_FILE.PUT_LINE( L_OUTPUT, G_NL || ')' );

UTL_FILE.FCLOSE( L_OUTPUT );

END;

FUNCTION QUOTE(P_STR IN VARCHAR2, P_ENCLOSURE IN VARCHAR2)

RETURN VARCHAR2

BEGIN

RETURN P_ENCLOSURE ||

REPLACE( P_STR, P_ENCLOSURE, P_ENCLOSURE||P_ENCLOSURE ) ||

P_ENCLOSURE;

END;

FUNCTION RUN( P_QUERY IN VARCHAR2,

P_TNAME IN VARCHAR2,

P_MODE IN VARCHAR2 DEFAULT 'REPLACE',

P_DIR IN VARCHAR2,

P_FILENAME IN VARCHAR2,

P_SEPARATOR IN VARCHAR2 DEFAULT ',',

P_ENCLOSURE IN VARCHAR2 DEFAULT '"',

P_TERMINATOR IN VARCHAR2 DEFAULT '|' ) RETURN NUMBER

L_OUTPUT UTL_FILE.FILE_TYPE;

L_COLUMNVALUE VARCHAR2(4000);

L_COLCNT NUMBER DEFAULT 0;

L_SEPARATOR VARCHAR2(10) DEFAULT '';

L_CNT NUMBER DEFAULT 0;

L_LINE LONG;

L_DATEFMT VARCHAR2(255);

L_DESCTBL DBMS_SQL.DESC_TAB;

BEGIN

SELECT VALUE

INTO L_DATEFMT

FROM NLS_SESSION_PARAMETERS

WHERE PARAMETER = 'NLS_DATE_FORMAT';

SET THE DATE FORMAT TO A BIG NUMERIC STRING. AVOIDS

ALL NLS ISSUES AND SAVES BOTH THE TIME AND DATE.

EXECUTE IMMEDIATE

'ALTER SESSION SET NLS_DATE_FORMAT=''DDMMYYYYHH24MISS'' ';

SET UP AN EXCEPTION BLOCK SO THAT IN THE EVENT OF ANY

ERROR, WE CAN AT LEAST RESET THE DATE FORMAT BACK.

BEGIN

PARSE AND DESCRIBE THE QUERY. WE RESET THE

DESCTBL TO AN EMPTY TABLE SO .COUNT ON IT

WILL BE RELIABLE.

DBMS_SQL.PARSE( G_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );

G_DESCTBL := L_DESCTBL;

DBMS_SQL.DESCRIBE_COLUMNS( G_THECURSOR, L_COLCNT, G_DESCTBL );

CREATE A CONTROL FILE TO RELOAD THIS DATA

INTO THE DESIRED TABLE.

DUMP_CTL( P_DIR, P_FILENAME, P_TNAME, P_MODE, P_SEPARATOR,

P_ENCLOSURE, P_TERMINATOR );

BIND EVERY SINGLE COLUMN TO A VARCHAR2(4000). WE DON'T CARE

IF WE ARE FETCHING A NUMBER OR A DATE OR WHATEVER.

EVERYTHING CAN BE A STRING.

FOR I IN 1 .. L_COLCNT LOOP

DBMS_SQL.DEFINE_COLUMN( G_THECURSOR, I, L_COLUMNVALUE, 4000 );

END LOOP ;

RUN THE QUERY - IGNORE THE OUTPUT OF EXECUTE. IT IS ONLY

VALID WHEN THE DML IS AN INSERT/UPDATE OR DELETE.

L_CNT := DBMS_SQL.EXECUTE(G_THECURSOR);

OPEN THE FILE TO WRITE OUTPUT TO AND THEN WRITE THE

DELIMITED DATA TO IT.

L_OUTPUT := UTL_FILE.FOPEN( P_DIR, P_FILENAME || '.DAT', 'W',

32760 );

LOOP

EXIT WHEN ( DBMS_SQL.FETCH_ROWS(G_THECURSOR) <= 0 );

L_SEPARATOR := '';

L_LINE := NULL;

FOR I IN 1 .. L_COLCNT LOOP

DBMS_SQL.COLUMN_VALUE( G_THECURSOR, I,

L_COLUMNVALUE );

L_LINE := L_LINE || L_SEPARATOR ||

QUOTE( L_COLUMNVALUE, P_ENCLOSURE );

L_SEPARATOR := P_SEPARATOR;

END LOOP ;

L_LINE := L_LINE || P_TERMINATOR;

UTL_FILE.PUT_LINE( L_OUTPUT, L_LINE );

L_CNT := L_CNT+1;

END LOOP ;

UTL_FILE.FCLOSE( L_OUTPUT );

NOW RESET THE DATE FORMAT AND RETURN THE NUMBER OF ROWS

WRITTEN TO THE OUTPUT FILE.

EXECUTE IMMEDIATE

'ALTER SESSION SET NLS_DATE_FORMAT=''' || L_DATEFMT || '''';

RETURN L_CNT;

-- EXCEPTION

IN THE EVENT OF ANY ERROR, RESET THE DATA FORMAT AND

RE-RAISE THE ERROR.

-- WHEN OTHERS THEN

-- EXECUTE IMMEDIATE

-- 'ALTER SESSION SET NLS_DATE_FORMAT=''' || L_DATEFMT || '''';

-- RAISE;

END;

END RUN;

END UNLOADER;

装载LOB

1. 利用PL/SQL

CREATE OR REPLACE DIRECTORY 目录名 AS ‘路径’;

DECLARE

L_CLOB CLOB;

L_BFILE BFILE;

BEGIN

INSERT INTO DEMO VALUES ( 1, EMPTY_CLOB() )

RETURNING THECLOB INTO L_CLOB;

--若路径名定义时未用””，则内部转换为全大写

L_BFILE := BFILENAME( '路径名', '文件名' );

DBMS_LOB.FILEOPEN( L_BFILE );

DBMS_LOB.LOADFROMFILE( L_CLOB, L_BFILE,

DBMS_LOB.GETLENGTH( L_BFILE ) );

DBMS_LOB.FILECLOSE( L_BFILE );

END;

2. 使用SQLLDR

i. 装载同一行的LOB数据

这里注意LOB数据中往往含有逗号、换行符等，那么确定分割符时同前，Win平台为”str X’ 7C 0D 0A ’”而UNIX平台是”str X’ 7C 0A ’”，其次可以将LOB的那个字段类型最大说明为CHAR(1000000)。

ii. 装载不在同行中的LOB数据

即数据文件中某列包含了需要装载到LOB的文件名。则ctl文件中数据类型说明部分为“字段名 LOBFILE(含文件名的字段名) TERMINATED BY EOF”。

iii. 装载LOB数据到对象列

用SQLLDR装载VARRAYS/嵌套表

在存储过程中调用SQLLDR

无法调用。只能用PL/SQL、Java、C来实现一个小SQLLDR。

9.3 警告

不能选择要使用的回滚段

使用REPLACE选项，将在导入前产生DELETE命令，可能产生大量回滚，但Oracle不允许选择回滚段。

TRUNCATE的不同作用

假设将要装载相似数量的数据，则可使用TRUNCATE的扩展形式：

TRUNCATE TABLE … REUSE STORAGE

这样并未释放空间，只是将空间均标志为自由空间。

SQLLDR默认为CHAR(255)

要导入更长的文本，只需显式指定CHAR(N)。

命令行取代控制文件

对于例如INFILE等命令行与控制文件均可指定的参数，命令行具有优先级。

第 10 章优化策略与工具

10.1 标识问题

10.2 我的方法

10.3 绑定变量与分析（再次）

不使用绑定变量将增加语句分析，除了消耗CPU时间外，还会增加字典高速缓存上的闩锁。

显示会话等待的事件：V$SESSION_EVENT。具体事件名和含义可以参考Oracle Reference Manual的附录Oracle Wait Events。

CURSOR_SHARING

CURSOR_SHARING参数缺省为EXACT，若指定为FORCE，则优化器可能将语句中所有的常数转换为绑定变量，虽然减少了语句分析，但是也会带来如下副作用：

优化器可供利用的信息可能减少，从而改变执行路径，例如条件中对于某个特定值索引有较好的选择性，改为绑定变量时优化器并不会发现这一点。

查询输出格式发生变化。虽然返回的数据长度不变，但列的长度可能改变。例如对于SELECT id, ‘tom’ name from emp; name应该为VARCHAR2(3)，但是由于’tom’被改为绑定变量，则可能name的显示长度变为32。

查询计划更难评估。由于语句的改变，EXPLAIN PLAN看到的查询与数据库看到的可能不一致，从而使AUTOTRACE等的输出与实际执行路径不一致。

因此，完善的应用系统不应当依靠CURSOR_SHARING来提高效率，仅能作为权宜之计。

10.4 SQL_TRACE, TIMED_STATISTICS与TKPROF

TIMED_STATISTICS并不会对系统产生过大负担，因此建议设置为TRUE。

启动跟踪

SQL_TRACE可在系统或会话级激活。激活后跟踪文件将产生至init.ora参数USER_DUMP_DEST（专用服务器）或BACKGROUND_DUMP_DEST（MTS）指定的目录。而文件大小通过MAX_DUMP_FILE_SIZE控制，其设置有如下三种方法：

仅数值：以OS块为单位；

数值+K/M：指定文件绝对大小；

UNLIMITED：无上限。

一般只需要设置50 -100M 就足够了。

激活SQL_TRACE的几种常用方式如下：

ALTER SESSION SET SQL_TRACE=TRUE|FALSE;

SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION 这里我们需要指定SID和SERIAL#（参考V$SESSION）；

ALTER SESSION SET EVENTS. 可获得更详细的信息。

此外也可通过DBMS_SUPPORT包，相当于EVENTS跟踪的一个界面，但此包需要Oracle人员支持，非标配。

随着WEB服务方式的普及，往往一个数据库会话很短，难以单独跟踪，对此，我们可以根据用户，在数据库级建立触发器：

CREATE OR REPLACE TRIGGER logon_trigger

AFTER LOGON ON DATABASE

BEGIN

IF ( USER= ‘TKYTE’ ) THEN

EXECUTE IMMEDIATE ‘ALTER SESSION SET EVENTS ‘ ’10046 TRACE NAME CONTEXT FOREVER, LEVEL 4’ ‘ ’;

END IF;

END;/

使用并解析TKPROF输出

1. 激活SQL_TRACE后，通过如下查询检查SPID：

SELECT a.spid FROM v$process a, v$session b

WHERE a.addr = b.paddr

AND b.audsid = userenv(‘sessionid’);

此SPID就包含在跟踪文件的文件名中。

UNIX系统中，若你不在Oracle的管理组中，则生成的跟踪文件所在目录可能无法访问，此时需要设定init.ora参数_trace_files_public = true 。

2. TKPROF语法： TKPROF *.trc *.txt

其他用法可以直接运行TKPROF查看。一般常用选项就是-sort，可以根据某些参数值排序。

3. 对跟踪文件输出的一些解释：

i. 行：

PARSE阶段：包括了软分析（在SHARED_POOL中找到语句）和硬分析；

EXECUTE阶段：对SELECT几乎为空，对UPDATE则几乎是全部工作的体现；

FETCH阶段：对SELECT是几乎所有的工作，对UPDATE则为空。

ii. 列：

COUNT：事件发生的次数；

CPU：消耗的CPU时间（CPU秒）；

ELAPSED：总体运行时间；

DISK：磁盘物理I/O；

QUERY：一致读模式访问的块数，也包括了从回滚段读取的块数；

CURRENT：访问的当前信息数据块（而不是一致读模式），例如SELECT时读取数据字典内容，修改时也需要访问数据字典内容以写。

ROWS：所涉及的行数。

4. 需要注意的现象：

i. 高的PARSE COUNT/EXECUTE COUNT（接近100%），且EXECUTE COUNT大于1

即执行语句时分析的次数，如果过高，可能是软分析也过多了，对一个会话，应该是分析一次反复执行。

ii. 对几乎所有SQL，EXECUTE COUNT都是1

可能没有使用绑定变量。在一个真实应用中，应该很少看到不同的SQL，同一个SQL应执行多次。

iii. CPU和ELAPSED时间相差较大

说明花了很长时间等待一个事件，例如磁盘I/O、锁等。

iv. (FETCH COUNT)/(ROWS FETCHED)比例高

没有很好的使用批量提取。批量提取数据的方法是和语言/API相关的，例如Pro* C中需要使用prefetch=NN预编译，Java/JDBC下可以调用SETROWPREFETCH方法，PL/SQL可以在SELECT INTO中直接使用BULK COLLECT。而SQL* PLUS缺省为每次取15行。

v. 极大的DISK COUNT

较难推断，但若DISK COUNT = QUERY + CURRENT MODE BLOCK COUNT，则说明几乎所有数据都来自磁盘。此时需要考虑SGA大小和此查询效率。

vi. 极大的QUERY COUNT或CURRENT COUNT

SQL工作量很大，需要注意。

5. EXPLAIN PLAN问题

跟踪文件中显示的是真正执行的路径。TKPROF也支持EXPLAIN=XXX/XXX选项，不建议使用，其输出是转换跟踪文件当时优化器选择的执行路径，并是利用数据库的EXPLAIN工具，与真实路径时不完全一致的。

使用与解析原始跟踪文件

1. EVENTS跟踪

ALTER SESSION SET EVENTS ‘10046 trace name context forever, level N’;

N=1 同标准SQL_TRACE；

N=4 增加获得绑定变量值；

N=8 增加获得查询级的等待事件；

N=12 增加获得绑定变量值和查询级的等待事件。

2. 原始跟踪文件分段解析

文件头含有时间、数据库版本、OS版本、实例名等。

APPNAME mod=’%s’ mh=%lu act=’%s’ ah=%lu

mod	传入DBMS_APPLICATION_INFO的模块名
mh	模块哈希值
act	传入DBMS_APPLICATION_INFO的动作
ah	动作哈希值

Parsing in Cursor #%d dep=%d uid=%ld oct=%d lid=%ld tim=%ld hv=%ld ad=’%s’

Cursor #	游标号。也可以用此值获知应用最大打开的游标数。
len	下面SQL语句的长度
dep	SQL语句的递归（recursive）深度
uid	当前方案的用户ID。注意，这并不一定和后面的lid一致，因为可以用 alter session set current_schema来修改分析时的方案
oct	Oracle命令类型（Oracle Command Type）
lid	用于安全性检查访问权限的用户ID
tim	定时器，1/100秒
ha	SQL语句的哈希ID
ad	V$SQLAREA中此SQL语句的ADDR列

EXEC Cursor#:c=%d,e=%d,p=%d,cr=%d,mis=%d,r=%d,dep=%d,og=%d,tim=%d

Cursor #	游标号
c	CPU时间，1/100秒
e	流逝（Elapsed）时间，1/100秒
p	物理读
cr	一致（QUERY模式）读（逻辑I/O）
cu	当前（Current）模式读（逻辑I/O）
mis	字典缓存中的游标不命中数，说明由于过期已从共享池中清除或从未进入共享池等，而不得不分析此语句
r	处理的行数
dep	SQL语句的递归深度
og	优化器目标：1=ALL ROWS 2=FIRST ROWS 3=RULE 4=CHOOSE
tim	定时器

与EXEC段类似的还有（即取代“EXEC”）：

PARSE	分析一个语句
FETCH	从一个游标取出数据行
UNMAP	用于显示在不需要时从中间结果释放临时段
SORT UMAP	同UNMAP，指排序段

WAIT Cursor#: nam=’%s’ ela=%d p1=%ul p2=%ul p3=%ul

Cursor#	游标号
nam	等待事件名
ela	流逝时间，1/100秒
p1,p2,p3	等待事件特定的参数

以上为文件头与ALTER SESSION出现的跟踪信息。此后开始出现运行的SQL语句。

BIND段

cursor#	游标号
bind N	绑定位置，从0开始
dty	数据类型
mxl	绑定变量最大长度
mal	最大数组长度（当使用数组绑定或BULK操作时）
scl	数值范围（scale）
pre	精度（precision）
oacflg	内部标记。若此值为奇数，则绑定变量可能为NULL（允许为NULL）
oacfl2	内部标记续
size	缓冲区大小
offset	用于逐片（piecewise）绑定
bfp	绑定地址
bln	绑定缓冲区大小
avl	真实值长度
flag	内部标记
value	绑定值的字符串表示（如果可能，会是一个十六进制dump）

其中dty：SELECT text FROM ALL_VIEWS WHERE view_name = ‘USER_VIEWS’ 可看到一个将dty数值转换为字符串表示的函数。

此后我们可以看到WAIT段，即真正的等待事件。

对于ENQUEUE事件，实际就是锁。可用以下函数（传入参数为p1）判断类型：

CREATE OR REPLACE FUNCTION enqueue_decode(l_p 1 in number) return varchar2

l_str varchar2(25);

BEGIN

SELECT CHR(BITAND(l_p1, -16777216) / 16777215) ||

CHR(BITAND(l_p1, 16711680) / 65535) || ‘ ‘ ||

DECODE(BITAND(l_p1, 65535),

0, ‘No lock’,

1, ‘No lock’,

2, ‘Row-Share’,

3, ‘Row-Exclusive’,

4, ‘Share’,

5, ‘Share Row-Excl’,

6, ‘Exclusive’ )

INTO l_str

FROM DUAL;

RETURN l_str;

END;

XCTEND（事务边界）段记录了提交等：

rlbk	回滚标记：0 提交 1 回滚
rd_only	只读标记：0 变化提交或回滚 1 事务只读

STAT段记录了运行时SQL真正的执行计划：

cursor #	游标号
id	执行计划行号
cnt	查询计划中流经此步骤的行数
pid	此步骤的父ID
pos	执行计划中的位置
obj	访问的对象的对象ID
op	操作的文本描述

PARSE ERROR段

len	SQL语句长度
dep	SQL语句递归深度
uid	分析的方案
oct	Oracle命令类型
lid	权限方案ID
tim	定时器
err	ORA错误代码

ERROR段

cursor #	游标数
err	ORA错误代码
tim	定时器

10.5 DBMS_PROFILER

10.6 StatsPack

10.7 V$表

V$EVENT_NAME

说明事件名和p1、p2、p3三个参数。

V$FILESTAT和V$TEMPSTAT

说明系统I/O概况。

V$LOCK

说明系统锁的情况。但注意Oracle并不在外部保存行锁，此视图可以找到TM（DML Enqueue）锁，即说明产生了行锁。

V$MYSTAT

说明当前会话的统计信息。需要V_$STATNAME（不用V$STATNAME，只是V_$STATNAME的一个同义词）和V_$MYSTAT上的SELECT权限。

CREATE VIEW MY_STATS AS

SELECT a.name, b.value

FROM V$STATNAME a, V$MYSTAT b

WHERE a.statistic# = b.statistic#;

V$OPEN_CURSOR

记录所有会话打开的游标。由于Oracle也会缓存已关闭的游标，因此此视图中也会包含已关闭的游标信息。

V$PARAMETER

说明了所有的init.ora参数。

V$SESSION

记录数据库的每个会话。需要对V_$SESSION的SELECT权限。

V$SESSION_EVENT

说明会话的事件情况。

V$SESSION_LONGOPS

记录CBO认为执行时间超过6秒的命令及进展。

V$SESSION_WAIT

记录所有正在等待某事件的会话及已等待时间。

V$SESSTAT

类似V$MYSTAT，但显示所有会话。

V$SESS_IO

说明会话的I/O信息

V$SQL和V$SQLAREA

记录SQL信息。建议使用V$SQL，V$SQLAREA是从V$SQL合并而来的视图，代价较高，对已经繁忙的系统是一个负担。

V$STATNAME

说明了统计号到统计名的映射。

V$SYSSTAT

记录实例层面的统计信息。当数据库关闭时才清空，也是StatsPack很多数据的来源。

V$SYSTEM_EVENT

记录实例层面的等待事件信息。也是StatsPack很多数据的来源。

第 11 章优化器计划稳定性

11.1 概览

CREATE OR REPLACE ONLINE MyOutLine

FOR CATEGORY mycategory

SELECT ……;

需要CREATE OUTLINE权限

使用时指定会话的CATEGORY即可：

ALTER SESSION SET USE_STORED_OUTLINES = mycategory;

11.2 OPS的使用

对已封装的应用中SQL进行的优化方法

ALTER SESSION SET CREATE_STORED_OUTLINES = test;

执行应用，如一个存储过程等

ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

SET LONG 5000

SELECT name, sql_text FROM user_outlines WHERE category = ‘test’;

此时可以看到所运行的SQL语句。也可以通过一个ON LOGON触发器来实现，即一登陆就ALTER SESSION…

优化时修改OPTIMIZER_GOAL后：

ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS;

ALTER OUTLINE name REBUILD;

ALTER SESSION SET OPTIMIZER_GOAL = CHOOSE;

此时就固定为OPTIMIZER_GOAL = FIRST_ROWS时的执行计划了。

一个开发工具

由于开发环境与实际部署环境可能不一致，为了保证执行计划与开发环境一致，可以建立一个ON LOGON触发器来将执行计划归入一个category中，然后exp/imp到新环境中。

用来观察是否使用了索引

SELECT name, hint FROM user_outline_hints

WHERE hink LIKE ‘INDEX%’;

用来观察应用使用了什么SQL语句

11.3 OPS如何工作

OUTLINES与OUTLINE_HITS

均分别有DBA_、USER_、ALL_三张视图，其中DBA_多一个owner字段，说明创建者，另两张与用户有关系。

DBA_OUTLINES：

NAME	OUTLINE名，若创建时未指定，则使用系统命名
OWNER	创建时的方案名
CATEGORY	创建的列别，若未指定则为DEFAULT
USED	是否使用过
TIMESTAMP	创建的时间
VERSION	创建时的数据库版本
SQL_TEXT	SQL查询语句

DBA_OUTLINE_HINTS：

NAME	OUTLINE名，若创建时未指定，则使用系统命名
OWNER	创建时的方案名
NODE	提示应用的层次，从最外层查询（1）开始累加计数
STAGE	提示应用的阶段，即提示在编译的哪个阶段写入
JOIN_POS	提示应用的表名，对非访问方式提示为0
HINT	提示

11.4 创建存储概要

使用DDL

CREATE <OR REPLACE> OUTLINE OUTLINE_NAME

ON STATEMENT

使用ALTER SESSION

ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;

ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

ALTER SESSION SET CREATE_STORED_OUTLINES = mycategory;

当设为TRUE时，所创建的概要归类入DEFAULT。

11.5 OUTLN用户

所有8i数据库中均缺省创建，缺省密码为OUTLN，并可在安装后立即更改。方案含有两个表和一些索引，存放于SYSTEM表空间中，若需要大量使用概要，可用如下方法转移表空间（其中一张表含有LONG类型字段，无法ALTER TABLE MOVE）。

EXP USERID=OUTLN/OUTLN OWNER=OUTLN

ALTER USER OUTLN DEFAULT TABLESPACE tools;

REVOKE UNLIMITED TABLESPACE FROM OUTLN;

ALTER USER OUTLN QUOTA 0K ON SYSTEM;

ALTER USER OUTLN QUOTA UNLIMITED ON tools;

DROP TABLE ol$;

DROP TABLE ol$hints;

IMP USERID=OUTLN/OUTLN FULL=YES

若系统已经使用了概要，则操作应尽量在单用户模式下执行，数据库无其它活动终端用户。

11.6 在数据库间转移概要

EXP USERID=OUTLN/OUTLN QUERY=”where category=’test’” tables=(ol$, ol$hints)

IMP USERID=OUTLN/OUTLN FULL=Y IGNORE=YES

这里也可以使用参数文件来定义导出的查询条件。

11.7 获得正确的概要

有时仅修改某些参数是无法获得所需要的执行计划的，还要添加提示。但概要的使用是基于相同的SQL文本，为了不修改应用但使用添加了提示的执行计划，可以采用如下方法：

例如需要SELECT FROM (SELECT /*+ use_hash(emp) */ FROM emp) emp,

(SELECT /*+ use_hash(dept) */ FROM dept) dept

WHERE emp.deptno=dept.deptno;

则可以在另一个方案中删除emp、dept表，将内层查询语句建立成名为emp和dept的视图，然后对SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno; 建立概要。则此后可以指定应用使用此概要（SQL文本一致）。

这也是利用了OPS是全局的，并不关心所引用对象，而是纯粹根据SQL文本进行转换。

11.8 管理概要

通过DDL

ALTER OUTLINE outline_name RENAME TO new_name;

ALTER OUTLINE outline_name CHANGE CATEGORY TO new_category_name;

ALTER OUTLINE outline_name REBUILD;

DROP OUTLINE outline_name;

OUTLN_PKG包

作用：提供批量管理的功能；提供EXP/IMP的API

由DBMSOL.SQL和PRVTOL.PLB脚本（%ORACLE_HOME%/RDBMS/ADMIN）创建，而这两个脚本由CATPROC.SQL调用并缺省安装到数据库。

DROP_UNUSED – 删除所有类别中所有未使用的概要。

EXEC OUTLN_PKG.DROP_UNUSED;

DROP_BY_CAT – 删除指定类别中的所有概要。

EXEC OUTLN_PKG.DROP_BY_CAT(category_name);

UPDATE_BY_CAT – 重命名一个类别或将其合并入另一个类别。

EXEC OUTLN_PKG.UPDATE_BY_CAT(old_category_name, new_category_name); 若新名已被用，则合并，且若新旧类别存在相同SQL文本的概要，保留新类别中的，而此重复的概要仍保留于原类别中。

11.9 最后说明

创建概要需要CREATE ANY OUTLINE权限，若无权限，利用ALTER SESSION方式来创建概要时不会提示错误，但不会创建概要。

删除用户时即便指定CASCADE选项，也不会删除其创建的概要。

如果CURSOR_SHARING设为force，则用DDL和ALTER SESSION两种方法获得的SQL文本可能是不同的，前者就是输入的SQL，而后者是系统内部已经转换过绑定变量的SQL。

概要的使用依靠文本完全匹配，即便是大小写不同也会造成SQL文本不匹配。

OR扩展问题：由于WHERE条件中有OR的SQL会被改写为UNION ALL模式，概要记录的提示可能无法正常使用，而只是作用到第一个条件上。因此要注意USER_OUTLINE_HINTS表中HINT LIKE ‘USE_CONCAT%’的概要和提示，应当删除或移走。

使用概要对性能影响很小。创建概要时接近首次分析该条语句的时间，此后第一次分析慢于正常分析时间，而随后概要已经进入缓存，将不会观察到性能影响。

11.10 可能遇到的错误

ORA-18001 – 使用ALTER OUTLINE语法错误

ORA-18002 – 所引用的概要不存在（从未创建过或者被删除）

ORA-18003 – 概要的数字签名已存在，数字签名用于快速查找到合适的概要，此错误极少发生

ORA-18004 – 概要已存在，一般是命名冲突

ORA-18005 – 需要CREATE ANY OUTLINE权限

ORA-18006 – 需要DROP ANY OUTLINE权限

ORA-18007 – 需要ALTER ANY OUTLINE权限

第 12 章分析函数

12.1 分析函数如何工作

语法

FUNCTION_NAME(<参数>,…)

OVER

(<PARTITION BY 表达式,…> <ORDER BY 表达式 <ASC | DESC> <NULLS FIRST | NULLS LAST>> <WINDOWING子句>)

PARTITION子句

ORDER BY子句

WINDOWING子句

缺省时相当于RANGE UNBOUNDED PRECEDING

1. 值域窗（RANGE WINDOW）

RANGE N PRECEDING

仅对数值或日期类型有效，选定窗为排序后当前行之前，某列（即排序列）值大于/小于（当前行该列值 –/+ N）的所有行，因此与ORDER BY子句有关系。

2. 行窗（ROW WINDOW）

ROWS N PRECEDING

选定窗为当前行及之前N行。

还可以加上BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n FOLLOWING

函数

AVG(<distinct \| all> expr)	一组或选定窗中表达式的平均值
CORR(expr, expr)	即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))，两个表达式的互相关，-1（反相关） ~ 1（正相关），0表示不相关
COUNT(<distinct> <*> <expr>)	计数
COVAR_POP(expr, expr)	总体协方差
COVAR_SAMP(expr, expr)	样本协方差
CUME_DIST	累积分布，即行在组中的相对位置，返回0 ~ 1
DENSE_RANK	行的相对排序（与ORDER BY搭配），相同的值具有一样的序数（NULL计为相同），并不留空序数
FIRST_VALUE	一个组的第一个值
LAG(expr, <offset>, <default>)	访问之前的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如第一行不存在之前行）
LAST_VALUE	一个组的最后一个值
LEAD(expr, <offset>, <default>)	访问之后的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如最后行不存在之前行）
MAXexpr)	最大值
MIN(expr)	最小值
NTILE(expr)	按表达式的值和行在组中的位置编号，如表达式为4，则组分4份，分别为1 ~ 4的值，而不能等分则多出的部分在值最小的那组
PERCENT_RANK	类似CUME_DIST，1/(行的序数 - 1)
RANK	相对序数，允许并列，并空出随后序号
RATIO_TO_REPORT(expr)	表达式值 / SUM(表达式值)
REGR_ xxxx(expr, expr)	线性回归函数
ROW_NUMBER	排序的组中行的偏移
STDDEV(expr)	标准差
STDDEV_POP(expr)	总体标准差
STDDEV_SAMP(expr)	样本标准差
SUM(expr)	合计
VAR_POP(expr)	总体方差
VAR_SAMP(expr)	样本方差
VARIANCE(expr)	方差

12.2 例子

竖表转横表

一般形式为将一个列为C!, C2, … CN的表，以C1, C2, … CX为基准，将CX+1, … CN的不同值改为列。一般化的语法：

SELECT C1, C2, … CX,

MAX(DECODE(rn, 1, CX+1, NULL)) CX+1_1, … MAX(DECODE(rn, 1, CN, NULL)) CN_1

MAX(DECODE(rn, 2, CX+1, NULL)) CX+1_2, … MAX(DECODE(rn, 2, CN, NULL)) CN_2

…

MAX(DECODE(rn,N,CX+1, NULL)) CX+1_N, … MAX(DECODE(rn,N, CN, NULL)) CN_N

FROM

(SELECT C1, C2, … CN,

ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX ORDER BY <something>) rn

FROM T

WHERE …)

GROUP BY C1, C2, … CX;

通用包：

CREATE OR REPLACE PACKAGE pkg_pivot

TYPE refcursor IS REF CURSOR;

TYPE ARRAY IS TABLE OF VARCHAR2(30);

PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL,

p_max_cols_query IN VARCHAR2 DEFAULT NULL,

p_query IN VARCHAR2,

p_anchor IN ARRAY,

p_pivot IN ARRAY,

p_cursor IN OUT refcursor);

END;

CREATE OR REPLACE PACKAGE BODY pkg_pivot

PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL,

p_max_cols_query IN VARCHAR2 DEFAULT NULL,

p_query IN VARCHAR2,

p_anchor IN ARRAY,

p_pivot IN ARRAY,

p_cursor IN OUT refcursor)

l_max_cols NUMBER;

l_query LONG;

l_cnames ARRAY;

BEGIN

IF (p_max_cols IS NOT NULL)

THEN

EXECUTE IMMEDIATE p_max_cols_query INTO l_max_cols;

ELSE

RAISE_APPLICATION_ERROR(-20001, 'Cannot figure out max cols');

END IF;

l_query := 'select ';

FOR i IN 1 .. p_anchor.count

LOOP

l_query := l_query || p_anchor(i) || ',';

END LOOP ;

FOR i IN 1 .. l_max_cols

LOOP

FOR j IN 1 .. p_pivot.count

LOOP

l_query := l_query || 'max(decode(rn,'||i||','||p_pivot(j)||',null)) '||p_pivot(j) || '_' || i || ',';

END LOOP ;

l_query := RTRIM(l_query,',') || ' from (' || p_query || ') group by ';

FOR i IN 1 .. p_anchor.count

LOOP

l_query := l_query || p_anchor(i) || ',';

END LOOP ;

l_query := RTRIM(l_query,',');

EXECUTE IMMEDIATE 'alter session set cursor_sharing=force';

OPEN p_cursor FOR l_query;

EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact';

END;

其中：

p_max_cols_query为SELECT MAX(COUNT(*)) FROM TABLE_NAME GROUP BY C1, C2, … CX;

p_query为SELECT C1, C2, … CN ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX ORDER BY <something>) rn FROM TABLE_NAME;

p_anchor为pkg_pivot.array(C1, C2, … CX)

p_pivot为pkg_pivot.array(CX+1, CX+2, … CN)

p_cursor为返回的游标。

12.3 最后说明

PL/SQL与分析函数

PL/SQL不支持分析函数的语法，可以通过以下两种方法解决：

１．使用动态游标；

２．将含分析函数的语句创建为视图。

WHERE子句中的分析函数

由于查询仅在最后的ORDER BY子句前执行分析函数，因此WHERE条件中无法使用分析函数，只能利用嵌套循环实现。

第 13 章物化视图

8.1.5 企业版/个人版开始支持

需要权限：GRANT CREATE MATERIALIZED VIEW，还必须直接赋予GRANT QUERY REWRITE。为实现查询重写，必须使用CBO。

13.1 物化视图如何工作

设置

COMPATIBLE参数必须高于 8.1.0

QUERY_REWRITE_ENABLED = TRUE

QUERY_REWRITE_INTEGRETY =

ENFORCED - 查询仅用Oracle强制与保证的约束、规则重写；

TRUSTED – 查询除用Oracle强制与保证的约束、规则，也可用用户设定的数据间的任何关系来重写；

STALE_TOLERATED – 即便Oracle知道物化视图中数据过期（与事实表等不同步），也重写查询。

创建物化视图的用户必须具有直接赋予的GRANT QUERY REWRITE权限，不能通过角色继承。

内部机制

全文匹配

部分匹配：从FROM子句开始，优化器比较之后的文本，然后比较SELECT列表

一般重写方法：

数据充分

关联兼容

分组兼容

聚集兼容

13.2 确保使用物化视图

约束

考虑到现实环境的数据量，可以将主键、外键、非空等约束置为NOVALIDATE，并调整QUERY_REWRITE_INTEGRITY为TRUSTED，这样可以达到“欺骗”数据库的目的，但必须注意如果无法保证此类约束的真实有效，查询改写后可能造成结果不精确。

维度

实际就是指明已存在的表中各列的归并关系，从而关联事实表后形成的物化视图可用于向“上”归并（相当于用表中代表更高归并关系的列关联事实表）。标准语法：

CREATE DIMENSION time_hierarchy_dim

LEVEL day IS time_hierarchy.day

LEVEL mmyyyy IS time_hierarchy.mmyyyy

LEVEL yyyy IS time_hierarchy.yyyy

HIERARCHY time_rollup

(day CHILD OF mmyyyy CHILD OF yyyy)

ATTRIBUTE mmyyyy

DETERMINES mon_yyyy;

13.3 DBMS_OLAP

估计（物化视图）大小

DBMS_OLAP.ESTIMATE_SUMMARY_SIZE(视图名, 视图定义, 估计行数, 估计字节数);

其中后两个参数为NUMBER型输出参数。

维度有效性检查

DBMS_OLAP.VALIDATE_DIMENSION(视图名, 用户名, FALSE, FALSE);

SELECT * FROM 维度表名

WHERE ROWIN IN (SEELCT bad_rowid FROM MVIEW$_EXCEPTION);

所选出行即为不符合维度定义的行。

13.4 最后说明

物化视图不为OLTP系统设计

在事实表等更新时会导致物化视图行锁，从而影响系统并发性。

第 14 章分区

14.1 分区的使用

增加可用性

减轻维护负担

提高DML与查询的性能

14.2 分区如何工作

表分区策略

索引分区

本地索引

分为本地前缀索引（Local Prefixed Index）、本地非前缀索引（Local Non-prefixed Index）

1．索引的选择

在单表查询中，本地非前缀索引可能增加可用性，也更加实用。例如表T(a, b)按a区间分区，若在b上建立本地索引，则当某个分区离线，仅查询b的某个值时，该索引可用，而索引(a, b)不可用；删除索引(a, b)，查询(a, b)的某对值，b上的索引仍可用。此时若建立索引(b, a)，则可应对各类查询。

在多表关联（如上例中按照(a, b)值关联）时，系统将发现代价较高而不会用到本地非前缀索引（如上例中(b, a)）。

因此建立本地索引时应当考虑通常的使用环境。

2．无法基于本地非前缀索引建立唯一键或主键。

全局索引

仅有一种，即全局前缀索引

1. 数据仓库环境

在（与建立好相应索引的表）交换分区与索引或分割分区后，全局索引将失效而必须重建，因此全局索引并不适合数据仓库。

例如：

ALTER TABLE partitioned

EXCHANGE PARTITION fy_1999

WITH TABLE fy_1999

INCLUDING INDEXES

WITHOUT VALIDATION;

ALTER TABLE partitioned

SPLIT PARTITION the_rest

AT (TO_DATE(‘ 200101 ’ , ’yyyymm’))

INTO (PARTITION fy_2000, PARTITION the_rest);

2. OLTP环境

一定程度上增加了可用性。当某些分区离线，不含有用于分区的列且合乎查询条件的数据存在于在线分区的索引仍然是可用的，对于不需要查询全表而是通过索引即可得到结果的查询也是有效的（例如COUNT非用于分区的列等）。

第 15 章自治事务

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

15.1 为何使用自治事务

无法回滚的审计

一般情况下利用触发器禁止某些对表的更新等操作时，若记录日志，则触发器最后抛出异常时会造成日志回滚。利用自治事务可防止此点。

避免变异表

即在触发器中操作触发此触发器的表

在触发器中使用DDL

写数据库

对数据库有写操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的存储过程或函数是无法简单的用SQL来调用的，此时可以将其设为自治事务，从而避免ORA-14552（无法在一个查询或DML中执行DDL、COMMIT、ROLLBACK）、ORA-14551（无法在一个查询中执行DML操作）等错误。需要注意的是函数必须有返回值，但仅有IN参数（不能有OUT或IN/OUT参数）。

开发更模块化的代码

在大型开发中，自治事务可以将代码更加模块化，失败或成功时不会影响调用者的其它操作，代价是调用者失去了对此模块的控制，并且模块内部无法引用调用者未提交的数据。

15.2 如何工作

事务控制

DECLARE整个块都是属于父事务的，自治事务从离PRAGMA后的第一个BEGIN开始，只要此BEGIN块仍在作用域，则都属于自治事务。例如在DECLARE模块中声明一个写数据库的函数，则此函数虽然在自治事务所在存储过程执行，但其属于父事务；而自治事务中调用的任何函数和存储过程、激发的任何触发器等均为此自治事务的一部分。

自治事务可以嵌套，嵌套深度等只受INIT.ORA参数TRANSACTIONS（同时并发的事务数，缺省为SESSIONS的1.1倍）制约。