oracle分页查询的效率分析

约定不等于承诺〃 2022-06-13 03:43 752阅读 0赞

**ROWNUM**

可能都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。  
ROWNUM是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。  
ROWNUM总是从1开始，不管当前的记录是否满足查询结果，ROWNUM返回的值都是1，如果这条记录的值最终满足所有的条件，那么ROWNUM会递加，下一条记录的ROWNUM会返回2，否则下一条记录的ROWNUM仍然返回1。  
理解了这一点，就清楚为什么一般的ROWNUM大于某个值或等于某个不为1的值是无法返回结果的，因此对于每条记录的ROWNUM都是1，而ROWNUM为1不满足查询的结果，所以下一条记录的ROWNUM不会递增，仍然是1，因此所有的记录都不满足条件。

**分页查询格式1**  
在查询的最外层控制分页的最小值和最大值。查询语句如下：

**\[sql\]**  [view plain][]   [copy][view plain]

1.  **SELECT** \* **FROM**   
2.  (  
3.  **SELECT** A.\*, ROWNUM RN   
4.  **FROM** (**SELECT** \* **FROM** TABLE\_NAME) A   
5.  )  
6.  **WHERE** RN BETWEEN 21 AND 40

**分页查询格式2**

**\[sql\]**  [view plain][]   [copy][view plain]

1.  **SELECT** \* **FROM**   
2.  (  
3.  **SELECT** A.\*, ROWNUM RN   
4.  **FROM** (**SELECT** \* **FROM** TABLE\_NAME) A   
5.  **WHERE** ROWNUM <= 40  
6.  )  
7.  **WHERE** RN >= 21

**分页查询格式3**  
考虑到多表联合的情况，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

**\[sql\]**  [view plain][]   [copy][view plain]

1.  **SELECT** /\*+ FIRST\_ROWS \*/ \* **FROM**   
2.  (  
3.  **SELECT** A.\*, ROWNUM RN   
4.  **FROM** (**SELECT** \* **FROM** TABLE\_NAME) A   
5.  **WHERE** ROWNUM <= 40  
6.  )  
7.  **WHERE** RN >= 21

**效率问题**  
对比这两种写法，**绝大多数的情况下，第2个查询的效率比第1个高得多。**  
这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第2个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。  
而第1个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第1个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。  
上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。  
  
观察上面格式1和格式2二者的执行计划可以发现，两个执行计划唯一的区别就是格式2的查询在COUNT这步使用了STOPKEY，也就是说，Oracle将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。  
  
分页查询语句之所以可以很快的返回结果，是因为它的目标是最快的返回第一条结果。如果每页有20条记录，目前翻到第5页，那么只需要返回前100条记录都可以满足查询的要求了，也许还有几万条记录也符合查询的条件，但是由于分页的限制，在当前的查询中可以忽略这些数据，而只需尽快的返回前100条数据。这也是为什么在标准分页查询语句中经常会使用FIRST\_ROWS提示的原因。  
对于行操作，可以在得到结果的同时将结果直接返回给上一层调用。但是对于结果集操作，Oracle必须得到结果集中所有的数据，因此分页查询中所带的ROWNUM信息不起左右。如果最内层的子查询中包含了下面这些操作中的一个以上，则分页查询语句无法体现出任何的性能优势：UNION、UNION ALL、MINUS、INTERSECT、GROUP BY、DISTINCT、UNIQUE以及聚集函数如MAX、MIN和分析函数等。  
  
Oracle10g的新功能GROUP BY STOPKEY，使得Oracle10g解决了GROUP BY操作分页效率低的问题。在10g以前，Oracle的GROUP BY操作必须完全执行完，才能将结果返回给用户。但是Oracle10g增加了GROUP BY STOPKEY执行路径，使得用户在执行GROUP BY操作时，可以根据STOPKEY随时中止正在运行的操作。这使得标准分页函数对于GROUP BY操作重新发挥了作用。  
  
除了这些操作以外，分页查询还有一个很明显的特点，就是处理的页数越小，效率就越高，越到后面，查询速度越慢。  
分页查询用来提高返回速度的方法都是针对数据量较小的前N条记录而言。无论是索引扫描，NESTED LOOP连接，还是ORDER BY STOPKEY，这些方法带来性能提升的前提都是数据量比较小，一旦分页到了最后几页，会发现这些方法不但没有办法带来性能的提升，而且性能比普通查询还要低得多。这一点，在使用分页查询的时候，一定要心里有数。  
分页查询一般情况下，很少会翻到最后一篇，如果只是偶尔碰到这种情况，对系统性能不会有很大的影响，但是如果经常碰到这种情况，在设计分页查询时应该给予足够的考虑。  
  
