读完几章，就决定如果有一天写看这本书的感想，就得用这么怪的一个题目。德波顿（Alan de Botton）的这部《亲吻与诉说》（Kiss and Tell）就是这样一部独特的传记。
    传记的对象并不是什么惊天动地的伟人、政治家、科学家、人文学家甚至一个至少听说过的名字，而是一个普通女子，一个叙述者偶然遇到的女子，一个和叙述者似乎发生了点感情的女子（否则大概就不叫kiss了）。虽说老外们喜欢个人主义，从个人而不是集体角度或视野写的文学作品和影视作品（特别是好莱坞的片子里绝不缺乏孤胆英雄）颇为流行，但多少都得塑造出些不凡或者英雄主义。看看现在流行的美剧也可以知道，主人公们总是有些绝招。但是这本书的伊莎贝尔所表现的绝对只是一个凡人的一切，有着普鲁斯特式的回忆、混乱的生活、粗俗的习惯……
    作者对传记的第二个颠覆来自于他所写的内容。整个传记虽然从几个方面写出了并且写活了一个人，但是同样是写童年，作者着力于伊莎贝尔对童年的部分记忆，童年的一些令人尴尬的瞬间；谈到记忆，伊莎贝尔的记忆也是不完整的，总是所谓普鲁斯特式的，可能只是一件家庭事件中自己的奇异的感受；心理方面，叙述者开始试图通过心理测试题了解她，后来则是研究伊莎贝尔的一些细微而怪异习惯；叙述者甚至用了一章来讲饮食，伊莎贝尔的饮食习惯和养成，以及可能对她心理的反映或者影响……整篇虽然一反传记往常的客观（仿佛旁观），采用一个与被叙述者纠缠着的叙述者角度，添加了自己的影子，颇有新意。但是更富有革命意义的是，作者在叙述的同时夹杂了大量对传记这种文体写法的阐述，仿佛本篇传记倒在其次，只是作为自己对传记写法理解的illustration。这也就是为什么我觉得这是一篇传记学。在阐述中，作者对传统传记客观的写法、热衷写大事等等进行了批判，提出了人被小事和偶然性所左右的可能。
    这种议论+叙述（不说是叙述+议论，因为我还是觉得议论部分更睿智）的写法在之前国内翻译出版的他的《旅行的艺术》（实际是之后写的）里也充分运用。我想，能将两种笔法都运用自如糅合无缝的作者，应当是可以担当各类书评中所说的英伦怪才之说的。《男人和女人》一章的对话描写，充分体现了其文笔之精妙，建议一读。
    总体来说，读《译林》上的小说如看美国大片（事实上不少改编成了好莱坞快餐），读这本书的感觉则像看了部法国片，温情、真实而细腻。所以建议不喜欢法国片的就不要看了，否则你看了一部分就会把它扔了。对文学本身没有兴趣的也还是换本书吧，因为从头到尾真的没讲什么。不过我想我还是会买德波顿的下一本书的。

