In Oracle 11g, how to change the order of the results of a sql without “order by”?(转)
oracle 11g
当sql语句中不加order by的时候,好像是按rowid的顺序返回结果的。
我也看过一些相关的文档,oracle的官方意思就是不加order by,就不保证输出的顺序。
那么,问题来了:
如果现在我select XXX,一组结果出来,顺序是.......A....B.....
那么接下来我做什么操作,再做同样一句select,怎么才能让结果变成....B...A.....
Notice1: 操作中请勿修改表的主键,不要用DDL,只用select * from A;得到结果
Notice2:
create table A(id NUMBER(20) primary key, name VARCHAR2(30));
insert into A values(1,'one');
insert into A values(2,'two');select * from A;
这个问题我试验了一些操作,比如update,insert,delete一两条数据啊,都不能做到改变两个结果的相对顺序。
Given this requirement:
Do not change the SQL
select * from table A
.
Change the table A to change the results order
the answer is a clear and definite: this is not possible.
(Keeping my initial answer as a reference)
"I know the results will be sorted by rowid
" - no, they are not.
The rows will be returned in any order the database thinks is the most efficient.
There is no, absolutely no, guarantee that rows are returned in any specific order unless you specify an ORDER BY
.
The ONLY way to get a specific order is to use an ORDER BY
.
http://stackoverflow.com/questions/20186673/in-oracle-11g-how-to-change-the-order-of-the-results-of-a-sql-without-order-by
rowid是标识行的唯一性,格式:data object number(6个字符)+relative file number(3个字符)+block number(6个字符)+row number(3个字符)
数据库的理论基础就是集合论,本质上就是无序的,不使用order by(子查询或查询)是不能保证顺序,你把数据不停无序删除再加回估计就能看到这个现象,但大部分时候都是在磁盘上的次序,这跟数据库怎么读数据有关
http://zhidao.baidu.com/link?url=F6VOk6Khxty3AmU9CgVRrdaBf7UBSsQU6wKLnmqE2XxQ75P_4nTtqYMARQAGOJk6q-cTYxXK1jiiaYQT1DHaGvEBdYo9mj571VrusfBd2JO