**多表联合**  
下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。  
一般对于大表查询情况下，HASH JOIN的效率要比NESTED LOOP高很多，所以CBO一般默认会选择HASH JOIN.  
  
但是如果分页查询的内层是这种连接查询的话，使用NESTED LOOP可以更快的得到前N条记录。  
在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。  
  
HASH JOIN中第一步也就是第一张表的全表扫描是无法应用STOPKEY的，这就是NESTED LOOP比HASH JOIN优势的地方。  
但是，如果恰好第一张表很小，对这张表的全扫描的代价极低，会显得HASH JOIN效率更高。  
如果两张表的大小相近，或者Oracle错误的选择了先扫描大表，则使用HASH JOIN的效率就会低得多。  
  
因此对于表连接来说，在写分页查询的时候，可以考虑增加FIRST\_ROWS提示，它会导致CBO选择NESTED LOOP，有助于更快的将查询结果返回。  
其实，不光是表连接，对于所有的分页查询都可以加上FIRST\_ROWS提示。  
不过需要注意的时，分页查询的目标是尽快的返回前N条记录，因此，无论是ROWNUM还是FIRST\_ROWS机制都是提高前几页的查询速度，  
对于分页查询的最后几页，采用HASH JOIN的方式，执行效率几乎没有任何改变，而采用NESTED LOOP方式，则效率严重下降，而且远远低于HASH JOIN的方式。  
  
**排序列不唯一所带来的问题**  
如果用来排序的列不唯一，也就是存在值相等的行，可能会造成第一次在前10条返回记录中，某行数据出现了，而第二次在11到第20条记录中，某行数据又出现了。一条数据重复出现两次，就必然意味着有数据在两次查询中都不会出现。  
其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序[算法][Link 1]不具有稳定性，也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。  
解决这个问题其实也很简单。有两种方法可以考虑。  
1）在使用不唯一的字段排序时，后面跟一个唯一的字段。  
一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟ROWID也可以。这种方法最简单，且对性能的影响最小。  
2）另一种方法就是使用前面给出过多次的BETWEEN AND的方法。  
这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。  
但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低  
  
**[测试][Link 2]结果**  
下面做一些测试，按照如下步骤准备数据：

**\[sql\]**  [view plain][]   [copy][view plain]

1.  **CREATE** **TABLE** T **AS** **SELECT** \* **FROM** DBA\_USERS;  
2.  **CREATE** **TABLE** T1 **AS** **SELECT** \* **FROM** DBA\_SOURCE;  
3.  **ALTER** **TABLE** T **ADD** **CONSTRAINT** PK\_T **PRIMARY** **KEY** (USERNAME);  
4.  **ALTER** **TABLE** T1 **ADD** **CONSTRAINT** FK\_T1\_OWNER **FOREIGN** **KEY** (OWNER) **REFERENCES** T(USERNAME);  
5.  **CREATE** **INDEX** IND\_T1\_OWNER **ON** T1(OWNER);  
6.  **EXEC** DBMS\_STATS.GATHER\_TABLE\_STATS(USER, 'T')  
7.  **EXEC** DBMS\_STATS.GATHER\_TABLE\_STATS(USER, 'T1')  
8.  **set** autotrace traceonly  
9.  **set** timing **on**

现在表格T中有37行数据，表格T1中有623K行数据。  
  
**比较格式1和格式2的查询计划**

**\[sql\]**  [view plain][]   [copy][view plain]

1.  \--查询语句1  
2.  **SELECT** \* **FROM**     
3.  (    
4.  **SELECT** A.\*, ROWNUM RN     
5.  **FROM** (**SELECT** \* **FROM** T1) A     
6.  )    
7.  **WHERE** RN BETWEEN 21 AND 40;  
8.  \--查询语句2  
9.  **SELECT** \* **FROM**     
10. (    
11. **SELECT** A.\*, ROWNUM RN     
12. **FROM** (**SELECT** \* **FROM** T1) A     
13. **WHERE** ROWNUM <= 40    
14. )    
15. **WHERE** RN >= 21;

