rownum详解(结果集陷阱与性能陷阱) - 10g

jy01649210

rownum是一个伪列，只有有结果记录时，rownum才有相应数据，因此对它的使用不能像普通列那样。

使用陷阱：

一，不能对rownum直接使用>(大于等于1的数值)，>=(大于等于2的数值)，=(大于等于2的数值)，否则无结果。

这是因为：

1、ROWNUM 是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM

数值；

2、返回结果记录的ROWNUM 是从1 开始排序的，因此第一条始终是1;

这样，当查询到第一条记录时，该记录的ROWNUM 为1，但条件要求ROWNUM>1，因此不

符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其

ROWNUM 还是为1，如此循环，就不会产生结果。

二，rownum和order by一起使用，只有当order by字段是主键时才先排序后计算rownum，原因同一。

例：

select bdm_id,rownum as rn from FACT_FA_BDM_ACTIVITY a where rownum <= 5 order by bdm_id；--task_id主键

  BDM_ID RN
1 6573 3
2 6573 2
3 6983 1
4 7098 4
5 7180 5

三，order by的字段有多个重复值，根据rownum取结果数据时将是不稳定的

例：

select task_id,bdm_id from (select a.*,rownum as rn from (select * from FACT_FA_BDM_ACTIVITY order by bdm_id) a where rownum <= 10)  t where t.rn >= 1；

    TASK_ID BDM_ID
1 949731 5175
2 949732 5175
3 949763 5175
4 949765 5175
5 950665 5534
6 950674 5534
7 950679 5534
8 950819 5534
9 950796 5534
10 950794 5534

select task_id,bdm_id from (select a.*,rownum as rn from (select * from FACT_FA_BDM_ACTIVITY order by bdm_id) a where rownum <= 20)  t where t.rn >= 11；

    TASK_ID BDM_ID
1 950819 5534
2 950794 5534
3 950838 5534
4 949664 5534
5 949602 5534
6 951049 5534
7 951046 5534
8 951043 5534
9 951023 5534
10 950974 5534

 

看查询计划有SORT ORDER BY STOPKEY，优化器用的类似快排的算法，所以排序结果是不稳定的。

 

性能陷阱：

例：(假设子查询是个视图)

select v.* from (select rownum as rn, a.* from FACT_FA_BDM_ACTIVITY a) v where v.task_id = 10

该sql的执行计划是TABLE ACCESS FULL，这是因为子查询中加入了rownum，会先产生子查询的结果集，优化器未能先进行查询重写，不走主索引。