Oracle rownum 伪列

Oracle 伪列 rownum


ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。利用ROWNUM，我们可以生产一些原先难以实现的结果输出，但因为它是伪列的这个特殊性，我们在使用时也需要注意一些事项，不要掉入“陷阱”。


一、特殊结果输出


1.Top N 结果输出


select * from t_test4 where rownum <= 5;
但是，如果你希望对一个排序结果取Top N数据的话，使用ROWNUM存在一些“陷阱”。


1.2 分页查询


下面三种方法得到第6-10条记录
select * from (select a.*, rownum as rn from css_bl_view a where capture_phone_num='925-4604800') b 
where b.rn between 6 and 10;


select * from (select a.*, rownum as rn from css_bl_view a where capture_phone_num='925-4604800' and rownum<=10) b
where b.rn > 5;


select * from css_bl_view a 
where capture_phone_num = '925-4604800' and rownum<=10 
minus
select * from css_bl_view a 
where capture_phone_num = '925-4604800' and rownum<=5


1.3 分组子排序


SELECT DECODE(nu - min_sno, 0, a.owner, null) owner,
       DECODE(nu - min_sno, 0, 1, nu + 1 - min_sno) sno,
       a.table_name
  FROM (select l.*, rownum nu
          from (SELECT owner, table_name
                  FROM dba_tables
                 ORDER BY owner, table_name) l) a,
       (SELECT owner, MIN(rownum) min_sno
          FROM (SELECT owner, table_name
                  FROM dba_tables
                 ORDER BY owner, table_name)
         GROUP BY owner
         ORDER BY owner) b
 WHERE a.owner = b.owner




二、性能


我们很多程序员在确认某个表中是否有相应数据时，喜欢加上ROWNUM=1，其思路就是
只要存在一条数据就说明有相应数据，查询就可以直接返回了，这样就能提高性能了。但
是在10G 之前，使用ROWNUM=1 是不能达到预期的性能效果的，而是需要通过<2 或<=1 作
为过滤条件才能达到预期效果。




三、ROWNUM的使用“陷阱”


由于ROWNUM 是一个伪列，只有有结果记录时，ROWNUM 才有相应数据，因此对它的使
用不能向普通列那样使用，否则就会陷入一些“陷阱”当中。


3.1 对ROWNUM进行>、>=、=操作


不能对ROWNUM 使用>（大于或等于1的数值）、>=（大于1的数值）、=（0或者大于1的数值），否则无结果。


select l.*,rownum from tb_snacks_analyse_list l where l.sum_time='201212' and rownum>1 --无值
select l.*,rownum from tb_snacks_analyse_list l where l.sum_time='201212' and rownum>=2 --无值
select l.*,rownum from tb_snacks_analyse_list l where l.sum_time='201212' and rownum=2 --无值


这是因为：
1、ROWNUM 是伪列，必须要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值；
2、返回结果记录的ROWNUM 是从1 开始排序的，因此第一条始终是1;这样，当查询到第一条记录时，该记录的ROWNUM 为1，但条件要求ROWNUM>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM 还是为1，如此循环，就不会产生结果。


上述查询可以通过子查询来替代：
select * from (select l.*,rownum cn from tb_snacks_analyse_list l where l.sum_time='201212') where cn>1


3.2 ROWNUM 和Order BY


select l.*,rownum cn from tb_snacks_analyse_list l where l.sum_time='201212' rownum<=5 order by create_table


要注意的是：在使用ROWNUM 时，只有当Order By 的字段是主键时，查询结果才会先排序再计算ROWNUM,但是，对非主键字段OBJECT_NAME 进行排序时却不是。
出现这种混乱的原因是：Oracle 先按物理存储位置（rowid）顺序取出满足rownum 条件的记录，即物理位置上的前5 条数据，然后在对这些数据按照Order By 的字段进行排序，而不是我们所期望的先排序、再取特定记录数。


select * from (select l.*,rownum cn from tb_snacks_analyse_list l where l.sum_time='201212' order by create_table) where cn>1 rownum<=5


3.3 排序分页


当对存在重复值的字段排序后再分页输出，我们很容易会陷入到另外一个“陷阱”。


t_test1表owner字段内容一致，object_name内容唯一。
OWNER    OBJECT_NAME
-------- --------------
AFWOWNER AFWADAPTER
AFWOWNER AFWADAPTERFQN_PK
AFWOWNER AFWADAPTERCONFIGURATION
AFWOWNER AFWADAPTERCONFIGURATION_PK


select owner, object_name 
from (select a.*, rownum as rn 
      from (select owner, object_name from t_test1 order by owner) a
      where rownum <= 10)