<table style="width:900px; height:257px"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>执行计划</td> 
   <td>执行时间</td> 
   <td>统计信息</td> 
  </tr> 
  <tr> 
   <td>查询语句1</td> 
   <td> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">----------------------------------------------------------<br> Plan hash value: 3921461035</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">----------------------------------------------------------------------------<br> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br> ----------------------------------------------------------------------------<br> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 623K|&nbsp; 1231M|&nbsp; 2879&nbsp;&nbsp; (1)| 00:00:35 |<br> |*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 623K|&nbsp; 1231M|&nbsp; 2879&nbsp;&nbsp; (1)| 00:00:35 |<br> |&nbsp;&nbsp; 2 |&nbsp;&nbsp; COUNT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL| T1&nbsp;&nbsp; |&nbsp;&nbsp; 623K|&nbsp;&nbsp;&nbsp; 59M|&nbsp; 2879&nbsp;&nbsp; (1)| 00:00:35 |<br> ----------------------------------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">Predicate Information (identified by operation id):<br> ---------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">&nbsp;&nbsp; 1 - filter("RN"&lt;=40 AND "RN"&gt;=21)</span></p> </td> 
   <td><span style="font-size:10px">00: 00: 02.40</span></td> 
   <td><span style="font-size:10px">1&nbsp; recursive calls<br> 0&nbsp; db block gets<br> 10441&nbsp; consistent gets<br> 10435&nbsp; physical reads<br> 0&nbsp; redo size<br> 1720&nbsp; bytes sent via SQL*Net to client<br> 431&nbsp; bytes received via SQL*Net from client<br> 3&nbsp; SQL*Net roundtrips to/from client<br> 0&nbsp; sorts (memory)<br> 0&nbsp; sorts (disk)<br> 20&nbsp; rows processed</span></td> 
  </tr> 
  <tr> 
   <td>查询语句2</td> 
   <td> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">----------------------------------------------------------------------------<br> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br> ----------------------------------------------------------------------------<br> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 82800 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 |<br> |*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 82800 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 |<br> |*&nbsp; 2 |&nbsp;&nbsp; COUNT STOPKEY&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL| T1&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 |&nbsp; 4000 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 |<br> ----------------------------------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">Predicate Information (identified by operation id):<br> ---------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">&nbsp;&nbsp; 1 - filter("RN"&gt;=21)<br> &nbsp;&nbsp; 2 - filter(ROWNUM&lt;=40)</span></p> </td> 
   <td><span style="font-size:10px">00: 00: 00.03</span></td> 
   <td><span style="font-size:10px">0&nbsp; recursive calls<br> 0&nbsp; db block gets<br> 6&nbsp; consistent gets<br> 20&nbsp; physical reads<br> 0&nbsp; redo size<br> 1720&nbsp; bytes sent via SQL*Net to client<br> 431&nbsp; bytes received via SQL*Net from client<br> 3&nbsp; SQL*Net roundtrips to/from client<br> 0&nbsp; sorts (memory)<br> 0&nbsp; sorts (disk)<br> 20&nbsp; rows processed</span></td> 
  </tr> 
 </tbody> 
</table>

**关联查询**

**\[sql\]**  [view plain][]   [copy][view plain]

1.  \--查询语句1  
2.  **SELECT** \* **FROM**     
3.  (    
4.  **SELECT** A.\*, ROWNUM RN     
5.  **FROM** (**SELECT** \* **FROM** T, T1 **WHERE** T.USERNAME = T1.OWNER) A     
6.  **WHERE** ROWNUM <= 40    
7.  )    
8.  **WHERE** RN >= 21;  
9.  \--查询语句2  
10. **SELECT** /\*+ FIRST\_ROWS \*/ \* **FROM**     
11. (    
12. **SELECT** A.\*, ROWNUM RN     
13. **FROM** (**SELECT** \* **FROM** T, T1 **WHERE** T.USERNAME = T1.OWNER) A     
14. **WHERE** ROWNUM <= 40    
15. )    
16. **WHERE** RN >= 21;  
17. \--或者  
18. **SELECT** \* **FROM**     
19. (    
20. **SELECT** A.\*, ROWNUM RN     
21. **FROM** (**SELECT** /\*+ USE\_NL(T T1) \*/ \* **FROM** T, T1 **WHERE** T.USERNAME = T1.OWNER) A     
22. **WHERE** ROWNUM <= 40    
23. )    
24. **WHERE** RN >= 21;

可以看到默认是采用hash join,改用nested loop join方式似乎效率并没有明显提高，但是这是由于表T比较小只有34行，所以hash join的第一步即使对T进行全表扫描而无法应用stopkey，效率也很高。

