如何利用分析函数改写范围判断自关联查询详解
<p><span><strong>前言</strong></span></p>
<p>
最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。</p>
<p>
分析、定位数据库的主要负载是这条语句引起的过程相对简单，通过AWR报告就可以比较容易的完成定位，这里就不赘述了。</p>
<p>
现在直接看一下这个导致性能问题的SQL语句，其对应的SQL REPORT统计如下：</p>
<p>
</p>
<table><tbody>
<tr>
<td>
Stat Name</td>
<td>
Statement Total</td>
<td>
Per Execution</td>
<td>
% Snap Total</td>
</tr>
<tr>
<td>
Elapsed Time (ms)</td>
<td>
363,741</td>
<td>
363,740.78</td>
<td>
8 .42</td>
</tr>
<tr>
<td>
CPU Time (ms)</td>
<td>
362,770</td>
<td>
362,770.00</td>
<td>
8 .81</td>
</tr>
<tr>
<td>
Executions</td>
<td>
1</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Buffer Gets</td>
<td>
756</td>
<td>
756.00</td>
<td>
0.00</td>
</tr>
<tr>
<td>
Disk Reads</td>
<td>
0</td>
<td>
0.00</td>
<td>
0.00</td>
</tr>
<tr>
<td>
Parse Calls</td>
<td>
1</td>
<td>
1.00</td>
<td>
0.01</td>
</tr>
<tr>
<td>
Rows</td>
<td>
50,825</td>
<td>
50,825.00</td>
<td>
</td>
</tr>
<tr>
<td>
User I/O Wait Time (ms)</td>
<td>
0</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Cluster Wait Time (ms)</td>
<td>
0</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Application Wait Time (ms)</td>
<td>
0</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Concurrency Wait Time (ms)</td>
<td>
0</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Invalidations</td>
<td>
0</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Version Count</td>
<td>
1</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Sharable Mem(KB)</td>
<td>
28</td>
<td>
</td>
<td>
</td>
</tr>
</tbody></table>
<p>
</p>
<p>
从SQL的性能指标上看，其单次执行需要6分钟左右，处理5万多条记录，逻辑度只有756，主要消耗时间在CPU上。而这里就存在疑点，逻辑读如此之低，而CPU时间花费又如此之高，那么这些CPU都消耗在哪里呢？当然这个问通过SQL的统计信息中是找不到答案的，我们下面关注SQL的执行计划：</p>
<p>
</p>
<table><tbody>
<tr>
<td>
Id</td>
<td>
Operation</td>
<td>
Name</td>
<td>
Rows</td>
<td>
Bytes</td>
<td>
TempSpc</td>
<td>
Cost (%CPU)</td>
<td>
Time</td>
</tr>
<tr>
<td>
0</td>
<td>
SELECT STATEMENT</td>
<td>
</td>
<td>
</td>
<td>
</td>
<td>
</td>
<td>
1226 (100)</td>
<td>
</td>
</tr>
<tr>
<td>
1</td>
<td>
SORT ORDER BY</td>
<td>
</td>
<td>
49379</td>
<td>
3375K</td>
<td>
3888K</td>
<td>
1226 (2)</td>
<td>
00:00:05</td>
</tr>
<tr>
<td>
2</td>
<td>
HASH JOIN ANTI</td>
<td>
</td>
<td>
49379</td>
<td>
3375K</td>
<td>
2272K</td>
<td>
401 (3)</td>
<td>
00:00:02</td>
</tr>
<tr>
<td>
3</td>
<td>
TABLE ACCESS FULL</td>
<td>
T_NUM</td>
<td>
49379</td>
<td>
1687K</td>
<td>
</td>
<td>
88 (4)</td>
<td>
00:00:01</td>
</tr>
<tr>
<td>
4</td>
<td>
TABLE ACCESS FULL</td>
<td>
T_NUM</td>
<td>
49379</td>
<td>
1687K</td>
<td>
</td>
<td>
88 (4)</td>
<td>
00:00:01</td>
</tr>
</tbody></table>
<p>
</p>
<p>
从执行计划看，Oracle选择了HASH JOIN ANTI，JOIN的两张表都是T_NUM，且都采用了全表扫描，并未选择索引。仅靠执行计划也只等得到上面的结论，至于为什么不选择索引，以及为什么执行时间过长，还需要进一步的分析。</p>
<p>
将原SQL进行简单脱密改写后， SQL文本类似如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_128081">
<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">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, </code><code class="sql keyword">END</code><code class="sql plain">, ROWID, LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">T_NUM A</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">WHERE</code> <code class="sql color1">NOT</code> <code class="sql plain">EXISTS (</code>
</div>
<div class="line number4 index3 alt1">
<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">T_NUM B</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">WHERE</code> <code class="sql plain">B.</code><code class="sql keyword">BEGIN</code> <code class="sql plain"><= A.</code><code class="sql keyword">BEGIN</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql color1">AND</code> <code class="sql plain">B.</code><code class="sql keyword">END</code> <code class="sql plain">>= A.</code><code class="sql keyword">END</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql color1">AND</code> <code class="sql plain">B.ROWID != A.ROWID</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql color1">AND</code> <code class="sql plain">LENGTH(B.</code><code class="sql keyword">BEGIN</code><code class="sql plain">) = LENGTH(A.</code><code class="sql keyword">BEGIN</code><code class="sql plain">));</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语句，会发现这是一个自关联语句，在BEGIN字段长度相等的前提下，想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。</p>
<p>
简单一点说，表中的记录表示的是由BEGIN开始到END截至的范围，那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是，对于当前的SQL逻辑，如果存在两条范围完全相同的记录，那么最终这两条记录都会被舍弃。</p>
<p>
业务的逻辑并不是特别复杂，但是要解决一条记录与其他记录进行比较，多半采用的方法是自关联，而在这个自关联中，既有大于等于又有小于等于，还有不等于，仅有的一个等于的关联条件，来自范围段BEGIN的长度的比较。</p>
<p>
显而易见的是，如果是范围段本身的比较，其选择度一般还是不错的，但是如果只是比较其长度，那么无疑容易产生大量的重复，比如在这个例子中：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_271834">
<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>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">SQL> </code><code class="sql keyword">select</code> <code class="sql plain">length(</code><code class="sql keyword">begin</code><code class="sql plain">), </code><code class="sql color2">count</code><code class="sql plain">(*) </code><code class="sql keyword">from</code> <code class="sql plain">t_num </code><code class="sql keyword">group</code> <code class="sql keyword">by</code> <code class="sql plain">length(</code><code class="sql keyword">begin</code><code class="sql plain">) </code><code class="sql keyword">order</code> <code class="sql keyword">by</code> <code class="sql plain">2 </code><code class="sql keyword">desc</code><code class="sql plain">;</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">) </code><code class="sql color2">COUNT</code><code class="sql plain">(*)</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">————- ———-</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">12 22096</code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">11 9011</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">13 8999</code>
</div>
<div class="line number14 index13 alt1">
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">14 8186</code>
</div>
<div class="line number16 index15 alt1">
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">16 49</code>
</div>
<div class="line number18 index17 alt1">
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">9 45</code>
</div>
<div class="line number20 index19 alt1">
</div>
<div class="line number21 index20 alt2">
<code class="sql plain">8 41</code>
</div>
<div class="line number22 index21 alt1">
</div>
<div class="line number23 index22 alt2">
<code class="sql plain">7 27</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
大量重复的数据出现在长度为11到14的范围上，在这种情况下，仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的，这时一条记录根据这个等值条件会关联到近万条记录，设置关联到两万多条记录，显然大量的实践消耗在低效的连接过程中。</p>
<p>
再来看一下具体的SQL语句，会发现几乎没有办法建立索引，因为LENGTH(BEGIN)的选择度非常查，而其他的条件都是不等查询，选择度也不会好，即使建立索引，强制执行选择索引，效率也不会好。</p>
<p>
那么如果想要继续优化这个SQL，就只剩下一个办法，那就是SQL的改写。对于自关联查询而言，最佳的改写方法是利用分析函数，其强大的行级处理能力，可以在一次扫描过程中获得一条记录与其他记录的关系，从而消除了自关联的必要性。</p>
<p>
SQL改写结果如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_887478">
<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>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, OLDEND </code><code class="sql keyword">END</code><code class="sql plain">, LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">(</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, OLDEND, </code><code class="sql keyword">END</code><code class="sql plain">, LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">), </code><code class="sql color2">COUNT</code><code class="sql plain">(*) OVER(PARTITION </code><code class="sql keyword">BY</code> <code class="sql plain">LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">), </code><code class="sql keyword">BEGIN</code><code class="sql plain">, OLDEND) CN,</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql plain">ROW_NUMBER() OVER(PARTITION </code><code class="sql keyword">BY</code> <code class="sql plain">LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">), </code><code class="sql keyword">END</code> <code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql keyword">BEGIN</code><code class="sql plain">) RN</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">FROM</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql plain">(</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, </code><code class="sql keyword">END</code> <code class="sql plain">OLDEND, </code><code class="sql keyword">MAX</code><code class="sql plain">(</code><code class="sql keyword">END</code><code class="sql plain">) OVER(PARTITION </code><code class="sql keyword">BY</code> <code class="sql plain">LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">) </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, </code><code class="sql keyword">END</code> <code class="sql keyword">DESC</code><code class="sql plain">) </code><code class="sql keyword">END</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">FROM</code> <code class="sql plain">T_NUM</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">)</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql plain">)</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">RN = 1</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql color1">AND</code> <code class="sql plain">CN = 1;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
简单的说，内层的分析函数MAX用来根据BEGIN从小到大，END从大到小的条件，确定每个范围对应的最大的END的值。而外层的两个分析函数，COUNT用来去掉完全重复的记录，而ROW_NUMBER用来获取范围最大的记录（也就是没有被其他记录的范围所涵盖）。</p>
<p>
改写后，这个SQL避免对自关联，也就不存在关联条件重复值过高的性能隐患了。在模拟环境中，性能对比如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_354833">
<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>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
<div class="line number42 index41 alt1">
42</div>
<div class="line number43 index42 alt2">
43</div>
<div class="line number44 index43 alt1">
44</div>
<div class="line number45 index44 alt2">
45</div>
<div class="line number46 index45 alt1">
46</div>
<div class="line number47 index46 alt2">
47</div>
<div class="line number48 index47 alt1">
48</div>
<div class="line number49 index48 alt2">
49</div>
<div class="line number50 index49 alt1">
50</div>
<div class="line number51 index50 alt2">
51</div>
<div class="line number52 index51 alt1">
52</div>
<div class="line number53 index52 alt2">
53</div>
<div class="line number54 index53 alt1">
54</div>
<div class="line number55 index54 alt2">
55</div>
<div class="line number56 index55 alt1">
56</div>
<div class="line number57 index56 alt2">
57</div>
<div class="line number58 index57 alt1">
58</div>
<div class="line number59 index58 alt2">
59</div>
<div class="line number60 index59 alt1">
60</div>
<div class="line number61 index60 alt2">
61</div>
<div class="line number62 index61 alt1">
62</div>
<div class="line number63 index62 alt2">
63</div>
<div class="line number64 index63 alt1">
64</div>
<div class="line number65 index64 alt2">
65</div>
<div class="line number66 index65 alt1">
66</div>
<div class="line number67 index66 alt2">
67</div>
<div class="line number68 index67 alt1">
68</div>
<div class="line number69 index68 alt2">
69</div>
<div class="line number70 index69 alt1">
70</div>
<div class="line number71 index70 alt2">
71</div>
<div class="line number72 index71 alt1">
72</div>
<div class="line number73 index72 alt2">
73</div>
<div class="line number74 index73 alt1">
74</div>
<div class="line number75 index74 alt2">
75</div>
<div class="line number76 index75 alt1">
76</div>
<div class="line number77 index76 alt2">
77</div>
<div class="line number78 index77 alt1">
78</div>
<div class="line number79 index78 alt2">
79</div>
<div class="line number80 index79 alt1">
80</div>
<div class="line number81 index80 alt2">
81</div>
<div class="line number82 index81 alt1">
82</div>
<div class="line number83 index82 alt2">
83</div>
<div class="line number84 index83 alt1">
84</div>
<div class="line number85 index84 alt2">
85</div>
<div class="line number86 index85 alt1">
86</div>
<div class="line number87 index86 alt2">
87</div>
<div class="line number88 index87 alt1">
88</div>
<div class="line number89 index88 alt2">
89</div>
<div class="line number90 index89 alt1">
90</div>
<div class="line number91 index90 alt2">
91</div>
<div class="line number92 index91 alt1">
92</div>
<div class="line number93 index92 alt2">
93</div>
<div class="line number94 index93 alt1">
94</div>
<div class="line number95 index94 alt2">
95</div>
<div class="line number96 index95 alt1">
96</div>
<div class="line number97 index96 alt2">
97</div>
<div class="line number98 index97 alt1">
98</div>
<div class="line number99 index98 alt2">
99</div>
<div class="line number100 index99 alt1">
100</div>
<div class="line number101 index100 alt2">
101</div>
<div class="line number102 index101 alt1">
102</div>
<div class="line number103 index102 alt2">
103</div>
<div class="line number104 index103 alt1">
104</div>
<div class="line number105 index104 alt2">
105</div>
<div class="line number106 index105 alt1">
106</div>
<div class="line number107 index106 alt2">
107</div>
<div class="line number108 index107 alt1">
108</div>
<div class="line number109 index108 alt2">
109</div>
<div class="line number110 index109 alt1">
110</div>
<div class="line number111 index110 alt2">
111</div>
<div class="line number112 index111 alt1">
112</div>
<div class="line number113 index112 alt2">
113</div>
<div class="line number114 index113 alt1">
114</div>
<div class="line number115 index114 alt2">
115</div>
<div class="line number116 index115 alt1">
116</div>
<div class="line number117 index116 alt2">
117</div>
<div class="line number118 index117 alt1">
118</div>
<div class="line number119 index118 alt2">
119</div>
<div class="line number120 index119 alt1">
120</div>
<div class="line number121 index120 alt2">
121</div>
<div class="line number122 index121 alt1">
122</div>
<div class="line number123 index122 alt2">
123</div>
<div class="line number124 index123 alt1">
124</div>
<div class="line number125 index124 alt2">
125</div>
<div class="line number126 index125 alt1">
126</div>
<div class="line number127 index126 alt2">
127</div>
<div class="line number128 index127 alt1">
128</div>
<div class="line number129 index128 alt2">
129</div>
<div class="line number130 index129 alt1">
130</div>
<div class="line number131 index130 alt2">
131</div>
<div class="line number132 index131 alt1">
132</div>
<div class="line number133 index132 alt2">
133</div>
<div class="line number134 index133 alt1">
134</div>
<div class="line number135 index134 alt2">
135</div>
<div class="line number136 index135 alt1">
136</div>
<div class="line number137 index136 alt2">
137</div>
<div class="line number138 index137 alt1">
138</div>
<div class="line number139 index138 alt2">
139</div>
<div class="line number140 index139 alt1">
140</div>
<div class="line number141 index140 alt2">
141</div>
<div class="line number142 index141 alt1">
142</div>
<div class="line number143 index142 alt2">
143</div>
<div class="line number144 index143 alt1">
144</div>
<div class="line number145 index144 alt2">
145</div>
<div class="line number146 index145 alt1">
146</div>
<div class="line number147 index146 alt2">
147</div>
<div class="line number148 index147 alt1">
148</div>
<div class="line number149 index148 alt2">
149</div>
<div class="line number150 index149 alt1">
150</div>
<div class="line number151 index150 alt2">
151</div>
<div class="line number152 index151 alt1">
152</div>
<div class="line number153 index152 alt2">
153</div>
<div class="line number154 index153 alt1">
154</div>
<div class="line number155 index154 alt2">
155</div>
<div class="line number156 index155 alt1">
156</div>
<div class="line number157 index156 alt2">
157</div>
<div class="line number158 index157 alt1">
158</div>
<div class="line number159 index158 alt2">
159</div>
<div class="line number160 index159 alt1">
160</div>
<div class="line number161 index160 alt2">
161</div>
<div class="line number162 index161 alt1">
162</div>
<div class="line number163 index162 alt2">
163</div>
<div class="line number164 index163 alt1">
164</div>
<div class="line number165 index164 alt2">
165</div>
<div class="line number166 index165 alt1">
166</div>
<div class="line number167 index166 alt2">
167</div>
<div class="line number168 index167 alt1">
168</div>
<div class="line number169 index168 alt2">
169</div>
<div class="line number170 index169 alt1">
170</div>
<div class="line number171 index170 alt2">
171</div>
<div class="line number172 index171 alt1">
172</div>
<div class="line number173 index172 alt2">
173</div>
<div class="line number174 index173 alt1">
174</div>
<div class="line number175 index174 alt2">
175</div>
<div class="line number176 index175 alt1">
176</div>
<div class="line number177 index176 alt2">
177</div>
<div class="line number178 index177 alt1">
178</div>
<div class="line number179 index178 alt2">
179</div>
<div class="line number180 index179 alt1">
180</div>
<div class="line number181 index180 alt2">
181</div>
<div class="line number182 index181 alt1">
182</div>
<div class="line number183 index182 alt2">
183</div>
<div class="line number184 index183 alt1">
184</div>
<div class="line number185 index184 alt2">
185</div>
<div class="line number186 index185 alt1">
186</div>
<div class="line number187 index186 alt2">
187</div>
<div class="line number188 index187 alt1">
188</div>
<div class="line number189 index188 alt2">
189</div>
<div class="line number190 index189 alt1">
190</div>
<div class="line number191 index190 alt2">
191</div>
<div class="line number192 index191 alt1">
192</div>
<div class="line number193 index192 alt2">
193</div>
<div class="line number194 index193 alt1">
194</div>
<div class="line number195 index194 alt2">
195</div>
<div class="line number196 index195 alt1">
196</div>
<div class="line number197 index196 alt2">
197</div>
<div class="line number198 index197 alt1">
198</div>
<div class="line number199 index198 alt2">
199</div>
<div class="line number200 index199 alt1">
200</div>
<div class="line number201 index200 alt2">
201</div>
<div class="line number202 index201 alt1">
202</div>
<div class="line number203 index202 alt2">
203</div>
<div class="line number204 index203 alt1">
204</div>
<div class="line number205 index204 alt2">
205</div>
<div class="line number206 index205 alt1">
206</div>
<div class="line number207 index206 alt2">
207</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">SQL> </code><code class="sql keyword">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, </code><code class="sql keyword">END</code><code class="sql plain">, ROWID, LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">)</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql plain">2 </code><code class="sql keyword">FROM</code> <code class="sql plain">T_NUM A</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql plain">3 </code><code class="sql keyword">WHERE</code> <code class="sql color1">NOT</code> <code class="sql plain">EXISTS (</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">4 </code><code class="sql keyword">SELECT</code> <code class="sql plain">1</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">5 </code><code class="sql keyword">FROM</code> <code class="sql plain">T_NUM B</code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="sql plain">6 </code><code class="sql keyword">WHERE</code> <code class="sql plain">B.</code><code class="sql keyword">BEGIN</code> <code class="sql plain"><= A.</code><code class="sql keyword">BEGIN</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql plain">7 </code><code class="sql color1">AND</code> <code class="sql plain">B.</code><code class="sql keyword">END</code> <code class="sql plain">>= A.</code><code class="sql keyword">END</code>
</div>
<div class="line number14 index13 alt1">
</div>
<div class="line number15 index14 alt2">
<code class="sql plain">8 </code><code class="sql color1">AND</code> <code class="sql plain">B.ROWID != A.ROWID</code>
</div>
<div class="line number16 index15 alt1">
</div>
<div class="line number17 index16 alt2">
<code class="sql plain">9 </code><code class="sql color1">AND</code> <code class="sql plain">LENGTH(B.</code><code class="sql keyword">BEGIN</code><code class="sql plain">) = LENGTH(A.</code><code class="sql keyword">BEGIN</code><code class="sql plain">))</code>
</div>
<div class="line number18 index17 alt1">
</div>
<div class="line number19 index18 alt2">
<code class="sql plain">10 ;</code>
</div>
<div class="line number20 index19 alt1">
</div>
<div class="line number21 index20 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number22 index21 alt1">
</div>
<div class="line number23 index22 alt2">
<code class="sql plain">48344 </code><code class="sql keyword">rows</code> <code class="sql plain">selected.</code>
</div>
<div class="line number24 index23 alt1">
</div>
<div class="line number25 index24 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number26 index25 alt1">
</div>
<div class="line number27 index26 alt2">
<code class="sql plain">Elapsed: 00:00:57.68</code>
</div>
<div class="line number28 index27 alt1">
</div>
<div class="line number29 index28 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number30 index29 alt1">
</div>
<div class="line number31 index30 alt2">
<code class="sql plain">Execution Plan</code>
</div>
<div class="line number32 index31 alt1">
</div>
<div class="line number33 index32 alt2">
<code class="sql plain">———————————————————-</code>
</div>
<div class="line number34 index33 alt1">
</div>
<div class="line number35 index34 alt2">
<code class="sql plain">Plan hash value: 2540751655</code>
</div>
<div class="line number36 index35 alt1">
</div>
<div class="line number37 index36 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number38 index37 alt1">
</div>
<div class="line number39 index38 alt2">
<code class="sql plain">————————————————————————————</code>
</div>
<div class="line number40 index39 alt1">
</div>
<div class="line number41 index40 alt2">
<code class="sql plain">| Id | Operation | </code><code class="sql keyword">Name</code> <code class="sql plain">| </code><code class="sql keyword">Rows</code> <code class="sql plain">| Bytes |TempSpc| Cost (%CPU)| </code><code class="sql keyword">Time</code> <code class="sql plain">|</code>
</div>
<div class="line number42 index41 alt1">
</div>
<div class="line number43 index42 alt2">
<code class="sql plain">————————————————————————————</code>
</div>
<div class="line number44 index43 alt1">
</div>
<div class="line number45 index44 alt2">
<code class="sql plain">| 0 | </code><code class="sql keyword">SELECT</code> <code class="sql plain">STATEMENT | | 48454 | 1703K| | 275 (1)| 00:00:04 |</code>
</div>
<div class="line number46 index45 alt1">
</div>
<div class="line number47 index46 alt2">
<code class="sql plain">|* 1 | HASH </code><code class="sql color1">JOIN</code> <code class="sql plain">ANTI | | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 |</code>
</div>
<div class="line number48 index47 alt1">
</div>
<div class="line number49 index48 alt2">
<code class="sql plain">| 2 | </code><code class="sql keyword">TABLE</code> <code class="sql plain">ACCESS </code><code class="sql keyword">FULL</code><code class="sql plain">| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |</code>
</div>
<div class="line number50 index49 alt1">
</div>
<div class="line number51 index50 alt2">
<code class="sql plain">| 3 | </code><code class="sql keyword">TABLE</code> <code class="sql plain">ACCESS </code><code class="sql keyword">FULL</code><code class="sql plain">| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |</code>
</div>
<div class="line number52 index51 alt1">
</div>
<div class="line number53 index52 alt2">
<code class="sql plain">————————————————————————————</code>
</div>
<div class="line number54 index53 alt1">
</div>
<div class="line number55 index54 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number56 index55 alt1">
</div>
<div class="line number57 index56 alt2">
<code class="sql plain">Predicate Information (identified </code><code class="sql keyword">by</code> <code class="sql plain">operation id):</code>
</div>
<div class="line number58 index57 alt1">
</div>
<div class="line number59 index58 alt2">
<code class="sql plain">—————————————————</code>
</div>
<div class="line number60 index59 alt1">
</div>
<div class="line number61 index60 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number62 index61 alt1">
</div>
<div class="line number63 index62 alt2">
<code class="sql plain">1 – access(LENGTH(TO_CHAR(“B”.”</code><code class="sql keyword">BEGIN</code><code class="sql plain">”))=LENGTH(TO_CHAR(“A”.”</code><code class="sql keyword">BEGIN</code><code class="sql plain">”)))</code>
</div>
<div class="line number64 index63 alt1">
</div>
<div class="line number65 index64 alt2">
<code class="sql plain">filter(“B”.”</code><code class="sql keyword">BEGIN</code><code class="sql plain">”<=”A”.”</code><code class="sql keyword">BEGIN</code><code class="sql plain">” </code><code class="sql color1">AND</code> <code class="sql plain">“B”.”</code><code class="sql keyword">END</code><code class="sql plain">”>=”A”.”</code><code class="sql keyword">END</code><code class="sql plain">” </code><code class="sql color1">AND</code>
</div>
<div class="line number66 index65 alt1">
</div>
<div class="line number67 index66 alt2">
<code class="sql plain">“B”.ROWID<>”A”.ROWID)</code>
</div>
<div class="line number68 index67 alt1">
</div>
<div class="line number69 index68 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number70 index69 alt1">
</div>
<div class="line number71 index70 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number72 index71 alt1">
</div>
<div class="line number73 index72 alt2">
<code class="sql keyword">Statistics</code>
</div>
<div class="line number74 index73 alt1">
</div>
<div class="line number75 index74 alt2">
<code class="sql plain">———————————————————-</code>
</div>
<div class="line number76 index75 alt1">
</div>
<div class="line number77 index76 alt2">
<code class="sql plain">0 recursive calls</code>
</div>
<div class="line number78 index77 alt1">
</div>
<div class="line number79 index78 alt2">
<code class="sql plain">0 db block gets</code>
</div>
<div class="line number80 index79 alt1">
</div>
<div class="line number81 index80 alt2">
<code class="sql plain">404 consistent gets</code>
</div>
<div class="line number82 index81 alt1">
</div>
<div class="line number83 index82 alt2">
<code class="sql plain">0 physical reads</code>
</div>
<div class="line number84 index83 alt1">
</div>
<div class="line number85 index84 alt2">
<code class="sql plain">0 redo </code><code class="sql keyword">size</code>
</div>
<div class="line number86 index85 alt1">
</div>
<div class="line number87 index86 alt2">
<code class="sql plain">2315794 bytes sent via SQL*Net </code><code class="sql keyword">to</code> <code class="sql plain">client</code>
</div>
<div class="line number88 index87 alt1">
</div>
<div class="line number89 index88 alt2">
<code class="sql plain">35966 bytes received via SQL*Net </code><code class="sql keyword">from</code> <code class="sql plain">client</code>
</div>
<div class="line number90 index89 alt1">
</div>
<div class="line number91 index90 alt2">
<code class="sql plain">3224 SQL*Net roundtrips </code><code class="sql keyword">to</code><code class="sql plain">/</code><code class="sql keyword">from</code> <code class="sql plain">client</code>
</div>
<div class="line number92 index91 alt1">
</div>
<div class="line number93 index92 alt2">
<code class="sql plain">0 sorts (memory)</code>
</div>
<div class="line number94 index93 alt1">
</div>
<div class="line number95 index94 alt2">
<code class="sql plain">0 sorts (disk)</code>
</div>
<div class="line number96 index95 alt1">
</div>
<div class="line number97 index96 alt2">
<code class="sql plain">48344 </code><code class="sql keyword">rows</code> <code class="sql plain">processed</code>
</div>
<div class="line number98 index97 alt1">
</div>
<div class="line number99 index98 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number100 index99 alt1">
</div>
<div class="line number101 index100 alt2">
<code class="sql plain">SQL> </code><code class="sql keyword">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, OLDEND </code><code class="sql keyword">END</code><code class="sql plain">, LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">)</code>
</div>
<div class="line number102 index101 alt1">
</div>
<div class="line number103 index102 alt2">
<code class="sql plain">2 </code><code class="sql keyword">FROM</code> <code class="sql plain">(</code>
</div>
<div class="line number104 index103 alt1">
</div>
<div class="line number105 index104 alt2">
<code class="sql plain">3 </code><code class="sql keyword">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, OLDEND, </code><code class="sql keyword">END</code><code class="sql plain">, LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">), </code><code class="sql color2">COUNT</code><code class="sql plain">(*) OVER(PARTITION </code><code class="sql keyword">BY</code> <code class="sql plain">LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">), </code><code class="sql keyword">BEGIN</code><code class="sql plain">, OLDEND) CN,</code>
</div>
<div class="line number106 index105 alt1">
</div>
<div class="line number107 index106 alt2">
<code class="sql plain">4 ROW_NUMBER() OVER(PARTITION </code><code class="sql keyword">BY</code> <code class="sql plain">LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">), </code><code class="sql keyword">END</code> <code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql keyword">BEGIN</code><code class="sql plain">) RN</code>
</div>
<div class="line number108 index107 alt1">
</div>
<div class="line number109 index108 alt2">
<code class="sql plain">5 </code><code class="sql keyword">FROM</code>
</div>
<div class="line number110 index109 alt1">
</div>
<div class="line number111 index110 alt2">
<code class="sql plain">6 (</code>
</div>
<div class="line number112 index111 alt1">
</div>
<div class="line number113 index112 alt2">
<code class="sql plain">7 </code><code class="sql keyword">SELECT</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, </code><code class="sql keyword">END</code> <code class="sql plain">OLDEND, </code><code class="sql keyword">MAX</code><code class="sql plain">(</code><code class="sql keyword">END</code><code class="sql plain">) OVER(PARTITION </code><code class="sql keyword">BY</code> <code class="sql plain">LENGTH(</code><code class="sql keyword">BEGIN</code><code class="sql plain">) </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql keyword">BEGIN</code><code class="sql plain">, </code><code class="sql keyword">END</code> <code class="sql keyword">DESC</code><code class="sql plain">) </code><code class="sql keyword">END</code>
</div>
<div class="line number114 index113 alt1">
</div>
<div class="line number115 index114 alt2">
<code class="sql plain">8 </code><code class="sql keyword">FROM</code> <code class="sql plain">T_NUM</code>
</div>
<div class="line number116 index115 alt1">
</div>
<div class="line number117 index116 alt2">
<code class="sql plain">9 )</code>
</div>
<div class="line number118 index117 alt1">
</div>
<div class="line number119 index118 alt2">
<code class="sql plain">10 )</code>
</div>
<div class="line number120 index119 alt1">
</div>
<div class="line number121 index120 alt2">
<code class="sql plain">11 </code><code class="sql keyword">WHERE</code> <code class="sql plain">RN = 1</code>
</div>
<div class="line number122 index121 alt1">
</div>
<div class="line number123 index122 alt2">
<code class="sql plain">12 </code><code class="sql color1">AND</code> <code class="sql plain">CN = 1;</code>
</div>
<div class="line number124 index123 alt1">
</div>
<div class="line number125 index124 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number126 index125 alt1">
</div>
<div class="line number127 index126 alt2">
<code class="sql plain">48344 </code><code class="sql keyword">rows</code> <code class="sql plain">selected.</code>
</div>
<div class="line number128 index127 alt1">
</div>
<div class="line number129 index128 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number130 index129 alt1">
</div>
<div class="line number131 index130 alt2">
<code class="sql plain">Elapsed: 00:00:00.72</code>
</div>
<div class="line number132 index131 alt1">
</div>
<div class="line number133 index132 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number134 index133 alt1">
</div>
<div class="line number135 index134 alt2">
<code class="sql plain">Execution Plan</code>
</div>
<div class="line number136 index135 alt1">
</div>
<div class="line number137 index136 alt2">
<code class="sql plain">———————————————————-</code>
</div>
<div class="line number138 index137 alt1">
</div>
<div class="line number139 index138 alt2">
<code class="sql plain">Plan hash value: 1546715670</code>
</div>
<div class="line number140 index139 alt1">
</div>
<div class="line number141 index140 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number142 index141 alt1">
</div>
<div class="line number143 index142 alt2">
<code class="sql plain">——————————————————————————————</code>
</div>
<div class="line number144 index143 alt1">
</div>
<div class="line number145 index144 alt2">
<code class="sql plain">| Id | Operation | </code><code class="sql keyword">Name</code> <code class="sql plain">| </code><code class="sql keyword">Rows</code> <code class="sql plain">| Bytes |TempSpc| Cost (%CPU)| </code><code class="sql keyword">Time</code> <code class="sql plain">|</code>
</div>
<div class="line number146 index145 alt1">
</div>
<div class="line number147 index146 alt2">
<code class="sql plain">——————————————————————————————</code>
</div>
<div class="line number148 index147 alt1">
</div>
<div class="line number149 index148 alt2">
<code class="sql plain">| 0 | </code><code class="sql keyword">SELECT</code> <code class="sql plain">STATEMENT | | 48454 | 2460K| | 800 (1)| 00:00:10 |</code>
</div>
<div class="line number150 index149 alt1">
</div>
<div class="line number151 index150 alt2">
<code class="sql plain">|* 1 | </code><code class="sql keyword">VIEW</code> <code class="sql plain">| | 48454 | 2460K| | 800 (1)| 00:00:10 |</code>
</div>
<div class="line number152 index151 alt1">
</div>
<div class="line number153 index152 alt2">
<code class="sql plain">|* 2 | WINDOW SORT PUSHED RANK| | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 |</code>
</div>
<div class="line number154 index153 alt1">
</div>
<div class="line number155 index154 alt2">
<code class="sql plain">| 3 | WINDOW BUFFER | | 48454 | 1845K| | 800 (1)| 00:00:10 |</code>
</div>
<div class="line number156 index155 alt1">
</div>
<div class="line number157 index156 alt2">
<code class="sql plain">| 4 | </code><code class="sql keyword">VIEW</code> <code class="sql plain">| | 48454 | 1845K| | 311 (1)| 00:00:04 |</code>
</div>
<div class="line number158 index157 alt1">
</div>
<div class="line number159 index158 alt2">
<code class="sql plain">| 5 | WINDOW SORT | | 48454 | 662K| 1152K| 311 (1)| 00:00:04 |</code>
</div>
<div class="line number160 index159 alt1">
</div>
<div class="line number161 index160 alt2">
<code class="sql plain">| 6 | </code><code class="sql keyword">TABLE</code> <code class="sql plain">ACCESS </code><code class="sql keyword">FULL</code> <code class="sql plain">| T_NUM | 48454 | 662K| | 68 (0)| 00:00:01 |</code>
</div>
<div class="line number162 index161 alt1">
</div>
<div class="line number163 index162 alt2">
<code class="sql plain">——————————————————————————————</code>
</div>
<div class="line number164 index163 alt1">
</div>
<div class="line number165 index164 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number166 index165 alt1">
</div>
<div class="line number167 index166 alt2">
<code class="sql plain">Predicate Information (identified </code><code class="sql keyword">by</code> <code class="sql plain">operation id):</code>
</div>
<div class="line number168 index167 alt1">
</div>
<div class="line number169 index168 alt2">
<code class="sql plain">—————————————————</code>
</div>
<div class="line number170 index169 alt1">
</div>
<div class="line number171 index170 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number172 index171 alt1">
</div>
<div class="line number173 index172 alt2">
<code class="sql plain">1 – filter(“RN”=1 </code><code class="sql color1">AND</code> <code class="sql plain">“CN”=1)</code>
</div>
<div class="line number174 index173 alt1">
</div>
<div class="line number175 index174 alt2">
<code class="sql plain">2 – filter(ROW_NUMBER() OVER ( PARTITION </code><code class="sql keyword">BY</code> <code class="sql plain">LENGTH(TO_CHAR(“</code><code class="sql keyword">BEGIN</code><code class="sql plain">”)),”</code><code class="sql keyword">END</code><code class="sql plain">”</code>
</div>
<div class="line number176 index175 alt1">
</div>
<div class="line number177 index176 alt2">
<code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">“</code><code class="sql keyword">BEGIN</code><code class="sql plain">”)<=1)</code>
</div>
<div class="line number178 index177 alt1">
</div>
<div class="line number179 index178 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number180 index179 alt1">
</div>
<div class="line number181 index180 alt2">
<code class="sql spaces"> </code>
</div>
<div class="line number182 index181 alt1">
</div>
<div class="line number183 index182 alt2">
<code class="sql keyword">Statistics</code>
</div>
<div class="line number184 index183 alt1">
</div>
<div class="line number185 index184 alt2">
<code class="sql plain">———————————————————-</code>
</div>
<div class="line number186 index185 alt1">
</div>
<div class="line number187 index186 alt2">
<code class="sql plain">0 recursive calls</code>
</div>
<div class="line number188 index187 alt1">
</div>
<div class="line number189 index188 alt2">
<code class="sql plain">0 db block gets</code>
</div>
<div class="line number190 index189 alt1">
</div>
<div class="line number191 index190 alt2">
<code class="sql plain">202 consistent gets</code>
</div>
<div class="line number192 index191 alt1">
</div>
<div class="line number193 index192 alt2">
<code class="sql plain">0 physical reads</code>
</div>
<div class="line number194 index193 alt1">
</div>
<div class="line number195 index194 alt2">
<code class="sql plain">0 redo </code><code class="sql keyword">size</code>
</div>
<div class="line number196 index195 alt1">
</div>
<div class="line number197 index196 alt2">
<code class="sql plain">1493879 bytes sent via SQL*Net </code><code class="sql keyword">to</code> <code class="sql plain">client</code>
</div>
<div class="line number198 index197 alt1">
</div>
<div class="line number199 index198 alt2">
<code class="sql plain">35966 bytes received via SQL*Net </code><code class="sql keyword">from</code> <code class="sql plain">client</code>
</div>
<div class="line number200 index199 alt1">
</div>
<div class="line number201 index200 alt2">
<code class="sql plain">3224 SQL*Net roundtrips </code><code class="sql keyword">to</code><code class="sql plain">/</code><code class="sql keyword">from</code> <code class="sql plain">client</code>
</div>
<div class="line number202 index201 alt1">
</div>
<div class="line number203 index202 alt2">
<code class="sql plain">3 sorts (memory)</code>
</div>
<div class="line number204 index203 alt1">
</div>
<div class="line number205 index204 alt2">
<code class="sql plain">0 sorts (disk)</code>
</div>
<div class="line number206 index205 alt1">
</div>
<div class="line number207 index206 alt2">
<code class="sql plain">48344 </code><code class="sql keyword">rows</code> <code class="sql plain">processed</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运行时间接近1分钟，而改写后的SQL语句只需要0.72秒，执行时间变为原本的1/80，逻辑读减少一半。</p>
<p>
<span><strong>总结</strong></span></p>
<p>
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。</p>
<p>
原文链接：http://yangtingkun.net/?p=1513</p>
頁:
[1]