where rn >= 1;


select owner, object_name 
from (select a.*, rownum as rn 
      from (select owner, object_name from t_test1 order by owner) a
      where rownum <= 20)
where rn >= 11;


第一个查询结果所得到的记录会有部分存在于第二个查询结果中，出现着这种情况大多由优化器采用了“SORT (ORDER BY STOPKEY)”引起。
“SORT (ORDER BY STOPKEY)”不需要对所有数据进行排序，而是只要找出结果集中的按特定顺序的最前N 条记录，
一旦找出了这N 条记录，就无需再对剩下的数据进行排序，而直接返回结果。这种算法我们可以视为是“快速排序”算法的变种。
快速排序算法的基本思想是：先将数据分2 组集合，保证第一集合中的每个数据都大于第二个集合中每个数据，
然后再按这个原则对每个集合进行递归分组，直到集合的单位最小。在进行“SORT(ORDER BY STOPKEY)”时，
首先找出N 条数据（这些数据并没有做排序）放在第一组，保证第一组的数据都大于第二组的数据，然后只对第一组数据进行递归。
可以看到，基于这样的算法基础上，如果N 的数值不同，数据的分组也不同（如N=20时，第一次分组比例为12:8，然后继续递归；当N=10 时，第一次分组比例为3:7 … …），
这样，在数据的排序字段值都相等时，输出结果的顺序就会因为N 值不同而不同。


如何解决？
（1）、让查询计划避免“SORT (ORDER BY STOPKEY)”，采用“SORT (ORDER BY)”，使数据排序不受ROWNUM 的影响。但这样会使所有数据都做排序。


select owner, object_name 
from (select a.*, rownum as rn 
      from (select owner, object_name, rowid from t_test1 order by owner) a)
where rn <= 10 and rn >= 1;


select owner, object_name 
from (select a.*, rownum as rn 
      from (select owner, object_name, rowid from t_test1 order by owner) a)
where rn <= 20 and rn >= 11;


（2）在排序时，加上一个或多个字段（如主键字段、ROWID），使排序结果具有唯一性：


select owner, object_name 
from (select a.*, rownum as rn 
      from (select owner, object_name, rowid from t_test1 order by owner, object_id) a
      where rownum <= 10)
where rn >= 1;


select owner, object_name 
from (select a.*, rownum as rn 
      from (select owner, object_name, rowid from t_test1 order by owner, object_id) a
      where rownum <= 20)
where rn >= 11;


（3）对排序字段建立索引，并强制使用索引。这样就能利用索引已经建立好的排序结果


create index t_test1_idx1 on t_test1(owner);


select owner, object_name 
from (select a.*, rownum as rn 
      from (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a
      where rownum <= 10)
where rn >= 1;


select owner, object_name 
from (select a.*, rownum as rn 
      from (select /*+index(t T_TEST1_IDX1)*/owner, object_name from t_test1 t order by owner) a
      where rownum <= 20)
where rn >= 11;


3.4 性能陷阱


create index t_test1_idx1 on T_COM(COMP_ID, VIEW_TYPE, DELETED_IND, SH_ID) --复合索引


CREATE OR REPLACE VIEW MY_VIEW AS
SELECT ROWNUM ID,T0.COMP_ID COMPANY_ID,T0.SH_ID B_NUM,T0.REC_UPD_DT 
FROM T_COM T0
WHERE T0.DELETED_IND = 0
AND T0.VIEW_TYPE = 'BK'；


执行
select * from MY_VIEW where company_id='12'; 
看查询计划发现并没有命中索引。仔细分析视图的定义语句，其中包含了对ROWNUM 的查询。ROWNUM 是一个虚字段，只有产生结果集时才会有值。
因此，为保证逻辑结果，优化器并没有将视图查询条件与外部主查询条件合并后再执行查询操作，而是先执行子查询部分（条件不足以命中索引），
得到结果集（执行计划中的第一步Full Table Scan）和对应的ROWNUM值（执行计划中的第二步COUNT）以后再根据外部条件对结果集过滤（执行计划中的第三步FILTER）。
当我们将ROWNUM 从视图中拿掉，执行计划就能像我们之前所预想的命中索引了。




另外
select id, owner, object_name
from (select rownum as id, owner, object_name from t_test1 where object_name like 'TEM%') v
where owner='DEMO';


select id, owner, object_name
from (select rownum as id, owner, object_name from t_test1 where object_name like 'TEM%' and owner='DEMO') v;


上面2句的owner, object_name结果都一致，但是ID不同。