<table style="width:900px; height:257px"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>执行计划</td> 
   <td>执行时间</td> 
   <td>统计信息</td> 
  </tr> 
  <tr> 
   <td>查询语句1</td> 
   <td> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">-----------------------------------------------------------------------------<br> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br> -----------------------------------------------------------------------------<br> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 |&nbsp;&nbsp; 165K|&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp; (17)| 00:00:01 |<br> |*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 |&nbsp;&nbsp; 165K|&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp; (17)| 00:00:01 |<br> |*&nbsp; 2 |&nbsp;&nbsp; COUNT STOPKEY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |*&nbsp; 3 |&nbsp;&nbsp;&nbsp; HASH JOIN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 12400 |&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp; (17)| 00:00:01 |<br> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL| T&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 34 |&nbsp; 3740 |&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; (0)| 00:00:01 |<br> |&nbsp;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL| T1&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 |&nbsp; 4000 |&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp; (0)| 00:00:01 |<br> -----------------------------------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">Predicate Information (identified by operation id):<br> ---------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">&nbsp;&nbsp; 1 - filter("RN"&gt;=21)<br> &nbsp;&nbsp; 2 - filter(ROWNUM&lt;=40)<br> &nbsp;&nbsp; 3 - access("T"."USERNAME"="T1"."OWNER")</span></p> </td> 
   <td><span style="font-size:10px">00: 00: 00.04</span></td> 
   <td><span style="font-size:10px">0&nbsp;recursive calls<br> 0 db block gets<br> 9 consistent gets<br> 20 physical reads<br> 0 redo size<br> 2927 bytes sent via SQL*Net to client<br> 431 bytes received via SQL*Net from client<br> 3 SQL*Net roundtrips to/from client<br> 0 sorts (memory)<br> 0 sorts (disk)<br> 20 rows processed</span></td> 
  </tr> 
  <tr> 
   <td>查询语句2</td> 
   <td> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">-----------------------------------------------------------------------------------------------<br> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br> -----------------------------------------------------------------------------------------------<br> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 |&nbsp;&nbsp; 165K| 13627&nbsp;&nbsp; (1)| 00:02:44 |<br> |*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 |&nbsp;&nbsp; 165K| 13627&nbsp;&nbsp; (1)| 00:02:44 |<br> |*&nbsp; 2 |&nbsp;&nbsp; COUNT STOPKEY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; NESTED LOOPS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; NESTED LOOPS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 623K|&nbsp;&nbsp; 124M| 13627&nbsp;&nbsp; (1)| 00:02:44 |<br> |&nbsp;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | T&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 34 |&nbsp; 3740 |&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; (0)| 00:00:01 |<br> |*&nbsp; 6 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX RANGE SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | IND_T1_OWNER | 36684 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 91&nbsp;&nbsp; (0)| 00:00:02 |<br> |&nbsp;&nbsp; 7 |&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS BY INDEX ROWID| T1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18342 |&nbsp; 1791K|&nbsp;&nbsp; 710&nbsp;&nbsp; (1)| 00:00:09 |<br> -----------------------------------------------------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">Predicate Information (identified by operation id):<br> ---------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">&nbsp;&nbsp; 1 - filter("RN"&gt;=21)<br> &nbsp;&nbsp; 2 - filter(ROWNUM&lt;=40)<br> &nbsp;&nbsp; 6 - access("T"."USERNAME"="T1"."OWNER")</span></p> </td> 
   <td><span style="font-size:10px">00: 00: 00.01</span></td> 
   <td><span style="font-size:10px">1&nbsp;recursive calls<br> 0 db block gets<br> 14&nbsp;consistent gets<br> 0&nbsp;physical reads<br> 0 redo size<br> 2927&nbsp;bytes sent via SQL*Net to client<br> 431 bytes received via SQL*Net from client<br> 3 SQL*Net roundtrips to/from client<br> 0 sorts (memory)<br> 0 sorts (disk)<br> 20 rows processed</span></td> 
  </tr> 
 </tbody> 
</table>

现在增大表T，

**\[sql\]**  [view plain][]   [copy][view plain]

1.  **ALTER** **TABLE** T **MODIFY**(USER\_ID NULL, ACCOUNT\_STATUS NULL, DEFAULT\_TABLESPACE NULL,  
2.  TEMPORARY\_TABLESPACE NULL, CREATED NULL, PROFILE NULL);  
3.  **INSERT** **INTO** T(USERNAME) **SELECT** ('USER' || **LEVEL**) **FROM** DUAL **CONNECT** **BY** **LEVEL** < 100000;  
4.  **COMMIT**;  
5.  **EXEC** DBMS\_STATS.GATHER\_TABLE\_STATS(USER, 'T')