今天读HBR(Harvard Business Review)2006年9月号，看到了Robert F. Hurley的The Decision to Trust，讲述了他研究出的信任的模型，也就是当我们决定是否信任他人时是如何做出决定的，受哪些因素影响。这是一个很有意思的研究，作者经过一段时间的跟踪等等，找出了影响我们的十个因素，分为两大类。
    一是决定者因素（Decision-Maker Factors），也就是做出决定的我们自身的个性。主要是三个方面：
    第一是我们对风险的容忍度（Risk Tolerance），显然越趋向于冒险的会越容易信任他人，风险规避者则较少轻信；
    第二是调整能力（Level of Adjustment），越容易适应环境的人更容易相信别人，因为这种人对好的和坏的结果都可以很快接受并适应；
    第三是相对权力（Relative Power），如果自己更权威或者权力更大，就越放心信任别人，因为如果有不好的结果你可以有相应的惩罚措施，而相反你根本无法控制你的信任对象，那你肯定会三思后行。

    二是环境因素（Situational Factors），这里就涉及了信任对象的情况和我们与之的关系等。
    第一是安全性（Security），当我们工作可能不保时，我们还会信任老板么？
    第二是相似性（Number of Similarities），我们总是喜欢某些方面和我们一致的人，比如喝同样的茶、喜欢同样的乐队、穿衣服风格一致，甚至只是同一性别的。有的HR招人的时候喜欢找校友，其实潜意识里应该也有这样的因素，一个穿衣服不合你口味、毕业于你没听说过的学校的申请者，恐怕很难让你相信他的能力。所以有的单位同事间有很多共同点时，工作效率是比较高的，更容易形成合力。
    第三是利益倾向性（Alignment of Interests），有共同利益的，那叫风雨同舟嘛。
    第四是善意的关心（Benevolent Concern），这里我个人倒觉得和前一点比较近似，不过在无法达成利益一致时，让对方明白你愿意站在他的立场想问题还是很有好处的。当然文中作者也举了一个极端的例子。企业主要保障员工的利益，但是反而引来了银行的不信任，导致资金链出现了问题，最终破产。这里就是一个多方利益的均衡问题了。
    第五是能力（Capability），信任对象有能力完成我们对他的信任，才能获取我们的信任，不是么？
    第六，可预见性和一致性（Predictability and Integrity），这个和对风险的容忍度有些类似，但这个是从信任对象方面来说的。当一个信任对象尽管乐于承诺，但实际收效经常低于承诺，那又会有谁信他呢？
    第七，也是最后一点，沟通（Level of Communication），这一点也毋庸置疑了，有效的沟通才能拉近距离啊。

    总体来说，作者虽然没有提出新的观点，但是对几方面的总结还是很到位的，也肯定了这几方面的决定作用。在文章的后1/3还给出了一个作者职业生涯中的例子和一个美国能源公司处理有色人种歧视问题的案件来演示如何从这10点来提升企业内部的信任度，包括人与人之间和人与企业之间。这样的文章和总结在社会危机感加重、政府公信力下降的中国，应该也是一个很大的启示吧。

    这本书前几年引进的时候还是挺红火的，毕竟国内市场严肃音乐类的书并不多，到dangdang、china-pub搜索就知道，音乐类的书，大多就是乐谱和歌曲集的同义词。我买它也有两年了，一直没有看完，前段时间读《纯粹理性批判》有点郁闷，就又重新开始，今天单位开大会就抽空读完了。当时买一是因为作者是梅纽因，一是因为它似乎是一部音乐简史，而我当时正缺音乐史的书。
    这本书是衍生自一个电视节目的讲解词，号称是“人类的音乐”，但是着重点是在西方古典音乐（特别是欧美，因为涉及俄罗斯等很少）和美洲、非洲的民间音乐，之间的关系、区别、各自不同的审美，都贯穿着整本书。看过书评说这本书的特点是“关注人性”，避免了大量的术语、五线谱。我想这主要还是因为它是作为一个普及严肃音乐的电视节目而存在的。但是这决不是说这本书就很容易读。首先，它是一部简史，对每个作曲家涉猎并不多，最多3-4页纸，并且非常抽象，除非你听过所举的曲目，否则你要理解起来最好的选择还是去找一个版本来听；其次，它涉及了历史、社会学、宗教等等很多背景知识，而且我个人觉得它更倾向于从音乐学的角度来看问题，比如调性的演变、小节线的衍生等等，没有一定的背景，恐怕不是那么容易被普及的；最后，正是因为它原本是电视节目的一部分，很多东西如果有音乐、图像来配合可能会容易明白很多，中间还插入了三篇访谈，如果是配上当时的录影，恐怕更方便。正如出版说明里就说的，有些图片在翻译引进的时候拿不到版权，只能割爱删除，这确实是一个大的损失。
    有些事情就是这样，上面不容易的地方也反过来是其与众不同的地方，比如看问题的角度，比如相对普及但又非常有自己的学术观点。对作曲家的生平也都略有讲解，取舍完全取决于对其音乐的影响，行文取舍如此准确也是非常难得的。
    不过看完以后的感觉是看一部相对完整的音乐史是更为必要的了。这本书过于简略了，并且是充满了梅纽因自己的观点，最突出的一点就是对小提琴的地位叙述非常之多，对键盘音乐，作者虽然也不得不承认后来成为作曲家的首选（和声的发展注定的），但篇幅相当之少。另外作者为了控制长度吧，只选择了一些他个人认为属于转折点的作曲家，但即便这样也给了不少篇幅写当代一些作曲家，比如巴托克、施托克豪森等，居然很少提及俄罗斯作曲家，只略微带过了柴可夫斯基和斯特拉文斯基，肖斯塔科维奇等提都没提，似乎有点偏颇了。在全书也稍微提及了爵士、摇滚等，对摇滚也更多的是表达了自己的不解而已，其实就那么一点篇幅，不如不讲。
    总体来说，想看音乐史的，可以忽略这本书；对梅纽因有兴趣的，不妨一读。

