Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出

ORDER BY非稳定的排序
提一个问题: oracle在order by 排序时,是稳定排序算法吗? 发现用一个type进行排序后,做分页查询,第一页的数据和第二页的数据有重复 怀疑是order by 时,两次排列的顺序不一致

看到业务描述的问题可以得到的结论order by排序不稳定,还有第一个印象就是,type肯定是不唯一的,并且没有索引吧。

这里先科普下排序的稳定性,举个最简单的例子,1,2,3,1,4,5 排序 排序的结果是1,1,2,3,4,5,这时候观察这个1,如果第一个1还是排序前的那个1,那么算法是稳定的。也就是说相等数在排序后不发生交换。

还记得以前数据结构中的几种排序算法:

选择排序复杂度为n*n,不稳定排序,

快速排序复杂度为n*n,不稳定排序,

希尔排序复杂度为nlogn,不稳定排序,

堆排序复杂度为nlogn,不稳定排序,

冒泡排序复杂度为n*n,稳定排序,

插入排序复杂度为n*n,稳定排序,

归并排序复杂度为nlogn,稳定排序

基数排序的复杂度和位数是有关的,是稳定排序。

好了回到正题,本机测试,插入几条测试数据,表结构就两个字段,id和name,没有索引

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST;
1  2  test
2  2  test
3  3  test
4  4  test
5  1  test

可以看到,默认差的时候是是按照rownum排序的。
然后按照name排序,

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
1  2  test
2  2  test
5  1  test
4  4  test
3  3  test

可以看到,排列的顺序不是按照rownum来排序了。
这里再插入一个知识,如何在oracle里查看执行计划,我敲了半天的explain 发现没有用。。。

原来是这么看的,而且消息要比mysql详细多了。:

select * from table(dbms_xplan.display());
-------------------------------------------------------------------------------
| Id | Operation      | Name  | Rows | Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |     |   1 |   8 |  16  (7)| 00:00:01 |
|  1 | SORT ORDER BY   |     |   1 |   8 |  16  (7)| 00:00:01 |
|  2 |  COUNT       |     |    |    |      |     |
|  3 |  TABLE ACCESS FULL| ZZ_TEST |   1 |   8 |  15  (0)| 00:00:01 |
-------------------------------------------------------------------------------

好了,那么排序和索引有没有关系呢?
我们先在type上面加一个索引试试,这里我清空了重新插入了5个数据

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
1  3  test
2  4  test
5  2  test
4  1  test
3  5  test

貌似不给力啊老湿。
好,删掉type的索引,在id上加索引,清空表再插入5个数据

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
1  3  test
2  4  test
5  2  test
4  1  test
3  5  test

好吧。原来带上索引都不给力啊。。。

但是不对啊。。。总感觉不对劲啊。没错。。。我TMD一直再用的rownum而不是rowID啊。我一定是最近写分页写多了,坑爹啊。

这里简单的分辨一下rownum和rowid的区别,rownum是返回结果集的一个伪数列,用来标记返回结果的顺序,而rowid是一个物理值用来标记存储位置的。这个值是唯一而固定的

rowid和rownum都是虚列,但含义完全不同。rowid是物理地址,用于定位oracle中具体数据的物理存储位置,而rownum则是sql的输出结果排序。通俗的讲:rowid是相对不变的,rownum会变化,尤其是使用order by的时候。
那么我们再查下rowid试试,这时候表没有索引

SELECT rowid as rono,ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"
AAA7JjAB9AAAD+RAAA 1  3  test
AAA7JjAB9AAAD+RAAB 2  4  test
AAA7JjAB9AAAD+RAAG 5  2  test
AAA7JjAB9AAAD+RAAD 4  1  test
AAA7JjAB9AAAD+RAAC 3  5  test

感觉rowno和rowid一个样子啊

清空表,再在name上建立一个索引,然后在插入5条数据

AAA7JjAB9AAAD+RAAA 1  3  test
AAA7JjAB9AAAD+RAAB 2  4  test
AAA7JjAB9AAAD+RAAG 5  2  test
AAA7JjAB9AAAD+RAAD 4  1  test
AAA7JjAB9AAAD+RAAC 3  5  test

所以,也不是rowid的问题,oralce的排序就是不稳定的。
这里有个小技巧,因为rownum的输出顺序并不是排序的结果 那么如何能输出排序顺序的rownum呢?可以使用嵌套查询,这个和分页写法是一个道理的

select ROWNUM ,t.* from (SELECT rowid rono,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name") t

这里再插入一个小知识,如何在oracle下看表的

select  *  from  user_tables

可以查询出所有的用户表

select  table_name  from  user_tables;

查询结果按照in条件顺序输出序输出

业务需要,通过lucene查出符合搜索条件的id,然后在详情表里查出这些id的详情

SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64);

其中id是根据搜索的权值进行的排序,sql没有问题,但是通过这种sql查出来的结果的排序就不对了。

61 测试问题101 测试问题101
62 测试问题102 测试问题102
63 测试问题103 测试问题103
64 测试问题104 测试问题104
65 测试问题106 测试问题106

这个一般默认是按照主键来排序的,而并不是根据in中条件的顺序来排列的

网上有个案例是按照in顺序来排序的解决方案,是利用sql server的charindex来解决的。不过仅限于sqlserver

select id,title from info
where id in ('3,1,2,5,4')
order by charindex(','+convert(varchar,ID)+',',',3,1,2,5,4,')

CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX函数调用方法如下:

CHARINDEX ( expression1 , expression2 [ , start_location ] ) 