然后重新测试语句1，会发现现在oracle已经改成用nested loop join了。  
因此现在语句1和语句2的效果等同了。可以使用 USE\_HASH(T T1) HINT强制使用hash join，结果做下对比，会发现hash join的效率低于nested loop join，读数据发生的IO（consistent gets+physical reads）大大增加了.  
可以看到CBO是相当[智能][Link 3]了。  
  
**含排序的查询**  
含排序操作的分页查询。可以简单的将查询分为两种不同情况，第一种排序列就是索引列，这种可以利用索引读取，第二种排序列没有索引。  
第一种情况又可以细分为：完全索引扫描和通过索引扫描定位到表记录两种情况。无论是那种情况，都可以通过索引的全扫描来避免排序的产生。  
第二种情况下，排序不可避免，但是利用给出分页格式，Oracle不会对所有数据进行排序，而是只排序前N条记录。

**\[sql\]**  [view plain][]   [copy][view plain]

1.  \--查询语句1，排序列就是索引列.注意这里需要加上OWNER IS NOT NULL,否则由于OWNER列不是NOT NULL,会导致索引无法使用。  
2.  **SELECT** \* **FROM**  
3.  (  
4.  **SELECT** A.\*, ROWNUM RN  
5.  **FROM** (**SELECT** \* **FROM** T1 **WHERE** OWNER **IS** NOT NULL **ORDER** **BY** OWNER) A  
6.  **WHERE** ROWNUM <= 40  
7.  )  
8.  **WHERE** RN >= 21;  
9.    
10. \--查询语句2，排序列没有索引  
11. **SELECT** \* **FROM**  
12. (  
13. **SELECT** A.\*, ROWNUM RN  
14. **FROM** (**SELECT** \* **FROM** T1 **ORDER** **BY** **NAME**) A  
15. **WHERE** ROWNUM <= 40  
16. )  
17. **WHERE** RN >= 21;  
18.   
19.   
20. \--查询语句3，排序列没有索引  
21. **SELECT** \* **FROM**  
22. (  
23. **SELECT** A.\*, ROWNUM RN  
24. **FROM** (**SELECT** \* **FROM** T1 **ORDER** **BY** **NAME**) A  
25. )  
26. **WHERE** RN BETWEEN 21 AND 40;

