Rownum与Order by（二）

 

那么，我们是不是要放弃这个SQL结构了呢？笔者从网络上获取一个方案，如果将order by后面字段为主键字段，这种SQL结构是可以返回正确的结果的。下面实验：

 

 

SQL> alter table t add constraint pk_t primary key (row_num); //加主键

 

Table altered

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true); //重新收集统计量

 

PL/SQL procedure successfully completed

 

 

SQL> select * from t where rownum<5 order by row_num desc;

 

   ROW_NUM OBJECT_NAME

---------- --------------------------------------------------------------------------------

        21 TAR

        20 SOU

        19 T

        18 PCK_IWB_UNI_CLOSING

 

//返回了正确的结果！SQL语句没有变化！

 

按照主键排序，就没有问题了。我们检查一下这里面的执行计划。

 

 

SQL> explain plan for select * from t where rownum<5 order by row_num desc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 949946574

--------------------------------------------------------------------------------

| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |      |     4 |    52 |     2   (0)| 00:00

|*  1 |  COUNT STOPKEY               |      |       |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    21 |   273 |     2   (0)| 00:00

|   3 |    INDEX FULL SCAN DESCENDING| PK_T |     4 |       |     1   (0)| 00:00

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<5)

 

15 rows selected

 

 

这里的执行计划是这样，order by + 主键之后。CBO选择了读取数据表T的主键PK_T所对应的索引PK_T。索引的叶节点都是有序的，按照DESCENDING逆序读取索引树，取到数据表行rowid的列表。注意，这个rowid的列表是按照row_num逆序的顺序确立的列表。在第2步中，根据rowid列表访问数据表T，获取数据行（此时也是按照row_num逆序的）。最后从第1步的COUNT STOPKEY拦截住四行数据。

 

 

这种方法，借助了两个Oracle特性：其一是主键索引有序的特性，Order by的排序借助已经排好的索引完成；其二是CBO优化器，探索出这样的执行路径。

 

 

一般情况下，还是不要书写这样的SQL。对rownum，标准的做法是将其和order by分开进行处理。借助查询子句，可以获取到正确的结果。

 

//已经取消了主键；

SQL> desc t;

Name        Type          Nullable Default Comments

----------- ------------- -------- ------- --------

ROW_NUM     NUMBER        Y                        

OBJECT_NAME VARCHAR2(128) Y         

 

//将order by单独进行处理，明确表示：先排序，后rownum；

SQL> select * from (select * from t order by row_num desc) where rownum<5;

 

   ROW_NUM OBJECT_NAME

---------- --------------------------------------------------------------------------------

        21 TAR

        20 SOU

        19 T

        18 PCK_IWB_UNI_CLOSING

 

 

这种语法方法，可以在不受order by条件的情况下，也能实现需求。我们一起来看看执行计划。

 

 

SQL> explain plan for select * from (select * from t order by row_num desc) where rownum<5;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3299198703

--------------------------------------------------------------------------------

| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |      |     4 |   316 |     3   (0)| 00:00:01 |

|*  1 |  COUNT STOPKEY          |      |       |       |            |          |

|   2 |   VIEW                  |      |    21 |  1659 |     3   (0)| 00:00:01 |

|*  3 |    SORT ORDER BY STOPKEY|      |    21 |   273 |     3   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL   | T    |    21 |   273 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<5)

   3 - filter(ROWNUM<5)

 

17 rows selected

 

 

这是一个值得我们研究的执行计划，里面有一些有意思的内容。

 

首先，我们看到排序order by转化为操作“SORT ORDER BY STOPKEY”。这个操作是Oracle优化SQL的一个表现。在这个步骤中，加入了filter（rownum<5）。这个操作在view操作内部。说明，虽然我们将rownum<5写在子查询的外面，但Oracle还是将这个条件渗透到了视图化操作中。

 

SORT ORDER BY STOPKEY操作经常能够在rownum+orderby的嵌套查询中看到。这个操作是Oracle处理rownum+orderby的一种优化算法。这个操作本质上很像算法中的快排序。首先是将数据集合分成两个部分（A和B），保证A中排序值都不小于B中排序值。在对A集合分割成两部分，依次进行。数据集B和其他那些小数据集合就不进行排序了。这样操作，只进行较少的排序次数，就可以最快的获取全局最大（或者最小）的几个数据。

 

当然，SORT ORDER BY STOPKEY操作在我们这个SQL中应用是没有问题的。但是并不代表没有缺陷和陷阱。rownum和order by使用最多的场合是分页，使用SORT ORDER BY STOPKEY如果不当，有可能引发其他一些功能上的缺陷。这个问题我们以后找机会谈。

 

其次一个关注点就是VIEW操作。显然，对嵌套的查询，Oracle使用了VIEW操作，将查询结果作为一个视图，提供给外层操作使用。

 

 

最后，就是外层依然使用了count stopkey的操作，对结果集合进行拦截。因为在内层的查询中已经进行排序+拦截操作，可以保证结果是正确的。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-687125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-687125/