实验来自Cost Based Oracle Fundamentals第一章，说明了8i至9i在处理merge view时的差异。由于环境所限，没办法在8i上测试，以下第一部分是9.2.0.1上的全部测试过程（注意红色部分是缺省配置下的执行计划），之后是10.2.0.2上相关的变化。
其实主要的区别一个是_complex_view_merging在8i上缺省是false，也就是不会打开视图重写语句；而该参数在9i和10g上都缺省是true，允许重写语句。
其二是CBO在8i上受上面参数的影响，不会考虑打开视图重写语句；在9i上则走到了另一个极端，只要允许（视图中含有GROUP BY或SELECT列表中含有DISTICNT）进行重写，一律重写；10g上则是会进行COST的比较（这必须trace 10053事件了，这里并不能看出），找合算的做。

SQL>
SQL> begin
2 begin execute immediate 'purge recyclebin';
3 exception when others then null;
4 end;
5
6 begin execute immediate 'begin dbms_stats.delete_system_stats; end;';
7 exception when others then null;
8 end;
9
10 begin execute immediate 'alter session set "_optimizer_cost_model"=io';
11 exception when others then null;
12 end;
13
14 end;
15 /

PL/SQL 过程已成功完成。

SQL>
SQL> create table t1 (
2 id_par number(6) not null,
3 vc1 varchar2(32) not null,
4 vc2 varchar2(32) not null,
5 padding varchar2(100)
6 );

表已创建。

SQL>
SQL> alter table t1 add constraint t1_pk primary key (id_par);

表已更改。

SQL>
SQL> create table t2 (
2 id_ch number(6) not null,
3 id_par number(6) not null,
4 val number(6,2),
5 padding varchar2(100)
6 );

表已创建。

SQL>
SQL> alter table t2 add constraint t2_pk primary key (id_ch);

表已更改。

SQL> alter table t2 add constraint t2_fk_t1 foreign key (id_par) references t1;

表已更改。

SQL>
SQL> insert into t1
2 select
3 rownum,
4 vc1,
5 vc2,
6 rpad('x',100)
7 from
8 (
9 select
10 lpad(trunc(sqrt(rownum)),32) vc1,
11 lpad(rownum,32) vc2
12 from all_objects
13 where rownum <= 32
14 )
15 ;

已创建32行。

SQL>
SQL> commit;

提交完成。

SQL>
SQL> insert into t2
2 select
3 rownum,
4 d1.id_par,
5 rownum,
6 rpad('x',100)
7 from
8 t1 d1,
9 t1 d2
10 ;

已创建1024行。

SQL>
SQL> commit;

提交完成。

SQL>
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 't1',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 1'
8 );
9 end;
10 /

PL/SQL 过程已成功完成。

SQL>
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 't2',
5 cascade => true,
6 estimate_percent => null,
7 method_opt => 'for all columns size 1'
8 );
9 end;
10 /

PL/SQL 过程已成功完成。

SQL>
SQL> create or replace view avg_val_view AS
2 select
3 id_par, avg(val) avg_val_t1
4 from t2
5 group by
6 id_par;

视图已建立。

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> prompt

SQL> prompt Baseline example
Baseline example
SQL> prompt Default value for _complex_view_merging
Default value for _complex_view_merging
SQL> prompt Query unhinted
Query unhinted
SQL> prompt

SQL>
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=23 Bytes=1909)
1 0 SORT (GROUP BY) (Cost=7 Card=23 Bytes=1909)
2 1 HASH JOIN (Cost=5 Card=32 Bytes=2656)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=76)
4 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1024 Bytes=7168)



SQL>
SQL> alter session set "_complex_view_merging"=true;

会话已更改。

SQL>
SQL> prompt

SQL> prompt Complex view merging enabled
Complex view merging enabled
SQL> prompt Query unhinted
Query unhinted
SQL> prompt

SQL>
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=23 Bytes=1909)
1 0 SORT (GROUP BY) (Cost=7 Card=23 Bytes=1909)
2 1 HASH JOIN (Cost=5 Card=32 Bytes=2656)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=76)
4 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1024 Bytes=7168)



SQL>
SQL> prompt