<table style="width:900px; height:257px"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>执行计划</td> 
   <td>执行时间</td> 
   <td>统计信息</td> 
  </tr> 
  <tr> 
   <td>查询语句1</td> 
   <td> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">-----------------------------------------------------------------------------------------------<br> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br> -----------------------------------------------------------------------------------------------<br> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 82800 |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br> |*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 82800 |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br> |*&nbsp; 2 |&nbsp;&nbsp; COUNT STOPKEY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 82280 |&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS BY INDEX ROWID| T1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp;&nbsp;&nbsp; 62M|&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp; (0)| 00:00:01 |<br> |*&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX FULL SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | IND_T1_OWNER |&nbsp;&nbsp;&nbsp; 40 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp; (0)| 00:00:01 |<br> -----------------------------------------------------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">Predicate Information (identified by operation id):<br> ---------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">&nbsp;&nbsp; 1 - filter("RN"&gt;=21)<br> &nbsp;&nbsp; 2 - filter(ROWNUM&lt;=40)<br> &nbsp;&nbsp; 5 - filter("OWNER" IS NOT NULL)</span></p> *排序列就是索引列，可以看到通过索引的全扫描来避免了排序的产生。</td> 
   <td><span style="font-size:10px">00: 00: 00.01</span></td> 
   <td><span style="font-size:10px">1&nbsp;recursive calls<br> 0 db block gets<br> 8&nbsp;consistent gets<br> 1&nbsp;physical reads<br> 0 redo size<br> 1682&nbsp;bytes sent via SQL*Net to client<br> 427 bytes received via SQL*Net from client<br> 3 SQL*Net roundtrips to/from client<br> 0 sorts (memory)<br> 0 sorts (disk)<br> 20 rows processed</span></td> 
  </tr> 
  <tr> 
   <td>查询语句2</td> 
   <td> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">-----------------------------------------------------------------------------------------<br> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes |TempSpc| Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br> -----------------------------------------------------------------------------------------<br> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 82800 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 40 | 82800 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |*&nbsp; 2 |&nbsp;&nbsp; COUNT STOPKEY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp; 1268M|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |*&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; SORT ORDER BY STOPKEY|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp;&nbsp;&nbsp; 62M|&nbsp;&nbsp;&nbsp; 72M| 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |&nbsp;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL&nbsp;&nbsp; | T1&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp;&nbsp;&nbsp; 62M|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 3023&nbsp;&nbsp; (1)| 00:00:37 |<br> -----------------------------------------------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">Predicate Information (identified by operation id):<br> ---------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">&nbsp;&nbsp; 1 - filter("RN"&gt;=21)<br> &nbsp;&nbsp; 2 - filter(ROWNUM&lt;=40)<br> &nbsp;&nbsp; 4 - filter(ROWNUM&lt;=40)</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> *排序列没有索引，<span style="font-family:Simsun">排序不可避免。带STOPKEY的ORDER BY，排序操作放到了内存中，<br> 在<a href="http://lib.csdn.net/base/hadoop" title="Hadoop知识库" style="color:rgb(223,52,52); font-weight:bold" rel="nofollow">大数据</a>量需要排序的情况下，要比不带STOPKEY排序的效率高得多。</span></p> </td> 
   <td><span style="font-size:10px">00: 00: 01.32</span></td> 
   <td><span style="font-size:10px">1 recursive calls<br> 0 db block gets<br> 10973 consistent gets<br> 10969 physical reads<br> 0 redo size<br> 2529 bytes sent via SQL*Net to client<br> 427 bytes received via SQL*Net from client<br> 3 SQL*Net roundtrips to/from client<br> 1&nbsp;sorts (memory)<br> 0 sorts (disk)<br> 20 rows processed</span></td> 
  </tr> 
  <tr> 
   <td>查询语句3</td> 
   <td> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">--------------------------------------------------------------------------------------<br> | Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name | Rows&nbsp; | Bytes |TempSpc| Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |<br> --------------------------------------------------------------------------------------<br> |&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp; 1276M|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp; 1276M|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |&nbsp;&nbsp; 2 |&nbsp;&nbsp; COUNT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br> |&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp; 1268M|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; SORT ORDER BY&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp;&nbsp;&nbsp; 62M|&nbsp;&nbsp;&nbsp; 72M| 18077&nbsp;&nbsp; (1)| 00:03:37 |<br> |&nbsp;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL| T1&nbsp;&nbsp; |&nbsp;&nbsp; 646K|&nbsp;&nbsp;&nbsp; 62M|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 3023&nbsp;&nbsp; (1)| 00:00:37 |<br> --------------------------------------------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">Predicate Information (identified by operation id):<br> ---------------------------------------------------</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:10px">&nbsp;&nbsp; 1 - filter("RN"&lt;=40 AND "RN"&gt;=21)</span></p> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> *排序列没有索引，<span style="font-family:Simsun">排序不可避免，不带STOPKEY</span>，<br> <span style="font-family:Simsun">进行的数据的全排序，排序数据量大，排序操作不得不在磁盘上完成，因此耗时比较多。</span></p> </td> 
   <td><span style="font-size:10px">00: 00: 05.31</span></td> 
   <td><span style="font-size:10px">72&nbsp;recursive calls<br> 26&nbsp;db block gets<br> 10973 consistent gets<br> 19933&nbsp;physical reads<br> 0 redo size<br> 6489&nbsp;bytes sent via SQL*Net to client<br> 427 bytes received via SQL*Net from client<br> 3 SQL*Net roundtrips to/from client<br> 0 sorts (memory)<br> 1&nbsp;sorts (disk)<br> 20 rows processed</span></td> 
  </tr> 
 </tbody> 
</table>

**排序列不唯一所带来的问题**

**\[sql\]**  [view plain][]   [copy][view plain]

