oracle select order by的记录顺序

oracle select order by 的记录顺序

drop table test_olap;
-- Create table
create table TEST_OLAP
(
  CLASS NUMBER(1),
  NAME  VARCHAR2(100),
  SCORE NUMBER(4,1) default 0
);

delete from test_olap;
insert into test_olap (class, name,score)values(2, 'b2', 34);
insert into test_olap (class, name,score)values(3, 'b3', 34);
commit;
select t.class, t.name, t.score
  from test_olap t
where t.class > 1
order by t.score;
查询结果:
class  name    score
  3 b3 34.0 --后插入的结果排在前面
  2 b2 34.0

接下来:
delete from test_olap;
insert into test_olap (class, name, score) values (3, 'b3', 34);
insert into test_olap (class, name, score) values (2, 'b2', 34);
commit;
select t.class, t.name, t.score
  from test_olap t
where t.class > 1
order by t.score;
查询结果:
class  name    score
  2 b2 34.0 --后插入的结果排在前面
  3 b3 34.0

为什么在排序的时候,后插入数据库的记录会排在前面呢?

ORACLE默认是不保证任何排序的
显示出来结果的顺序就是读取数据的顺序

虽然你这里加了ORDER BY
可是由于两条记录的SCORE的值是相同的
所以ORDER BY并起不了什么作用 

drop table test_olap;
-- Create table
create table TEST_OLAP
(
  CLASS NUMBER(1),
  NAME  VARCHAR2(100),
  SCORE NUMBER(4,1) default 0
);

delete from test_olap;
insert into test_olap (class, name,score)values(1, 'b1', 34);
insert into test_olap (class, name,score)values(2, 'b2', 34);
insert into test_olap (class, name,score)values(3, 'b3', 34);
insert into test_olap (class, name,score)values(4, 'b4', 34);
insert into test_olap (class, name,score)values(5, 'b5', 34);
commit;

select t.class, t.name, t.score
  from test_olap t
where t.class > 0
order by t.score;

查询结果：
class  name  score
1      b1    34.0
2      b2    34.0
5      b5    34.0
4      b4    34.0
3 b3    34.0

多几条测试数据测试，果然是乱序的。