登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

rownum和order by 以及index的关系 - daimin's BLOG - CSDN博客  

2009-06-23 20:23:05|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 rownum和order by 以及index的关系收藏

 本篇文章主要讨论rownum和排序以及索引的关系
问题1、rownum正序是指什么?
问题2、rownum和order by语句在一起时,执行的先后顺序是什么?order by语句如何影响rownum正序?
问题3、rownnum与索引之间的关系?

-- Create table
create table DAIMIN
( ID       NUMBER not null,
  PARENTID NUMBER
);
alter table daimin add constraint pk_daimin primary key(id);

SQL> select *from DAIMIN;
        ID   PARENTID
---------- ----------
         1          6
         2          3
         5          1
         3          2


问题1、rownum正序是指什么?
    rownum正序是指rownum的返回是按照1,2,3依次递增,如上面的查询显示的效果

问题2、rownum和order by语句在一起时,执行的先后顺序是什么?order by语句如何影响rownum正序?
下面执行两个语句进行比较:
SQL> select rownum as r, t.ID from daimin t order by t.ID;
         R         ID
---------- ----------
         1          1
         2          2
         3          3
         4          5
现象:是rownum是按照正序来返回的
        
该语句的执行计划如下:
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  COUNT               |             |       |       |       |
|   2 |   INDEX FULL SCAN    | PK_DAIMIN   |       |       |       |
--------------------------------------------------------------------
现象:使用了PK_DAIMIN这个主键索引,并且从执行计划中看出COUNT操作之后没有SORT ORDER BY操作
     (Rownum事实上在COUNT (STOPKEY)时产生)

SQL> select rownum as r, t.parentid from daimin t order by t.parentid;
         R   PARENTID
---------- ----------
         3          1
         4          2
         2          3
         1          6
现象:是rownum没有按照正序来返回

该语句的执行计划如下:
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT ORDER BY       |             |       |       |       |
|   2 |   COUNT              |             |       |       |       |
|   3 |    TABLE ACCESS FULL | DAIMIN      |       |       |       |
--------------------------------------------------------------------
现象:没有使用索引查询 ,并且在COUNT操作之后有SORT ORDER BY操作
分析:为什么这两句SQL语句返回的rownum不一样呢?
      主要是由于第一句使用了索引已经排好序,然后再产生Rownum,此时已经是按照parentid排好序的顺序,
      再按照parentid排序之后原来的rownum标识不会被打乱,所以返回的rownum是正序;
      而第二句则使用全表扫描,在全表扫面是查询出来的结果集是按照表中原有的记录的先后顺序来返回的,
      然后在COUNT操作时给返回的记录标记1,2,3,在标好标记之后再按照parentid字段排序,这样就将原来
      在COUNT时的顺序打乱,重新排序,所以返回的rownum不是正序。

问题3、rownnum与索引之间的关系?是不是建了某个字段的索引都会使用索引呢(在没有改字段的where条件的情况下)?
下面做测试试验:
给DAIMIN表中的PARENTID字段建索引
create index DM_PARENTID on DAIMIN (PARENTID);

select rownum as r, t.parentid from daimin t order by t.parentid;
其执行计划:
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT ORDER BY       |             |       |       |       |
|   2 |   COUNT              |             |       |       |       |
|   3 |    TABLE ACCESS FULL | DAIMIN      |       |       |       |
--------------------------------------------------------------------

查询结果:
SQL> select rownum as r, t.parentid from daimin t order by t.parentid;
         R   PARENTID
---------- ----------
         3          1
         4          2
         2          3
         1          6

分析:给parentid字段建了索引,但是该语句并没有使用索引,仍然采用的是全表扫描,所以返回的rownum仍然不是正序。

如果修改DAIMIN表中的PARENTID字段为not null
alter table DAIMIN modify PARENTID not null;

再次查询该语句的其执行计划:
---------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |       |       |       |
|   1 |  COUNT               |              |       |       |       |
|   2 |   INDEX FULL SCAN    | DM_PARENTID  |       |       |       |
---------------------------------------------------------------------

查询结果:
SQL> select rownum as r, t.parentid from daimin t order by t.parentid;
         R   PARENTID
---------- ----------
         1          1
         2          2
         3          3
         4          6

分析:将parentid字段设置为not null字段之后,由于在order by parentid时会采用parentid字段的索引DM_PARENTID,
      所以改语句查询出来的rownum的顺序是正序。

问题:为什么给parentid字段设置为not null字段之后,此时该查询语句会使用索引呢(在没有该字段的where条件时)?
      这里主要是牵涉到order by使用索引的条件,什么情况下order by会使用索引?
order by 使用索引是有条件的:
1)ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
2)ORDER BY中所有的列必须定义为非空.
3)另外,如果ORDER BY中的列在where 条件中出现,也会使用索引

上面的试验是满足了ORDER BY中列parentid为非空,所以order by parentid使用parentid字段的索引。

对于复合索引:
create index DM_INDEX on DAIMIN (ID, PARENTID);

以下几句都使用了索引:
Select rownum, t.* from DAIMIN t order by ID, PARENTID;  --使用复合索引DM_INDEX
select rownum, t.* from DAIMIN t order by  t.ID desc, t.PARENTID desc;    --使用复合索引DM_INDEX
Select rownum, t.* from DAIMIN t order by ID;           --使用主键索引
Select rownum, t.* from DAIMIN t order by ID desc;      --使用主键索引

以下几句不使用索引:
select rownum, t.* from DAIMIN t order by  t.ID asc, t.PARENTID desc;   --不使用复合索引DM_INDEX,全表扫描

Select rownum, t.* from DAIMIN t order by PARENTID;
(该句parentid字段还没有设置为not null时,只有复合索引DM_INDEX,此时不使用复合索引)

总结:
1)默认情况是按顺序先取rownum,再order by
2)如果order by 满足使用索引的情况,则先order by,再取rownum

order by 使用索引是有条件的:
1)ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
2)ORDER BY中所有的列必须定义为非空.
3)另外,如果ORDER BY中的列在where 条件中出现,也会使用索引

        

你的问题1中的试验执行计划,可以看出,因为使用了索引,所以排序的操作省略了。
rownum和读数据的顺序有关,当全表扫描时,按照物理顺序标记,标记好后,放入排序区中排序,所以rownum的顺序可能会乱,因为它在排序前就标记好了,用索引时,按索引的顺序读出,并标记,标记好后,因为索引的顺序和排序的顺序相同,所以看出rownum没变。
只要记住一点,rownum是读出一条标记一条的,select * from rownum>n(n<>0)搜不出数据也是这个道理。
恩,是的
补充:
补充:
select rownum as r, t.parentid from daimin t
where rownum>=0
或者
select rownum as r, t.parentid from daimin t
where rownum>=1
可以查询出所有数据,但是如果
select rownum as r, t.parentid from daimin t
where rownum>=n(n>=2)
查询不出数据
  评论这张
 
阅读(623)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018