1.  tony@ORCL1> **CREATE** **TABLE** TEST **AS** **SELECT** ROWNUM ID, A.\* **FROM** DBA\_OBJECTS A;  
2.  **Table** created.  
3.    
4.  tony@ORCL1> **EXEC** DBMS\_STATS.GATHER\_TABLE\_STATS(USER, 'TEST');  
5.  PL/SQL **procedure** successfully completed.  
6.    
7.  tony@ORCL1> **COLUMN** OBJECT\_NAME FORMAT A30  
8.  tony@ORCL1> **SELECT** \* **FROM**  
9.    2  (  
10.   3  **SELECT** A.\*, ROWNUM RN  
11.   4  **FROM** (**SELECT** ID, OWNER, OBJECT\_NAME **FROM** TEST **WHERE** OWNER **IS** NOT NULL **ORDER** **BY** OWNER) A  
12.   5  **WHERE** ROWNUM <= 10  
13.   6  )  
14.   7  **WHERE** RN >= 1;  
15.   
16.         ID OWNER                          OBJECT\_NAME                            RN  
17. \---------- ------------------------------ ------------------------------ ----------  
18.      69170 APEX\_030200                    WWV\_FLOW\_INIT\_HTP\_BUFFER                1  
19.      69179 APEX\_030200                    WWV\_HTF                                 2  
20.      69178 APEX\_030200                    WWV\_FLOW\_LANG                           3  
21.      69177 APEX\_030200                    WWV\_FLOW\_UTILITIES                      4  
22.      69176 APEX\_030200                    VC4000ARRAY                             5  
23.      69175 APEX\_030200                    WWV\_FLOW\_SECURITY                       6  
24.      69174 APEX\_030200                    WWV\_FLOW                                7  
25.      69173 APEX\_030200                    HTMLDB\_ITEM                             8  
26.      69172 APEX\_030200                    WWV\_FLOW\_GLOBAL                         9  
27.      69171 APEX\_030200                    WWV\_FLOW\_IMAGE\_PREFIX                  10  
28.   
29. 10 **rows** selected.  
30.   
31. tony@ORCL1> **SELECT** \* **FROM**  
32.   2  (  
33.   3  **SELECT** A.\*, ROWNUM RN  
34.   4  **FROM** (**SELECT** ID, OWNER, OBJECT\_NAME **FROM** TEST **WHERE** OWNER **IS** NOT NULL **ORDER** **BY** OWNER) A  
35.   5  **WHERE** ROWNUM <= 20  
36.   6  )  
37.   7  **WHERE** RN >= 11;  
38.   
39.         ID OWNER                          OBJECT\_NAME                            RN  
40. \---------- ------------------------------ ------------------------------ ----------  
41.      69180 APEX\_030200                    WWV\_HTP                                11  
42.      69179 APEX\_030200                    WWV\_HTF                                12  
43.      69178 APEX\_030200                    WWV\_FLOW\_LANG                          13  
44.      69177 APEX\_030200                    WWV\_FLOW\_UTILITIES                     14  
45.      69176 APEX\_030200                    VC4000ARRAY                            15  
46.      69175 APEX\_030200                    WWV\_FLOW\_SECURITY                      16  
47.      69174 APEX\_030200                    WWV\_FLOW                               17  
48.      69173 APEX\_030200                    HTMLDB\_ITEM                            18  
49.      69172 APEX\_030200                    WWV\_FLOW\_GLOBAL                        19  
50.      69171 APEX\_030200                    WWV\_FLOW\_IMAGE\_PREFIX                  20  
51.   
52. 10 **rows** selected.  
53. \--可以看到，有多个ID在两次查询中都出现了。  
54. \--通过加上ID作为排序列解决这个问题。  
55.   
56. tony@ORCL1> **SELECT** \* **FROM**  
57.   2  (  
58.   3  **SELECT** A.\*, ROWNUM RN  
59.   4  **FROM** (**SELECT** ID, OWNER, OBJECT\_NAME **FROM** TEST **WHERE** OWNER **IS** NOT NULL **ORDER** **BY** OWNER, ID) A  
60.   5  **WHERE** ROWNUM <= 10  
61.   6  )  
62.   7  **WHERE** RN >= 1;  
63.   
64.         ID OWNER                          OBJECT\_NAME                            RN  
65. \---------- ------------------------------ ------------------------------ ----------  
66.      69170 APEX\_030200                    WWV\_FLOW\_INIT\_HTP\_BUFFER                1  
67.      69171 APEX\_030200                    WWV\_FLOW\_IMAGE\_PREFIX                   2  
68.      69172 APEX\_030200                    WWV\_FLOW\_GLOBAL                         3  
69.      69173 APEX\_030200                    HTMLDB\_ITEM                             4  
70.      69174 APEX\_030200                    WWV\_FLOW                                5  
71.      69175 APEX\_030200                    WWV\_FLOW\_SECURITY                       6  
72.      69176 APEX\_030200                    VC4000ARRAY                             7  
73.      69177 APEX\_030200                    WWV\_FLOW\_UTILITIES                      8  
74.      69178 APEX\_030200                    WWV\_FLOW\_LANG                           9  
75.      69179 APEX\_030200                    WWV\_HTF                                10  
76.   
77. 10 **rows** selected.  
78.   
79. tony@ORCL1> **SELECT** \* **FROM**  
80.   2  (  
81.   3  **SELECT** A.\*, ROWNUM RN  
82.   4  **FROM** (**SELECT** ID, OWNER, OBJECT\_NAME **FROM** TEST **WHERE** OWNER **IS** NOT NULL **ORDER** **BY** OWNER, ID) A  
83.   5  **WHERE** ROWNUM <= 20  
84.   6  )  
85.   7  **WHERE** RN >= 11;  
86.   
87.         ID OWNER                          OBJECT\_NAME                            RN  
88. \---------- ------------------------------ ------------------------------ ----------  
89.      69180 APEX\_030200                    WWV\_HTP                                11  
90.      69181 APEX\_030200                    ESCAPE\_SC                              12  
91.      69182 APEX\_030200                    WWV\_FLOW\_META\_DATA                     13  
92.      69183 APEX\_030200                    WWV\_FLOW\_TEMPLATES\_UTIL                14  
93.      69184 APEX\_030200                    WWV\_RENDER\_CALENDAR2                   15  
94.      69185 APEX\_030200                    WWV\_RENDER\_CHART2                      16  
95.      69186 APEX\_030200                    WWV\_FLOW\_CHECK                         17  
96.      69187 APEX\_030200                    WWV\_RENDER\_REPORT3                     18  
97.      69188 APEX\_030200                    WWV\_FLOW\_PAGE\_CACHE\_API                19  
98.      69189 APEX\_030200                    WWV\_FLOW\_RENDER\_QUERY                  20  
99.   
100. 10 **rows** selected.