SQL> prompt Complex view merging enabled
Complex view merging enabled
SQL> prompt no_merge hint applied
no_merge hint applied
SQL> prompt

SQL>
SQL> select
2 /*+ no_merge (avg_val_view) */
3 t1.vc1, avg_val_t1
4 from
5 t1, avg_val_view
6 where
7 t1.vc2 = lpad(18,32)
8 and avg_val_view.id_par = t1.id_par
9 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=95)
1 0 HASH JOIN (Cost=8 Card=1 Bytes=95)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)
3 1 VIEW OF 'AVG_VAL_VIEW' (Cost=5 Card=32 Bytes=832)
4 3 SORT (GROUP BY) (Cost=5 Card=32 Bytes=224)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1024 Bytes=7168)



SQL>
SQL>
SQL> alter session set "_complex_view_merging"=false;

会话已更改。

SQL>
SQL> prompt

SQL> prompt Complex view merging disabled
Complex view merging disabled
SQL> prompt Query unhinted
Query unhinted
SQL> prompt

SQL>
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=95)
1 0 HASH JOIN (Cost=8 Card=1 Bytes=95)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)
3 1 VIEW OF 'AVG_VAL_VIEW' (Cost=5 Card=32 Bytes=832)
4 3 SORT (GROUP BY) (Cost=5 Card=32 Bytes=224)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1024 Bytes=7168)



SQL>
SQL>
SQL> prompt

SQL> prompt Complex view merging disabled
Complex view merging disabled
SQL> prompt Query hinted to merge - doesn't work
Query hinted to merge - doesn't work
SQL> prompt

SQL>
SQL> select
2 /*+ merge(avg_val_view) */
3 t1.vc1, avg_val_t1
4 from
5 t1, avg_val_view
6 where
7 t1.vc2 = lpad(18,32)
8 and avg_val_view.id_par = t1.id_par
9 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=95)
1 0 HASH JOIN (Cost=8 Card=1 Bytes=95)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=69)
3 1 VIEW OF 'AVG_VAL_VIEW' (Cost=5 Card=32 Bytes=832)
4 3 SORT (GROUP BY) (Cost=5 Card=32 Bytes=224)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1024 Bytes=7168)



SQL>
SQL>
SQL> set autotrace off
SQL>
SQL> alter session set "_complex_view_merging"=true;

会话已更改。

SQL>
SQL> spool off

下面是10.2.0.2上的测试：
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;

执行计划
----------------------------------------------------------
Plan hash value: 3226881135

------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 2024 | 7 |
| 1 | HASH GROUP BY | | 23 | 2024 | 7 |
|* 2 | HASH JOIN | | 32 | 2816 | 5 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 81 | 2 |
| 4 | TABLE ACCESS FULL| T2 | 1024 | 7168 | 2 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID_PAR"="T1"."ID_PAR")
3 - filter("T1"."VC2"=' 18')

Note
-----
- cpu costing is off (consider enabling it)

SQL>
SQL> alter session set "_complex_view_merging"=true;

会话已更改。

SQL>
SQL> prompt

SQL> prompt Complex view merging enabled
Complex view merging enabled
SQL> prompt Query unhinted
Query unhinted
SQL> prompt

SQL>
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;

执行计划
----------------------------------------------------------
Plan hash value: 3226881135

------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 2024 | 7 |
| 1 | HASH GROUP BY | | 23 | 2024 | 7 |
|* 2 | HASH JOIN | | 32 | 2816 | 5 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 81 | 2 |
| 4 | TABLE ACCESS FULL| T2 | 1024 | 7168 | 2 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID_PAR"="T1"."ID_PAR")
3 - filter("T1"."VC2"=' 18')

Note
-----
- cpu costing is off (consider enabling it)

SQL>
SQL> prompt

SQL> prompt Complex view merging enabled
Complex view merging enabled
SQL> prompt no_merge hint applied
no_merge hint applied
SQL> prompt

SQL>
SQL> select
2 /*+ no_merge (avg_val_view) */
3 t1.vc1, avg_val_t1
4 from
5 t1, avg_val_view
6 where
7 t1.vc2 = lpad(18,32)
8 and avg_val_view.id_par = t1.id_par
9 ;