Expression1是要到expression2中寻找的字符中,start_location是CHARINDEX函数开始在expression2中找expression1的位置。 CHARINDEX函数返回一个整数,返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串,那么函数整数“0”
这里有小技巧,可以利用charindex来进行模糊匹配

select name,pass from dps_user where
charindex('张三',dps_user.name)> 0

但是oracle下是怎么实现相同的效果的呢?可以使用decode函数

SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64) ORDER BY "DECODE"(id, 63,1,62,2,65,3,61,64);
63 测试问题103 测试问题103
62 测试问题102 测试问题102
65 测试问题106 测试问题106
61 测试问题101 测试问题101
64 测试问题104 测试问题104

结果是符合条件的

(0)

相关推荐

  • Oracle数据库中的级联查询、级联删除、级联更新操作教程

    级联查询 在ORACLE 数据库中有一种方法可以实现级联查询 select * //要查询的字段 from table //具有子接点ID与父接点ID的表 start with selfid=id //给定一个startid(字段名为子接点ID,及开始的ID号) connect by prior selfid=parentid //联接条件为子接点等于父接点,不能反 这个SQL主要用于菜单的级联查询,给一个父接点可以查出所有的子接点.及子接点的子接点,一查到底,很实用.不过呢这个程序只能在ora

  • mysql、mssql及oracle分页查询方法详解

    本文实例讲述了mysql.mssql及oracle分页查询方法.分享给大家供大家参考.具体分析如下: 分页查询在web开发中是最常见的一种技术,最近在通过查资料,有一点自己的心得 一.mysql中的分页查询 注: m=(pageNum-1)*pageSize;n= pageSize; pageNum是要查询的页码,pageSize是每次查询的数据量, 方法一: select * from table order by id limit m, n; 该语句的意思为,查询m+n条记录,去掉前m条,返

  • Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍

    先来定义分页语句将要用到的几个参数: int currentPage ; //当前页 int pageRecord ; //每页显示记录数 以之前的ADDRESSBOOK数据表为例(每页显示10条记录): 一.SqlServe下载 分页语句 String sql = "select top "+pageRecord +" * from addressbook where id not in (select top "+(currentPage-)*pageRecor

  • Oracle 数据库特殊查询总结

    1. 查询本节点及本节点以下的所有节点: select * from table1 c start with c.p_id='0000000' connect by prior c.id=c.p_id and c.use_yn='Y' order by id ; 2. 查询节点中所有的层级关系 SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT"

  • ORACLE错误一览表 方便查询

    ORACLE错误一览表,方便大家查询! ORA-00001: 违反唯一约束条件 (.) ORA-00017: 请求会话以设置跟踪事件 ORA-00018: 超出最大会话数 ORA-00019: 超出最大会话许可数 ORA-00020: 超出最大进程数 () ORA-00021: 会话附属于其它某些进程:无法转换会话 ORA-00022: 无效的会话 ID:访问被拒绝 ORA-00023: 会话引用进程私用内存:无法分离会话 ORA-00024: 单一进程模式下不允许从多个进程注册 ORA-000

  • Oracle SQL树形结构查询

    oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: 复制代码 代码如下: select * from tablename start with cond1 connect by cond2 where cond3; 简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段: id,parentid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构. 用上

  • Oracle中scott表结构与简单查询实例分析

    本文实例讲述了Oracle中scott表结构与简单查询的方法.分享给大家供大家参考.具体分析如下: 1.scott用户的表的结构 查看表结构 desc 表名;//desc emp; emp表: SQL> desc emp; 名称 是否为空? 类型 ----------------- -------- ------------ EMPNO NOT NULL NUMBER(4) 雇员编号 ENAME VARCHAR2(10) 雇员姓名 JOB VARCHAR2(9) 雇员职位 MGR NUMBER(

  • oracle跨库查询dblink的用法实例详解

    本文实例讲述了oracle跨库查询dblink的用法.分享给大家供大家参考,具体如下: 1.创建之前的工作 在创建dblink之前,首先要查看用户是否有相应的权限.针对特定的用户,使用 sqlplus user/pwd登录后,执行如下语句: 复制代码 代码如下: select * from user_sys_privs t where t.privilege like upper('%link%'); 在sys用户下,显示结果为: SYS CREATE DATABASE LINK NO SYS

  • 一些Oracle数据库中的查询优化建议综合

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引.   2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引

  • 讲解Oracle数据库中的数据字典及相关SQL查询用法

    Oracle数据字典概述    数据库是数据的集合,数据库维护和管理这用户的数据,那么这些用户数据表都存在哪里,用户的信息是怎样的,存储这些用户的数据的路径在哪里,这些信息不属于用户的信息,却是数据库维护和管理用户数据的核心,这些信息就是数据库的数据字典来维护的,数据库的数据字典就汇集了这些数据库运行所需要的基础信息叻.每个数据库都提供了各自的数据字典的方案,虽然形式不同,但是目的和作用是一样的,比如在mysql里数据字典是在information_schema 里表现的,sqlserver则是

  • ORACLE 查询被锁住的对象,并结束其会话的方法

    使用Oracle时,发现有表被锁,又不知道是谁(或者哪个程序)锁的,怎么办 ? 两步走: 1.查找出被锁对象的会话ID和序列号 执行如下SQL: -- 查询出被锁对象,并提供 kill 脚本 SELECT S.SID, S.MACHINE, O.OBJECT_NAME, L.ORACLE_USERNAME, L.LOCKED_MODE, S.OSUSESR, 'ALTER SYSTEM KILL SESSION '''|| S.SID || ', '|| S.SERIAL#||''';' AS

随机推荐