精华内容
下载资源
问答
  • ORACLE分页查询SQL语句(最有效的分页)

    万次阅读 多人点赞 2018-11-01 16:18:24
    ** 一、效率高的写法 ** 1.无ORDER BY排序的写法。...即使查询的数据量再大,也几乎不受影响,速度依然!) SELECT * FROM (SELECT ROWNUM AS rowno, t.* FROM emp t WHERE hire_date BETWEEN TO_DATE ...

    一、效率高的写法

    1.无ORDER BY排序的写法。(效率最高)
    (经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!)

    SELECT *
    
      FROM (SELECT ROWNUM AS rowno, t.*
    
              FROM emp t
    
             WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
    
                                 AND TO_DATE ('20060731', 'yyyymmdd')
    
               AND ROWNUM <= 20) table_alias
    
     WHERE table_alias.rowno >= 10;
    

    2.有ORDER BY排序的写法。(效率较高)
    (经过测试,此方法随着查询范围的扩大,速度也会越来越慢哦!)

    SELECT *
    
      FROM (SELECT tt.*, ROWNUM AS rowno
    
              FROM (  SELECT t.*
    
                        FROM emp t
    
                       WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
    
                                           AND TO_DATE ('20060731', 'yyyymmdd')
    
                    ORDER BY create_time DESC, emp_no) tt
    
             WHERE ROWNUM <= 20) table_alias
    
     WHERE table_alias.rowno >= 10;
    

    二、效率垃圾但又似乎很常用的分页写法

    3.无ORDER BY排序的写法。(建议使用方法1代替)
    (此方法随着查询数据量的扩张,速度会越来越慢哦!)

    SELECT *
    
      FROM (SELECT ROWNUM AS rowno, t.*
    
              FROM k_task t
    
             WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
    
                                   AND TO_DATE ('20060731', 'yyyymmdd')) table_alias
    
     WHERE table_alias.rowno <= 20 AND table_alias.rowno >= 10;
    
    --TABLE_ALIAS.ROWNO  between 10 and 100;
    

    4.有ORDER BY排序的写法.(建议使用方法2代替)
    (此方法随着查询范围的扩大,速度会越来越慢哦!)

    SELECT *
    
      FROM (SELECT tt.*, ROWNUM AS rowno
    
              FROM (  SELECT *
    
                        FROM k_task t
    
                       WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
    
                                             AND TO_DATE ('20060531', 'yyyymmdd')
    
                    ORDER BY fact_up_time, flight_no) tt) table_alias
    
     WHERE table_alias.rowno BETWEEN 10 AND 20;
    

    5.另类语法。(有ORDER BY写法)
    (语法风格与传统的SQL语法不同,不方便阅读与理解,为规范与统一标准,不推荐使用。)

    WITH partdata AS
    
         (
    
            SELECT ROWNUM AS rowno, tt.*
    
              FROM (  SELECT *
    
                        FROM k_task t
    
                       WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
    
                                             AND TO_DATE ('20060531', 'yyyymmdd')
    
                    ORDER BY fact_up_time, flight_no) tt
    
             WHERE ROWNUM <= 20)
    
    SELECT *
    
      FROM partdata
    
     WHERE rowno >= 10;
    
     
    
    --6另类语法 。(无ORDER BY写法)
    
    WITH partdata AS
    
         (
    
            SELECT ROWNUM AS rowno, t.*
    
              FROM k_task t
    
             WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
    
                                   AND TO_DATE ('20060531', 'yyyymmdd')
    
               AND ROWNUM <= 20)
    
    SELECT *
    
      FROM partdata
    
     WHERE rowno >= 10;
    

    三、分析

    Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

    分页查询格式:

    SELECT *
    
      FROM (SELECT a.*, ROWNUM rn
    
              FROM (SELECT *
    
                      FROM table_name) a
    
             WHERE ROWNUM <= 40)
    
     WHERE rn >= 21
    

    其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

    上面给出的这个分页查询语句,在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,将结果尽快的返回。在上面的分页查询语句中,这种考虑主要体现在WHERE ROWNUM <= 40这句上。

    选择第21到40条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值,在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句,在查询的最外层控制分页的最小值和最大值。这是,查询语句如下:

    SELECT *
    
      FROM (SELECT a.*, ROWNUM rn
    
              FROM (SELECT *
    
                      FROM table_name) a)
    
     WHERE rn BETWEEN 21 AND 40
    

    对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。

    这是由于CBO优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。对于第一个查询语句,第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。

    而第二个查询语句,由于查询条件BETWEEN 21 AND 40是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。

    上面分析的查询不仅仅是针对单表的简单查询,对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

    这里就不对包含排序的查询进行说明了,下一篇文章会通过例子来详细说明。

    下面简单讨论一下多表联合的情况。

    对于最常见的等值表连接查询,CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN(MERGE JOIN效率比HASH JOIN效率低,一般CBO不会考虑)。在这里,由于使用了分页,因此指定了一个返回的最大记录数,NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层,而HASH JOIN必须处理完所有结果集(MERGE JOIN也是)。那么在大部分的情况下,对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率(分页查询的时候绝大部分的情况是查询前几页的数据,越靠后面的页数访问几率越小)。

    因此,如果不介意在系统中使用HINT的话,可以将分页的查询语句改写为:

    SELECT *
    
      FROM (SELECT a.*, ROWNUM rn
    
              FROM (SELECT *
    
                      FROM table_name) a
    
             WHERE ROWNUM <= 40)
    
     WHERE rn >= 21
    
    展开全文
  • Oracle分页查询sql语句

    万次阅读 2016-10-25 14:43:07
    1.oracle中的分页查询sql语句基本可以按照如下的模板来改写:SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM ) WHERE RN > 20 其中的SELECT * FROM TABLE_NAME 表示没有...

    1.oracle中的分页查询sql语句基本可以按照如下的模板来改写:

    SELECT *
      FROM (SELECT A.*, ROWNUM RN
              FROM (SELECT * FROM TABLE_NAME) A
             WHERE ROWNUM <= 40)
     WHERE RN > 20
    

    其中的SELECT * FROM TABLE_NAME 表示没有进行分页时的sql查询语句,而ROWNUM 和RN 限定了查询数据的范围。

    2.使用时的代码如下所示:

    /** 查询码号相关信息的sql语句 **/
    String sql_query = "SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT c.codename, case when (c.isopen = 0) then '不可用'"
                + " when (c.isopen = 1) then ((case when (c.codestatus = 0) then '空闲'"
                + " when (c.codestatus = 1) then '预占' when (c.codestatus = 2) then '占用'"
                + " when (c.enddate > sysdate) then '保留期' end)) end as status,t.name,( select p."
                + " party_name from CRM.PARTY p"
                + " where p.party_id=c.custid) as partyname, c.deviceid,(select l.name FROM"
                + " lgc_codetoclass_tbl l)as codeFeature,(SELECT g.name FROM LGC_CODEGRADE_TBL g"
                + " WHERE g.id=c.gradeid) as gradename,(select g.value "
                + " from LGC_CODEGRADE_TBL g where g.id = c.gradeid) as codePrice, c.comments"
                + " FROM LGC_CODESEG_TBL t, LGC_CODENUMBER_TBL c WHERE t.ID = c.CODESEGID and c.codename=?"
                + " ) a WHERE ROWNUM <= ?) WHERE rn > ?";
    /** 用来查询码号查询页面数据的方法的实现 **/
    public NumberRsp queryRecords(NumberEvt evt,int pageNo,int pageSize) {
            Map<String, Object> map = jdbcTemplate.queryForMap(sql_count);
            List<NumberVo> list = jdbcTemplate.query(sql_query, new Object[] { evt.getCode(),pageNo*pageSize,(pageNo-1)*pageSize}, new RowMapper<NumberVo>() {
                public NumberVo mapRow(ResultSet rs, int rowNum) throws SQLException {
                    // TODO Auto-generated method stub
                    NumberVo numberVo = new NumberVo();
                    numberVo.setPhone(rs.getString(1));
                    numberVo.setStatus(rs.getString(2));
                    numberVo.setCodePartName(rs.getString(3));
                    numberVo.setUnit(rs.getString(4));
                    numberVo.setFacility(rs.getString(5));
                    numberVo.setCodeFeature(rs.getString(6));
                    numberVo.setPricingLevel(rs.getString(7));
                    numberVo.setCodePrice(rs.getShort(8));
                    numberVo.setRemark(rs.getString(9));
                    return numberVo;
                }
            });
            NumberRsp numberRsp = new NumberRsp();
            if (map.get("total") != null) {
                numberRsp.setTotal(Integer.parseInt(map.get("total").toString()));
            }
            if (map.get("ky") != null) {
                numberRsp.setUsableNum(Integer.parseInt(map.get("ky").toString()));
            }
            if (map.get("bky") != null) {
                numberRsp.setUnUsableNum(Integer.parseInt(map.get("bky").toString()));
            }
            if (map.get("youhao") != null) {
                numberRsp.setYouhaoNum(Integer.parseInt(map.get("youhao").toString()));
            }
            if (map.get("puhao") != null) {
                numberRsp.setPuhaoNum(Integer.parseInt(map.get("puhao").toString()));
            }
            if (list != null) {
                numberRsp.setRecords(list);
            }
            return numberRsp;
        }
    展开全文
  • oracle 分页查询sql语句

    千次阅读 2016-12-22 09:32:54
    --每页显示2条新闻信息,查看第1页新闻信息(新闻标题、作者、创建时间)的分页sql语句如下 --页码:currPageNo 1 =(currPageNo-1)*PAGESIZE+1 每页显示的记录数:pageSize 此例=2 =CURRPAGENO * PAGESIZE SELECT id...

    1.

    SELECT t.t_credit_id as creditId,
           t.login_name  as loginName,
           t.long_pwd    as loginPwd,
           t.agin_pwd    as againPwd
      FROM (SELECT tc.t_credit_id,
                   tc.login_name,
                   tc.long_pwd,
                   tc.agin_pwd,
                   ROWNUM rn
              FROM t_credit tc
             where 1=1
             and ROWNUM <=10
             and tc.login_name like '%lisi%' --如查询条件写在里面是先分页在查询,写在外面先查询在分页
             ORDER BY tc.crete_time DESC) t
     WHERE t.rn >= 1;

    2.

    SELECT t.t_credit_id            as creditId,
           t.login_name             as loginName,
           t.long_pwd               as loginPwd,
           t.agin_pwd               as againPwd,
           t.id_card                as IDCard,
           t.fixed_telephone_number as fixedTelephoneNumber,
           t.telephone_number       as telephoneNumber,
           t.e_mail                 as email,
           t.address                as address,
           t.crete_time             as createtime,
           t.status                 as status
      FROM (SELECT tc.t_credit_id,
                   tc.login_name,
                   tc.long_pwd,
                   tc.agin_pwd,
                   tc.id_card,
                   tc.fixed_telephone_number,
                   tc.telephone_number,
                   tc.e_mail,
                   tc.address,
                   tc.crete_time,
                   tc.status,
                   ROWNUM rn
                   FROM t_credit tc
               ) t
     WHERE t.rn >= 1
       AND t.rn <= 20
       and t.login_name like '%l%'
     ORDER BY t.crete_time DESC;

    3.

    SELECT * FROM news_detail;


    --每页显示2条新闻信息,查看第1页新闻信息(新闻标题、作者、创建时间)的分页sql语句如下
    --页码:currPageNo 1 =(currPageNo-1)*PAGESIZE+1 每页显示的记录数:pageSize 此例=2 =CURRPAGENO * PAGESIZE
    SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
    WHERE a.rn>=1 AND a.rn<=2

    --每页显示2条新闻信息,查看第2页新闻信息(新闻标题、作者、创建时间)的分页sql语句如下
    --页码:currPageNo 2  每页显示的记录数:pageSize 此例=2
    SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
    WHERE a.rn>=3 AND a.rn<=4

    --每页显示2条新闻信息,查看第3页新闻信息(新闻标题、作者、创建时间)的分页sql语句如下
            --页码:currPageNo 3   每页显示的记录数:pageSize 此例=2
    SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
    WHERE a.rn>=5 AND a.rn<=6

    --起始记录数和结束记录数有什么特点?和页码与每页显示的记录有什么关系?
    --页码:currPageNo   每页显示的记录数:pageSize 此例=2
    SELECT id,title,author,createdate FROM (SELECT id,title,author,createdate,ROWNUM rn FROM news_detail) a
    WHERE a.rn>=(currPageNo - 1) * pageSize + 1 AND a.rn<=currPageNo * pageSize

    展开全文
  • ORACLE分页查询SQL语句

    2010-11-10 13:54:00
    select *  from (select T1.*, rownum linenum  from (select * from cod_mpos_order) T1  where rownum ) T2  where linenum >= 90

    select *
      from (select T1.*, rownum linenum
              from (select * from cod_mpos_order) T1
             where rownum <= 100) T2
     where linenum >= 90

    展开全文
  • <select id = "listQueryDpcInfoDO" resultMap ="getListDpcInfoDO" > select * from ( SELECT A.*, ROWNUM RN # from后为具体查询表语句 FROM ( select * from DPC...
  • select dpkts ,srcaddr,dstaddr,srcport,dstport,first,last,proc,octets,time_range_...字段 = 字段值 order by id asc) row_ WHERE rownum 本次查询截止rownum )  WHERE rownum_ >= 本次查询开始rownum
  • 关于SQL Server SQL语句查询分页数据的解决方案:要求选取 tbllendlist 中 第3000页的记录,每一页100条记录。-----------------------------------方法1:----------select top 100 * from tbllendlist where ...
  • Oracle 数据库的分页查询sql语句Oracle 数据库的分页查询sql语句oracle查询sql语句oracleselect *from (select rownum r,t1.* from 表名称 t1 where rownum < m + n) t2where t2.r >= m例如从表sys_option...
  • oracle分页查询sql

    2019-01-15 23:03:22
    oracle分页查询语句sql
  • 1.MySQL分页查询SQL语句: 关键字:”limit”select * from SYS_USER order by user_id limit 0,5;"0":第一条数据的位置,MySQL是从0开始的(例如第二页的话就是"limit 5,5") ...2.Oracle分页查询SQL语句
  • MySQL分页 Oracle分页 SQL Server分页 MySQL,ORACLE,SQL SERVER 分页查询语句 一、MySQL SELECT * FROM table_name LIMIT 0,5 ; 二、ORACLE SELECT * FROM ( SELECT t.* , rownum r FROM table_name...
  • 1.Oracle分页sql语句 [code="java"]SELECT * FROM (SELECT A.*, ROWNUM RN FROM ( select sno,sname,sage,ssex from tb_student where 1=1 ) A WHERE ROWNUM ='0'[/code] 2.Sql Server ...
  • MySQL、OracleSQL Server的分页查询语句 假设当前是第PageNo页,每页有PageSize条记录,现在分别用Mysql、OracleSQL Server分页查询student表。1、Mysql的分页查询: 1 SELECT 2 * 3 FROM 4 student 5 LIMIT ...
  • Oracle和MySQL分页查询SQL语句

    千次阅读 2017-04-05 16:57:30
    查询条件:查询数据库中test表第11到第20条数据 Oracle: select * from (select test.*,rownum rownum_ from test where rownum =11 MySQL: select * from test limit 10,10 注意: limit关键字的用法: ...
  • Oracle分页SQL语句

    2010-04-15 08:39:00
    SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (此处写查询的记录) A WHERE ROWNUM ) WHERE RN >= 11为起始记录5为结束记录
  • oracle分页查询sql语句
  • Oracle与MySQL的分页查询sql语句格式

    千次阅读 2018-03-13 23:00:10
    Oracle使用rownum进行分页,rownum是从1开始的: select * from (select rownum as rn, a.* from table_name a where rownum <= pageIndex * pageSize) where rn > (pageIndex - 1) * pageSize 其中,...
  • oracle: SELECT * FROM ( SELECT TEMP.* ,ROWNUM RN FROM ( 表) TEMP WHERE ROWNUM " + (currentPage - 1) * perPageRows sqlserver: SELECT * FROM (  SELECT TEMP.*,ROW_NUMBER() OVER (ORDER BY
  • oracle,sqlserver分页查询sql语句

    千次阅读 2011-08-30 16:17:55
    Oracle:    方法一:  SELECT * FROM   (  SELECT A.*, ROWNUM RN   FROM (SELECT * FROM TABLE_NAME) A   WHERE R
  • Oracle分页SQL语句

    2016-08-10 10:23:00
    Oracle分页SQL语句基本上是通用的,通用语句如下: 分页语句: select * from (select rownum r,a.* from qu_pilot_basic_info a where rownum<=20) where r>10; 说明: 该语句是用了两层嵌套查询,内层...
  • Oracle 分页查询语句SQL

    千次阅读 2018-11-02 14:26:55
    通用模板 SELECT * FROM (SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( ...//替换这里 ) TMP_PAGE WHERE ROWNUM ) ...因此,分页语句都是读取前面几页快,(相对于前面几页)越往后越慢。
  • select * from (select rownum,name from table where rownum ) where rownum > startIndex
  • oracle通用分页sql语句

    2018-04-28 14:50:14
    oracle通用分页sql语句解决方法:String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum {2}"; 详情地址: ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 40,346
精华内容 16,138
关键字:

oracle分页查询sql语句