update 子查询使用介绍
<p>基础知识<br><strong>1， 关联子查询和非关联子查询</strong></p>
<p>
在非关联子查询中，内部查询只执行一次并返回它的值给外部查询，然后外部查询在它的处理中使用内部查询返回给它的值。而在关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。</p>
<p>
如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_627135">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">o1.CustomerID, o1.OrderID, o1.OrderDate</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">Orders o1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">o1.OrderDate = (</code><code class="sql keyword">SELECT</code> <code class="sql keyword">Max</code><code class="sql plain">(OrderDate)</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">Orders o2</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">o2.CustomerID = o1.CustomerID)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
是一个关联子查询</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_992171">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql plain">o1.CustomerID, o1.OrderID, o1.OrderDate</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">Orders o1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">o1.OrderDate </code><code class="sql color1">IN</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">(</code><code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">2 o2.OrderDate</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">FROM</code> <code class="sql plain">Orders o2</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">WHERE</code> <code class="sql plain">o2.CustomerID = o1.CustomerID)</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">CustomerID</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
是一个非关联子查询</p>
<p>
<strong>2， 提示（HINT）</strong></p>
<p>
一般在优化时，无论采用基于规则的或是基于代价的方法，由Oracle系统的优化器来决定语句的执行路径。这样的选择的路径不要见得是最好的。所以，Oracle提供了一种方法叫提示的方法。它可以让编程人员按照自己的要求来选择执行路径，即提示优化器该按照什么样的执行规则来执行当前的语句。这样可以在性能上比起Oracle优化自主决定要好些。</p>
<p>
通常情况下，编程人员可以利用提示来进行优化决策。通过运用提示可以对下面内容进行指定：</p>
<p>
<br>
l SQL语句的优化方法；</p>
<p>
l 对于某条SQL语句，基于开销优化程序的目标；</p>
<p>
l SQL语句访问的访问路径；<br>
l 连接语句的连接次序；<br>
l 连接语句中的连接操作。</p>
<p>
如果希望优化器按照编程人员的要求执行，则要在语句中给出提示。提示的有效范围有限制，即有提示的语句块才能按照提示要求执行。下面语句可以指定提示：</p>
<p>
l 简单的SELECT ,UPDATE ,DELETE 语句；<br>
l 复合的主语句或子查询语句；<br>
l 组成查询（UNION）的一部分。</p>
<p>
提示的指定有原来的注释语句在加“+”构成。语法如下：</p>
<p>
[ SELECT | DELETE|UPDATE ] /*+ */</p>
<p>
或</p>
<p>
[ SELECT | DELETE|UPDATE ] --+ </p>
<p>
注意在“/*”后不要空就直接加“+”，同样 “--+”也是连着写。</p>
<p>
警告：如果该提示语句书写不正确，则Oracle就忽略掉该语句。</p>
<p>
常见的提示有：</p>
<p>
Ordered 强制按照from子句中指定的表的顺序进行连接<br>
Use_NL 强制指定两个表间的连接方式为嵌套循环（Nested Loops）<br>
Use_Hash 强制指定两个表间的连接方式为哈希连接（Hash Join）<br>
Use_Merge 强制指定两个表间的连接方式为合并排序连接（Merge Join）<br>
Push_Subq 让非关联子查询提前执行<br>
Index 强制使用某个索引</p>
<p>
<strong>3， 执行计划</strong></p>
<p>
在PL/SQL Developer的SQL WINDOWS中用鼠标或键盘选中SQL语句，然后按F5，就会出现执行计划解析的界面：</p>
<p>
<strong>4， Update的特点</strong></p>
<p>
Update的系统内部执行情况可以参照附文：对update事务的内部分析.doc</p>
<p>
使用Update的基本要点就是，</p>
<p>
1） 尽量使用更新表上的索引，减少不必要的更新<br>
2） 更新的数据来源花费时间尽可能短，如果无法做到就把更新内容插入到中间表中，然后给中间表建上索引，再来更新<br>
3） 如果更新的是主键，建议删除再插入。<br>
5， 示例用表</p>
<p>
后面的阐述将围绕以下两张表展开：</p>
<p>
Create table tab1 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);<br>
Create table tab2 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);<br>
Create table tab3 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);<br>
Create table tab4 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);<br><br>
workdate, cino为两张表的关键字，默认情况没有建主键索引。</p>
<p>
<strong>二，Update两种情况</strong></p>
<p>
用Update更新某个表，无外乎是两种情况：根据关联子查询，更新字段；通过非关联子查询，限定更新范围。如果还有第三种情况，那就是前两种情况的叠加。</p>
<p>
1， 根据关联子查询，更新字段</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_40655">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Update</code> <code class="sql plain">tab1 t</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">Set</code> <code class="sql plain">(val1, val2) = (</code><code class="sql keyword">select</code> <code class="sql plain">val1, val2</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">from</code> <code class="sql plain">tab2</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">where</code> <code class="sql plain">workdate = t.workdate</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql color1">and</code> <code class="sql plain">cino = t.cino);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
通过tab2来更新tab1的相应字段。执行SQL语句时，系统会从tab1中一行一行读记录，然后再通过关联子查询，找到相应的字段来更新。关联子查询能否通过tab1的条件快速的查找到对应记录，是优化能否实现的必要条件。所以一般都要求在tab2上建有Unique或者排重性较高的Normal索引。执行所用时间大概为（查询tab1中一条记录所用的时间 + 在tab2中查询一条记录所用的时间）* tab1中的记录条数。</p>
<p>
如果子查询条件比较复杂，如以下语句：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_691940">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Update</code> <code class="sql plain">tab1 t</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">Set</code> <code class="sql plain">(val1, val2) = (</code><code class="sql keyword">select</code> <code class="sql plain">val1, val2</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">from</code> <code class="sql plain">tab2 tt</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">where</code> <code class="sql plain">exists (</code><code class="sql keyword">select</code> <code class="sql plain">1</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">from</code> <code class="sql plain">tab3</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">where</code> <code class="sql plain">workdate = tt.workdate</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql color1">and</code> <code class="sql plain">cino = tt.cino)</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql color1">and</code> <code class="sql plain">workdate = t.workdate</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql color1">and</code> <code class="sql plain">cino = t.cino);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这时更新tab1中的每条记录花费在子查询上的时间将成倍增加，如果tab1中的记录数较多，这种更新语句几乎是不可完成。</p>
<p>
解决方式是，把子查询提取出来，做到中间表中，然后给中间表建上索引，用中间表来代替子查询，这样速度就能大大提高：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_320686">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Insert</code> <code class="sql keyword">into</code> <code class="sql plain">tab4</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">select</code> <code class="sql plain">workdate, cino, val1, val2</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">from</code> <code class="sql plain">tab2 tt</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">where</code> <code class="sql plain">exists (</code><code class="sql keyword">select</code> <code class="sql plain">1</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">from</code> <code class="sql plain">tab3</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">where</code> <code class="sql plain">workdate = tt.workdate</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql color1">and</code> <code class="sql plain">cino = tt.cino);</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">create</code> <code class="sql keyword">index</code> <code class="sql plain">tab4_ind01 </code><code class="sql keyword">on</code> <code class="sql plain">tab4(workdate, cino);</code>
</div>
<div class="line number9 index8 alt2">
</div>
<div class="line number10 index9 alt1">
<code class="sql keyword">Update</code> <code class="sql plain">tab1 t</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">Set</code> <code class="sql plain">(val1, val2) = (</code><code class="sql keyword">select</code> <code class="sql plain">val1, val2</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">from</code> <code class="sql plain">tab4 tt</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql keyword">where</code> <code class="sql plain">workdate = t.workdate</code>
</div>
<div class="line number14 index13 alt1">
<code class="sql color1">and</code> <code class="sql plain">cino = t.cino);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>2， 通过非关联子查询，限定更新范围</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_158335">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Update</code> <code class="sql plain">tab1 t</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">set</code> <code class="sql plain">val1 = 1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">where</code> <code class="sql plain">(workdate, cino) </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">workdate, cino </code><code class="sql keyword">from</code> <code class="sql plain">tab2)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
根据tab2提供的数据范围，来更新tab1中的相应记录的val1字段。</p>
<p>
在这种情况下，系统默认执行方式往往是先执行select workdate, cino from tab2子查询，在系统中形成系统视图，然后在tab1中选取一条记录，查询系统视图中是否存在相应的workdate, cino组合，如果存在，则更新tab1，如果不存在，则选取下一条记录。这种方式的查询时间大致等于：子查询查询时间 + （在tab1中选取一条记录的时间 + 在系统视图中全表扫描寻找一条记录时间）* tab1的记录条数。其中“在系统视图中全表扫描寻找一条记录时间”会根据tab2的大小而有所不同。若tab2记录数较小，系统可以直接把表读到系统区中；若tab2记录数多，系统无法形成系统视图，这时会每一次更新动作，就把子查询做一次，速度会非常的慢。</p>
<p>
针对这种情况的优化有两种</p>
<p>
1） 在tab1上的workdate, cino字段上加入索引，同时增加提示。</p>
<p>
修改以后的SQL语句如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_883840">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Update</code> <code class="sql plain">/*+ordered use_nl(sys, t)*/ tab1 t</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">set</code> <code class="sql plain">val1 = 1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">where</code> <code class="sql plain">(workdate, cino) </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">workdate, cino </code><code class="sql keyword">from</code> <code class="sql plain">tab2)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
其中sys表示系统视图。如果不加入ordered提示，系统将会默认以tab1表作为驱动表，这时就要对tab1作全表扫描。加入提示后，使用系统视图，即select workdate, cino from tab2，作为驱动表，在正常情况下，速度能提高很多。</p>
<p>
2） 在tab2表上的workdate, cino字段加入索引，同时改写SQL语句：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_607242">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Update</code> <code class="sql plain">tab1 t</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">set</code> <code class="sql plain">val1 = 1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">where</code> <code class="sql plain">exists (</code><code class="sql keyword">select</code> <code class="sql plain">1</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">from</code> <code class="sql plain">tab2</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">where</code> <code class="sql plain">workdate = t.workdate</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql color1">and</code> <code class="sql plain">cino = t.cino)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<strong>三，索引问题</strong></p>
<p>
update索引的使用比较特殊，有时看起来能用全索引，但实际上却只用到一部分，所以建议把复合索引的各字段写在一起。</p>
<p>
例如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_136276">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Update</code> <code class="sql plain">/*+ordered use_nl(sys, t)*/ tab1 t</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">set</code> <code class="sql plain">val1 = 1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">where</code> <code class="sql plain">cino </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">cino </code><code class="sql keyword">from</code> <code class="sql plain">tab2)</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql color1">and</code> <code class="sql plain">workdate = </code><code class="sql string">'200506'</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这条SQL语句是不能完全用到tab1上的复合索引workdate + cino的。能用到的只是workdate='200506'的约束。</p>
<p>
如果写成这样，就没问题：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_640523">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">Update</code> <code class="sql plain">/*+ordered use_nl(sys, t)*/ tab1 t</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">set</code> <code class="sql plain">val1 = 1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">where</code> <code class="sql plain">(workdate, cino) </code><code class="sql color1">in</code> <code class="sql plain">(</code><code class="sql keyword">select</code> <code class="sql plain">workdate, cino </code><code class="sql keyword">from</code> <code class="sql plain">tab2)</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
頁:
[1]