2008-7-5 21:20
gthboy
rownum受不受order by影响的观察
很多书上都写：rownum伪列不受order by子句影响，今天试了一下，发现不是那么简单的，呵呵。
[quote]SQL> create table t6 as select * from t5;
表已创建。
SQL> select constraint_name,constraint_type from user_constraints where table_na
me='T6';
未选定行[/quote]
表t6现在是一个没有约束的表
[quote]SQL> select rownum,rowid,t6.* from t6;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
5 AAAHROAAKAAAALkAAE 4 3
4 AAAHROAAKAAAALkAAD 5 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
7 AAAHROAAKAAAALkAAG 8 6
6 AAAHROAAKAAAALkAAF 6 7
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
3 AAAHROAAKAAAALkAAC 3 2
2 AAAHROAAKAAAALkAAB 2 2
1 AAAHROAAKAAAALkAAA 1 2
已选择7行。[/quote]
由上面可以看到，在普通字段上排序后，rownum的值与记录的rowid密切相关，它不受order by子句的影响。下面我们设置个主键再来看看
[quote]SQL> alter table t6 add constraint con_p_t6 primary key (a);
表已更改。
SQL> select rownum,rowid,t6.* from t6;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAE 4 3
5 AAAHROAAKAAAALkAAD 5 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAG 8 6
2 AAAHROAAKAAAALkAAF 6 7
3 AAAHROAAKAAAALkAAD 5 3
4 AAAHROAAKAAAALkAAE 4 3
5 AAAHROAAKAAAALkAAC 3 2
6 AAAHROAAKAAAALkAAB 2 2
7 AAAHROAAKAAAALkAAA 1 2
已选择7行。[/quote]
嘿嘿，与rowid紧密相关的rownum变成与order by相关了！再来看看普通字段
[quote]SQL> select rownum,rowid,t6.* from t6 order by b;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
7 AAAHROAAKAAAALkAAG 8 6
6 AAAHROAAKAAAALkAAF 6 7
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by b desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
已选择7行。[/quote]
看来，在普通字段上排序，rownum的值还是与记录的rowid紧密相关的。
结论：rownum伪列受不受order by排序的影响，要看我们order by什么字段。如果我们order by非主键字段，那么rounum是不受order by影响的；如果我们order by主键字段，那么rownum的值将受order by的影响！
很多书上都写：rownum伪列不受order by子句影响，今天试了一下，发现不是那么简单的，呵呵。
[quote]SQL> create table t6 as select * from t5;
表已创建。
SQL> select constraint_name,constraint_type from user_constraints where table_na
me='T6';
未选定行[/quote]
表t6现在是一个没有约束的表
[quote]SQL> select rownum,rowid,t6.* from t6;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
5 AAAHROAAKAAAALkAAE 4 3
4 AAAHROAAKAAAALkAAD 5 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
7 AAAHROAAKAAAALkAAG 8 6
6 AAAHROAAKAAAALkAAF 6 7
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
3 AAAHROAAKAAAALkAAC 3 2
2 AAAHROAAKAAAALkAAB 2 2
1 AAAHROAAKAAAALkAAA 1 2
已选择7行。[/quote]
由上面可以看到，在普通字段上排序后，rownum的值与记录的rowid密切相关，它不受order by子句的影响。下面我们设置个主键再来看看
[quote]SQL> alter table t6 add constraint con_p_t6 primary key (a);
表已更改。
SQL> select rownum,rowid,t6.* from t6;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAE 4 3
5 AAAHROAAKAAAALkAAD 5 3
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by a desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAG 8 6
2 AAAHROAAKAAAALkAAF 6 7
3 AAAHROAAKAAAALkAAD 5 3
4 AAAHROAAKAAAALkAAE 4 3
5 AAAHROAAKAAAALkAAC 3 2
6 AAAHROAAKAAAALkAAB 2 2
7 AAAHROAAKAAAALkAAA 1 2
已选择7行。[/quote]
嘿嘿，与rowid紧密相关的rownum变成与order by相关了！再来看看普通字段
[quote]SQL> select rownum,rowid,t6.* from t6 order by b;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
7 AAAHROAAKAAAALkAAG 8 6
6 AAAHROAAKAAAALkAAF 6 7
已选择7行。
SQL> select rownum,rowid,t6.* from t6 order by b desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
6 AAAHROAAKAAAALkAAF 6 7
7 AAAHROAAKAAAALkAAG 8 6
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
1 AAAHROAAKAAAALkAAA 1 2
2 AAAHROAAKAAAALkAAB 2 2
3 AAAHROAAKAAAALkAAC 3 2
已选择7行。[/quote]
看来，在普通字段上排序，rownum的值还是与记录的rowid紧密相关的。
结论：rownum伪列受不受order by排序的影响，要看我们order by什么字段。如果我们order by非主键字段，那么rounum是不受order by影响的；如果我们order by主键字段，那么rownum的值将受order by的影响！
2008-7-5 21:31
sunfly1983
看来以后用到rownum和order by的时候要小心了！
谢谢提醒啊！
谢谢提醒啊！
2008-7-5 23:10
caizhuoyi
Oracle中的rownum是在取数据的时候产生的序号，对指定排序的数据去指定的rowmun行数据并不一定出现预期的结果。
SQL> select rownum,rowid,t6.* from t6 order by a desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
7 AAAHROAAKAAAALkAAG 8 6
6 AAAHROAAKAAAALkAAF 6 7
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
3 AAAHROAAKAAAALkAAC 3 2
2 AAAHROAAKAAAALkAAB 2 2
1 AAAHROAAKAAAALkAAA 1 2
可以这样解决：
select rownum,t.* from (select rowid,t6.* from t6 order by a desc) t;
这样才能产生正确的结果。
SQL> select rownum,rowid,t6.* from t6 order by a desc;
ROWNUM ROWID A B C
---------- ------------------ ---------- ---------- ----------
7 AAAHROAAKAAAALkAAG 8 6
6 AAAHROAAKAAAALkAAF 6 7
4 AAAHROAAKAAAALkAAD 5 3
5 AAAHROAAKAAAALkAAE 4 3
3 AAAHROAAKAAAALkAAC 3 2
2 AAAHROAAKAAAALkAAB 2 2
1 AAAHROAAKAAAALkAAA 1 2
可以这样解决：
select rownum,t.* from (select rowid,t6.* from t6 order by a desc) t;
这样才能产生正确的结果。
2008-7-6 00:13
8193102
这个要看你的Oracle版本了,对于ORACLE9i中,官方网明确说明排序时受列的索引及非空有关系,并不是简单地说没有关系,对于Oracle9可以看看笔者的文章:
[url]http://blog.sina.com.cn/s/blog_511846a201008sbu.html[/url]
[url]http://blog.sina.com.cn/s/blog_511846a201008sbu.html[/url]
2008-7-6 07:24
jvkojvko
不明白
2008-7-6 09:40
gthboy
[quote]原帖由 [i]8193102[/i] 于 2008-7-6 00:13 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10868514&ptid=1017338][img]http://www.itpub.net/images/common/back.gif[/img][/url]
这个要看你的Oracle版本了,对于ORACLE9i中,官方网明确说明排序时受列的索引及非空有关系,并不是简单地说没有关系,对于Oracle9可以看看笔者的文章:
[url=http://blog.sina.com.cn/s/blog_511846a201008sbu.html]http://blog.sina.com.cn/s/blog_511846a201008sbu.html[/url] [/quote]
知道了，只要order by的字段有not null约束，同时有索引，那么rownum的顺序就是依次递增。
这个要看你的Oracle版本了,对于ORACLE9i中,官方网明确说明排序时受列的索引及非空有关系,并不是简单地说没有关系,对于Oracle9可以看看笔者的文章:
[url=http://blog.sina.com.cn/s/blog_511846a201008sbu.html]http://blog.sina.com.cn/s/blog_511846a201008sbu.html[/url] [/quote]
知道了，只要order by的字段有not null约束，同时有索引，那么rownum的顺序就是依次递增。
2008-7-6 10:42
8193102
但是要注意,在10g下的机制还是有变化的,因为它们的优化原则不同.
2008-7-6 12:15
gthboy
[quote]原帖由 [i]8193102[/i] 于 2008-7-6 10:42 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10869628&ptid=1017338][img]http://www.itpub.net/images/common/back.gif[/img][/url]
但是要注意,在10g下的机制还是有变化的,因为它们的优化原则不同. [/quote]
10g下有变化？怎么变化？
如果一个应用系统是基于9i开发的，使 用了rownum，那把数据迁移到10g上，程序执行结果会不会改变甚至出错啊？ :surprise:
但是要注意,在10g下的机制还是有变化的,因为它们的优化原则不同. [/quote]
10g下有变化？怎么变化？
如果一个应用系统是基于9i开发的，使 用了rownum，那把数据迁移到10g上，程序执行结果会不会改变甚至出错啊？ :surprise:
2008-7-7 10:40
gthboy
[quote]原帖由 [i]gthboy[/i] 于 2008-7-6 12:15 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10870029&ptid=1017338][img]http://www.itpub.net/images/common/back.gif[/img][/url]
10g下有变化？怎么变化？
如果一个应用系统是基于9i开发的，使 用了rownum，那把数据迁移到10g上，程序执行结果会不会改变甚至出错啊？ :surprise: [/quote]
这个问题，楼上老大再来讲讲呗
10g下有变化？怎么变化？
如果一个应用系统是基于9i开发的，使 用了rownum，那把数据迁移到10g上，程序执行结果会不会改变甚至出错啊？ :surprise: [/quote]
这个问题，楼上老大再来讲讲呗
2008-7-7 10:43
wuhuaT
[quote]原帖由 [i]8193102[/i] 于 2008-7-6 10:42 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10869628&ptid=1017338][img]http://www.itpub.net/images/common/back.gif[/img][/url]
但是要注意,在10g下的机制还是有变化的,因为它们的优化原则不同. [/quote]
是呀,升级了怎么整?愿闻其详.
但是要注意,在10g下的机制还是有变化的,因为它们的优化原则不同. [/quote]
是呀,升级了怎么整?愿闻其详.
2008-7-7 11:52
shiguibao
[quote]原帖由 [i]gthboy[/i] 于 2008-7-6 12:15 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10870029&ptid=1017338][img]http://www.itpub.net/images/common/back.gif[/img][/url]
10g下有变化？怎么变化？
如果一个应用系统是基于9i开发的，使 用了rownum，那把数据迁移到10g上，程序执行结果会不会改变甚至出错啊？ :surprise: [/quote]
不仅是rownum，如果是只group by 没有order by 都可能有问题
10g下有变化？怎么变化？
如果一个应用系统是基于9i开发的，使 用了rownum，那把数据迁移到10g上，程序执行结果会不会改变甚至出错啊？ :surprise: [/quote]
不仅是rownum，如果是只group by 没有order by 都可能有问题
2008-7-7 21:46
8193102
不过可以略总结一下,
select rownum, t.* from t.table where rownum=1 order by t.id;
select rownum, t.* from t.table where rownum=1 order by t.id desc;
t.id是主键(通过非空),不过是9i还是10g的结果都是一样的,
某些情况下,实在不行,可以加上强制索引,由于9i和10g的优化器不同,所以不建议这样使用.
不知道10g是否还可以改回用9i的优化方法,
select rownum, t.* from t.table where rownum<100 order by t.id;
此时就要看table的记录数了,在10g下这个100不是个确定数,少于一定比例可以rownum是从1开始的,有时候就是1条记录之差,可能就乱序了,
select rownum, t.* from t.table where rownum=1 order by t.id;
select rownum, t.* from t.table where rownum=1 order by t.id desc;
t.id是主键(通过非空),不过是9i还是10g的结果都是一样的,
某些情况下,实在不行,可以加上强制索引,由于9i和10g的优化器不同,所以不建议这样使用.
不知道10g是否还可以改回用9i的优化方法,
select rownum, t.* from t.table where rownum<100 order by t.id;
此时就要看table的记录数了,在10g下这个100不是个确定数,少于一定比例可以rownum是从1开始的,有时候就是1条记录之差,可能就乱序了,
2008-7-8 02:38
newkid
不要依赖任何自然顺序，因为那是很靠不住的。
像这种写法不可取：
select rownum, t.* from t where rownum<100 order by t.id;
必须改成这样：
select * from (select * from t order by t.id) where rownum<100;
像这种写法不可取：
select rownum, t.* from t where rownum<100 order by t.id;
必须改成这样：
select * from (select * from t order by t.id) where rownum<100;
2008-7-8 08:58
8193102
楼上的说得对,但是要知道,如果在9i下,这样写是没有问题的(9i在Oracle的官方网上我找到了相应的说明,只取前100条时,性能没有比它更好的),
如果在10g下是不能这么写的,它依赖于系统的优化策略
如果在10g下是不能这么写的,它依赖于系统的优化策略
2008-7-8 09:37
nyfor
rownum 就是按照取得数据的顺序来进行编号的.
那么数据的提取顺序就成为了一个关键的问题. 当Oracle执行计划不走索引的时候, 数据的提取顺序就是按照 ROWID 大小物理顺序提取的, 如果 Oracle执行计划中有走索引的时候, 那么数据的提取是先读取索引, 因此其顺序就是按照索引中的顺序来的.
俺认为 Rownum 与 Order By 没有任何关系.
那么数据的提取顺序就成为了一个关键的问题. 当Oracle执行计划不走索引的时候, 数据的提取顺序就是按照 ROWID 大小物理顺序提取的, 如果 Oracle执行计划中有走索引的时候, 那么数据的提取是先读取索引, 因此其顺序就是按照索引中的顺序来的.
俺认为 Rownum 与 Order By 没有任何关系.
2008-7-8 09:59
feng2371
没有任何关系？没太明白
2008-7-8 10:04
nyfor
rownum 只与和数据读取顺序有关的执行计划有关, 而与 Order by 无关,
当然 Order by 可能会影响 Oracle 的执行计划, 但这不是说就和 Rownum 有关系.
当然 Order by 可能会影响 Oracle 的执行计划, 但这不是说就和 Rownum 有关系.
2008-7-8 10:27
wilson2006
总之，rownum不是很保险，使用的时候要确保不出错，就要使用子查询。
2008-7-8 22:18
newkid
[quote]原帖由 [i]nyfor[/i] 于 2008-7-8 10:04 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10885431&ptid=1017338][img]http://www.itpub.net/images/common/back.gif[/img][/url]
rownum 只与和数据读取顺序有关的执行计划有关, 而与 Order by 无关,
当然 Order by 可能会影响 Oracle 的执行计划, 但这不是说就和 Rownum 有关系. [/quote]
rownum如果和order by在同一层，那就不能理解为排序后的序号(因此是无关的)
如果像我这样写：
select rownum, t2.* from (select * from t order by t.id) t2 where rownum<100;
rownum就是排序后的序号。
rownum 只与和数据读取顺序有关的执行计划有关, 而与 Order by 无关,
当然 Order by 可能会影响 Oracle 的执行计划, 但这不是说就和 Rownum 有关系. [/quote]
rownum如果和order by在同一层，那就不能理解为排序后的序号(因此是无关的)
如果像我这样写：
select rownum, t2.* from (select * from t order by t.id) t2 where rownum<100;
rownum就是排序后的序号。
2008-7-8 22:20
newkid
[quote]原帖由 [i]8193102[/i] 于 2008-7-8 08:58 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10884544&ptid=1017338][img]http://www.itpub.net/images/common/back.gif[/img][/url]
楼上的说得对,但是要知道,如果在9i下,这样写是没有问题的(9i在Oracle的官方网上我找到了相应的说明,只取前100条时,性能没有比它更好的),
如果在10g下是不能这么写的,它依赖于系统的优化策略 [/quote]
select rownum, t2.* from (select * from t order by t.id) t2 where rownum<100;
这是典型的 TOP N 查询，在10g下不能这么写？是数据不对？还是效率太低？那你说该怎么写？
楼上的说得对,但是要知道,如果在9i下,这样写是没有问题的(9i在Oracle的官方网上我找到了相应的说明,只取前100条时,性能没有比它更好的),
如果在10g下是不能这么写的,它依赖于系统的优化策略 [/quote]
select rownum, t2.* from (select * from t order by t.id) t2 where rownum<100;
这是典型的 TOP N 查询，在10g下不能这么写？是数据不对？还是效率太低？那你说该怎么写？
页: [1] 2
查看完整版本: rownum受不受order by影响的观察
Powered by ITPUB论坛