ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Rownum与Order by(二)

Rownum与Order by(二)

原创 Linux操作系统 作者:realkid4 时间:2011-02-13 22:50:01 0 删除 编辑

 

那么,我们是不是要放弃这个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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7676779