执行计划
----------------------------------------------------------
Plan hash value: 2162534217

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 8 |
|* 1 | HASH JOIN | | 1 | 95 | 8 |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 69 | 2 |
| 3 | VIEW | AVG_VAL_VIEW | 32 | 832 | 5 |
| 4 | HASH GROUP BY | | 32 | 224 | 5 |
| 5 | TABLE ACCESS FULL| T2 | 1024 | 7168 | 2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
2 - filter("T1"."VC2"=' 18')

Note
-----
- cpu costing is off (consider enabling it)

SQL>
SQL>
SQL> alter session set "_complex_view_merging"=false;

会话已更改。

SQL>
SQL> prompt

SQL> prompt Complex view merging disabled
Complex view merging disabled
SQL> prompt Query unhinted
Query unhinted
SQL> prompt

SQL>
SQL> select
2 t1.vc1, avg_val_t1
3 from
4 t1, avg_val_view
5 where
6 t1.vc2 = lpad(18,32)
7 and avg_val_view.id_par = t1.id_par
8 ;

执行计划
----------------------------------------------------------
Plan hash value: 2162534217

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 8 |
|* 1 | HASH JOIN | | 1 | 95 | 8 |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 69 | 2 |
| 3 | VIEW | AVG_VAL_VIEW | 32 | 832 | 5 |
| 4 | HASH GROUP BY | | 32 | 224 | 5 |
| 5 | TABLE ACCESS FULL| T2 | 1024 | 7168 | 2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
2 - filter("T1"."VC2"=' 18')

Note
-----
- cpu costing is off (consider enabling it)

SQL>
SQL>
SQL> prompt

SQL> prompt Complex view merging disabled
Complex view merging disabled
SQL> prompt Query hinted to merge - doesn't work
Query hinted to merge - doesn't work
SQL> prompt

SQL>
SQL> select
2 /*+ merge(avg_val_view) */
3 t1.vc1, avg_val_t1
4 from
5 t1, avg_val_view
6 where
7 t1.vc2 = lpad(18,32)
8 and avg_val_view.id_par = t1.id_par
9 ;

执行计划
----------------------------------------------------------
Plan hash value: 2162534217

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 8 |
|* 1 | HASH JOIN | | 1 | 95 | 8 |
|* 2 | TABLE ACCESS FULL | T1 | 1 | 69 | 2 |
| 3 | VIEW | AVG_VAL_VIEW | 32 | 832 | 5 |
| 4 | HASH GROUP BY | | 32 | 224 | 5 |
| 5 | TABLE ACCESS FULL| T2 | 1024 | 7168 | 2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
2 - filter("T1"."VC2"=' 18')

Note
-----
- cpu costing is off (consider enabling it)

SQL>

第 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的负面：破坏了依赖链、代码更脆弱、很难调优。

第 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:\oracle\RDBMS\demo\sadvdemo后执行：

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非用于分区的列等）。

第 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

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 <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条件中无法使用分析函数，只能利用嵌套循环实现。

第 11 章 优化器计划稳定性 11.1 概览

CREATE OR REPLACE ONLINE MyOutLine

FOR CATEGORY mycategory

ON

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 创建存储概要 相关的权限

CREATE ANY OUTLINE – 创建概要，否则报ORA-18005错误

ALTER ANY OUTLINE – 修改或重新计算概要

DROP ANY OUTLINE – 删除概要

EXECUTE ON OUTLN_PKG – 执行OUTLINE包

注意这里权限都是全局的，概要不存在真正的所有者。

使用DDL

CREATE <OR REPLACE> OUTLINE OUTLINE_NAME

<FOR CATEGORY CATEGORY_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权限

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

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

CREATE OR REPLACE FUNCTION enqueue_decode(l_p1 in number) return varchar2

AS

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很多数据的来源。

使用与解析原始跟踪文件

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数值转换为字符串表示的函数。

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工具，与真实路径时不完全一致的。

装载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’7C0D0A’”而UNIX平台是”str X’7C0A’”，其次可以将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来提高效率，仅能作为权宜之计

卸载数据

CREATE OR REPLACE PACKAGE UNLOADER

AS

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

AS

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

IS

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 )

IS

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;

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

IS

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

IS

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;

/

第 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. 内嵌的换行符换行

第 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不会如此。唯一方法是事先建立类似表空间结构的表再导入。

第 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，从而可以重用，但和堆组织表不同，即便只有一个索引，也会占据一个块。

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

AS

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

AS

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$是内嵌对象类型的标量实现。

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和嵌套表本身中索引的额外开销。