[view plain]: http://blog.csdn.net/fw0124/article/details/42737671#
[Link 1]: http://lib.csdn.net/base/datastructure
[Link 2]: http://lib.csdn.net/base/softwaretest
[Link 3]: http://lib.csdn.net/base/aiplanning

发表评论取消回复

表情：

评论列表（有 0 条评论，752人围观）

还没有评论，来说两句吧...

相关阅读

相关 Oracle中的分页查询

目录一、使用ROWNUM函数实现分页查询命令格式：注意事项：二、使用OFFSET和FETCH NEXT语句实现分页查询命令格式：注意事项：四、使用子

淩亂°似流年/ 2024年03月22日 14:58/ 0 赞/ 71 阅读

相关 oracle分页查询

第一步：实体类准备分页查询条件封装 / 分页条件 / @Setter @Getter public

古城微笑少年丶/ 2023年10月05日 19:52/ 0 赞/ 32 阅读

相关 oracle分页查询

[1、分页查寻入门][1] [2、ORACLE分页查询SQL语句(最有效的分页)][2_ORACLE_SQL] 如果项目中使用了mybatisPlus使用分页查询方法

怼烎@/ 2023年10月01日 16:32/ 0 赞/ 26 阅读

相关 Oracle分页查询

oracle 分页 `oracle在11c中所使用的分页是通过rownum或者rowid进行实现，但是这样的方式实现起来相对麻烦，所以在oracle更新到12c时就引入了

向右看齐/ 2022年09月08日 05:00/ 0 赞/ 283 阅读

相关 oracle分页查询

oracle分页查询 1.一个通用的格式如下： \--rownum 是记录序号(1，2，3...)， \--注意：如果 SQL 语句中有 ORDER BY ... 排序

小咪咪/ 2022年08月09日 21:28/ 0 赞/ 291 阅读

相关 Oracle分页查询

oracle分页查询： SELECT FROM ( SELECT ROWNUM AS rowno,

谁践踏了优雅/ 2022年07月11日 05:30/ 0 赞/ 307 阅读

相关 oracle分页查询的效率分析

ROWNUM 可能都知道ROWNUM只适用于小于或小于等于，如果进行等于判断，那么只能等于1，不能进行大于的比较。 ROWNUM是oracle系统顺序分配为从

约定不等于承诺〃/ 2022年06月13日 03:43/ 0 赞/ 753 阅读

相关 Oracle查询分页

SELECT FROM ( SELECT A., ROWNUM RN FROM (SELECT FROM TABLE_NAME) A ) WHERE RN BETW

淩亂°似流年/ 2022年06月06日 06:00/ 0 赞/ 278 阅读

相关 Oracle的分页查询

快速通道: 结论 Oracle的分页查询使用很广泛,也较为简单,主要根据是伪劣值rownum来实现,具体分两步走: 1. 首先要获得表中的num (这里借用已

青旅半醒/ 2022年02月19日 14:07/ 0 赞/ 293 阅读

相关 Oracle分页查询语句

前言本文是整理别人的一篇文章，介绍了Oracle数据库的分页查询原文地址：[http://blog.itpub.net/4227/][http_blog.itpu

落日映苍穹つ/ 2021年09月14日 14:44/ 0 赞/ 493 阅读