One-on-One Oracle阅读笔记2(转)
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子句
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( expr)
一组或选定窗中表达式的平均值
CORR(expr,
expr)
即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) *
STDDEV_POP(expr2))，两个表达式的互相关，-1（反相关） ~ 1（正相关），0表示不相关
COUNT( <*> )
计数
COVAR_POP(expr, expr)
总体协方差
COVAR_SAMP(expr, expr)
样本协方差
CUME_DIST
累积分布，即行在组中的相对位置，返回0 ~ 1
DENSE_RANK
行的相对排序（与ORDER BY搭配），相同的值具有一样的序数（NULL计为相同），并不留空序数
FIRST_VALUE
一个组的第一个值
LAG(expr,
, )
访问之前的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如第一行不存在之前行）
LAST_VALUE
一个组的最后一个值
LEAD(expr,
, )
访问之后的行，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) rn
FROM T
WHERE …)
GROUP BY C1, C2, … CX;
通用包：
CREATE OR REPLACE PACKAGE
pkg_pivot
AS
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
AS
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)
AS
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;
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;
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) 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);
所选出行即为不符合维度定义的行。
推荐物化视图
首先必须添加合适的外键，包通过外键来判定表之间的关系而不是维度。
DBMS_OLAP.RECOMMEND_MV(事实表名, 1000000000, ‘’);
第二个参数表示物化视图可用的空间大小，可传入一个较大的数。第三个参数传入需要保留的特定物化视图，传入空即为不考虑其他物化视图。
执行C:oracleRDBMSdemosadvdemo后执行：
DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS
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倍）制约。
作用域
1. 包中的变量
自治事务可看到并修改父事务的变量，父事务也会察觉到这一改变，且不存在回滚问题。
2. 会话设置/参数
自治事务与父事务共享同一个会话环境，通过ALTER SESSION作的修改对整个会话均有效。但SET TRANSACTION是事务级的，仅对提起修改的事务有效。
3. 数据库修改
父事务已提交的修改对自治事务可见，未提交的对自治事务不可见，自治事务的修改对父事务是否可见取决于隔离级别（Isolation Level）。
对于游标，取决于其打开的位置，若其在父事务中打开，则之前父事务未提交的修改对其是有效的，在自治事务中这些修改也可见；而在自治事务中打开，则父事务未提交的修改不可见。
若使用缺省的READ COMMITTED隔离级别，则自治事务的修改对父事务可见；若改用SERIALIZABLE，则不可见。
4. 锁
父事务与自治事务是完全不同的事务，因此无法共享锁等。
结束一个自治事务
必须提交一个COMMIT、ROLLBACK或执行DDL。
保存点
无法在自治事务中回滚到父事务中的一个保存点，只能在内部使用保存点。
15.3 最后说明
不支持分布式事务
截至8.1.7在自治事务中不支持分布式事务
仅可用PL/SQL
全部事务回滚
若自治事务出错，则全部回滚，即便父事务有异常处理模块。
事务级临时表
每个会话仅一个事务可访问事务级临时表（多个会话中的事务可并发操作）。
变异表
15.4 可能遇到的错误
ORA-06519 – 检查到活动自治事务，回滚——退出自治事务时没有提交、回滚或DDL操作
ORA-14450 – 试图访问正在使用的事务级临时表
ORA-00060 – 等待资源时检查到死锁
第 16 章 动态SQL
16.1 为何使用动态SQL
实现动态SQL有两种方式：DBMS_SQL和本地动态SQL（EXECUTE IMMEIDATE）
主要从以下方面考虑使用哪种方式：
1. 是否知道涉及的列数和类型
DBMS_SQL包括了一个可以“描述”结果集的存储过程（DBMS_SQL.DESCRIBE_COLUMNS），而本地动态SQL没有。
2. 是否知道可能涉及的绑定变量数和类型
DBMS_SQL允许过程化的绑定语句的输入，而本地动态SQL需要在编译时确定。
3. 是否使用“数组化”操作（Array
Processing）
DBMS_SQL允许，而本地动态SQL基本不可以，但可以用其他方式实现（对查询可用FETCH BULK
COLLECT INTO，对INSERT等，可用一个BEGIN …
END块中加循环实现）。
4. 是否在同一个会话中多次执行同一语句
DBMS_SQL可以分析一次执行多次，而本地动态SQL会在每次执行时进行软分析。
5. 是否需要用REF
CURSOR返回结果集
仅本地动态SQL可用REF
CURSOR返回结果集。
16.2 如何使用动态SQL
DBMS_SQL
1. 调用OPEN_CURSOR获得一个游标句柄；
2. 调用PARSE分析语句。一个游标句柄可以用于多条不同的已分析语句，但一个时间点仅一条有效；
3. 调用BIND_VARIABLE或BIND_ARRAY来提供语句的任何输入；
4. 若是一个查询（SELECT语句），调用DIFINE_COLUMN或DEFINE_ARRAY来告知Oracle如何返回结果；
5. 调用EXECUTE执行语句；
6. 若是一个查询，调用FETCH_ROWS来读取数据。可以使用COLUMN_VALUE从SELECT列表根据位置获得这些值；
7. 否则，若是一个PL/SQL块或带有RETURN子句的DML语句，可以调用VARIABLE_VALUE从块中根据变量名获得OUT值；
8. 调用CLOSE_CURSOR。
注意这里对任何异常都应该处理，以关闭游标，防止泄露资源。
本地动态SQL
EXECUTE
IMMEDIATE ‘语句’
[INTO
{变量1,
变量2, …
变量N |
记录体}]
[USING [IN
| OUT | IN OUT] 绑定变量1, …
绑定变量N]
[{RETURNING
| RETURN} INTO 输出1 [, …,
输出N]…];
注意本地动态SQL仅支持弱类型REF
CURSOR，即对于REF CURSOR，不支持BULK
COLLECT。
16.3 最后说明
动态SQL的负面：破坏了依赖链、代码更脆弱、很难调优。
第 17
章
interMedia
第 18 章 基于C的外部过程
创建概要需要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子句
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(
一组或选定窗中表达式的平均值
CORR(expr,
expr)
即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) *
STDDEV_POP(expr2))，两个表达式的互相关，-1（反相关） ~ 1（正相关），0表示不相关
COUNT(
计数
COVAR_POP(expr, expr)
总体协方差
COVAR_SAMP(expr, expr)
样本协方差
CUME_DIST
累积分布，即行在组中的相对位置，返回0 ~ 1
DENSE_RANK
行的相对排序（与ORDER BY搭配），相同的值具有一样的序数（NULL计为相同），并不留空序数
FIRST_VALUE
一个组的第一个值
LAG(expr,
访问之前的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如第一行不存在之前行）
LAST_VALUE
一个组的最后一个值
LEAD(expr,
访问之后的行，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
FROM T
WHERE …)
GROUP BY C1, C2, … CX;
通用包：
CREATE OR REPLACE PACKAGE
pkg_pivot
AS
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
AS
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)
AS
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;
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;
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
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);
所选出行即为不符合维度定义的行。
推荐物化视图
首先必须添加合适的外键，包通过外键来判定表之间的关系而不是维度。
DBMS_OLAP.RECOMMEND_MV(事实表名, 1000000000, ‘’);
第二个参数表示物化视图可用的空间大小，可传入一个较大的数。第三个参数传入需要保留的特定物化视图，传入空即为不考虑其他物化视图。
执行C:oracleRDBMSdemosadvdemo后执行：
DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS
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倍）制约。
作用域
1. 包中的变量
自治事务可看到并修改父事务的变量，父事务也会察觉到这一改变，且不存在回滚问题。
2. 会话设置/参数
自治事务与父事务共享同一个会话环境，通过ALTER SESSION作的修改对整个会话均有效。但SET TRANSACTION是事务级的，仅对提起修改的事务有效。
3. 数据库修改
父事务已提交的修改对自治事务可见，未提交的对自治事务不可见，自治事务的修改对父事务是否可见取决于隔离级别（Isolation Level）。
对于游标，取决于其打开的位置，若其在父事务中打开，则之前父事务未提交的修改对其是有效的，在自治事务中这些修改也可见；而在自治事务中打开，则父事务未提交的修改不可见。
若使用缺省的READ COMMITTED隔离级别，则自治事务的修改对父事务可见；若改用SERIALIZABLE，则不可见。
4. 锁
父事务与自治事务是完全不同的事务，因此无法共享锁等。
结束一个自治事务
必须提交一个COMMIT、ROLLBACK或执行DDL。
保存点
无法在自治事务中回滚到父事务中的一个保存点，只能在内部使用保存点。
15.3 最后说明
不支持分布式事务
截至8.1.7在自治事务中不支持分布式事务
仅可用PL/SQL
全部事务回滚
若自治事务出错，则全部回滚，即便父事务有异常处理模块。
事务级临时表
每个会话仅一个事务可访问事务级临时表（多个会话中的事务可并发操作）。
变异表
15.4 可能遇到的错误
ORA-06519 – 检查到活动自治事务，回滚——退出自治事务时没有提交、回滚或DDL操作
ORA-14450 – 试图访问正在使用的事务级临时表
ORA-00060 – 等待资源时检查到死锁
第 16 章 动态SQL
16.1 为何使用动态SQL
实现动态SQL有两种方式：DBMS_SQL和本地动态SQL（EXECUTE IMMEIDATE）
主要从以下方面考虑使用哪种方式：
1. 是否知道涉及的列数和类型
DBMS_SQL包括了一个可以“描述”结果集的存储过程（DBMS_SQL.DESCRIBE_COLUMNS），而本地动态SQL没有。
2. 是否知道可能涉及的绑定变量数和类型
DBMS_SQL允许过程化的绑定语句的输入，而本地动态SQL需要在编译时确定。
3. 是否使用“数组化”操作（Array
Processing）
DBMS_SQL允许，而本地动态SQL基本不可以，但可以用其他方式实现（对查询可用FETCH BULK
COLLECT INTO，对INSERT等，可用一个BEGIN …
END块中加循环实现）。
4. 是否在同一个会话中多次执行同一语句
DBMS_SQL可以分析一次执行多次，而本地动态SQL会在每次执行时进行软分析。
5. 是否需要用REF
CURSOR返回结果集
仅本地动态SQL可用REF
CURSOR返回结果集。
16.2 如何使用动态SQL
DBMS_SQL
1. 调用OPEN_CURSOR获得一个游标句柄；
2. 调用PARSE分析语句。一个游标句柄可以用于多条不同的已分析语句，但一个时间点仅一条有效；
3. 调用BIND_VARIABLE或BIND_ARRAY来提供语句的任何输入；
4. 若是一个查询（SELECT语句），调用DIFINE_COLUMN或DEFINE_ARRAY来告知Oracle如何返回结果；
5. 调用EXECUTE执行语句；
6. 若是一个查询，调用FETCH_ROWS来读取数据。可以使用COLUMN_VALUE从SELECT列表根据位置获得这些值；
7. 否则，若是一个PL/SQL块或带有RETURN子句的DML语句，可以调用VARIABLE_VALUE从块中根据变量名获得OUT值；
8. 调用CLOSE_CURSOR。
注意这里对任何异常都应该处理，以关闭游标，防止泄露资源。
本地动态SQL
EXECUTE
IMMEDIATE ‘语句’
[INTO
{变量1,
变量2, …
变量N |
记录体}]
[USING [IN
| OUT | IN OUT] 绑定变量1, …
绑定变量N]
[{RETURNING
| RETURN} INTO 输出1 [, …,
输出N]…];
注意本地动态SQL仅支持弱类型REF
CURSOR，即对于REF CURSOR，不支持BULK
COLLECT。
16.3 最后说明
动态SQL的负面：破坏了依赖链、代码更脆弱、很难调优。
第 17
章
interMedia
第 18 章 基于C的外部过程
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/756652/viewspace-242223/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
三国中最精辟的十句话
下一篇：
oracle嵌套表
请登录后发表评论
登录
全部评论
