关于oracle分页查询优化问题

Question

StringBuffer query = new StringBuffer();
query.append("select * from(");
query.append(
" select c.APP_OID,c.APP_USER_OID,c.APP_DEPT_NO,c.APP_USER_ID,c.APP_USER_CN,c.APP_DEPT,c.APP_DATE,c.APP_STATUS,c.APP_RECEIPT_CTX,c.PLAN_YEAR,row_number() ");
query.append(" over (order by  PLAN_YEAR desc,APP_DEPT_NO ) R ");
query.append(" from ws_app_plan c  where 1=1 "+ param +")");
query.append(" where R> " + (rollPage.getCurPage() - 1) * rollPage.getPerPageSize());
query.append(" and R<= " + rollPage.getCurPage() * rollPage.getPerPageSize());阿扁如上代码；现客户那边每次查询都要大概半分钟。。请问如何优化该SQL。。

Answer 1

sql没啥好优化的吧，不行的话用三层rownum分页试试
是不是数据量太大，没有索引的问题？

Answer 2

分页慢肯定是数据量太大，使用子查询导致的，不清楚你是否用的是JDBC连接操作数据库，如果是JDBC的话，建议用rs.abosolute()的方法去定位结果集。

Answer 3

我也感觉是没有建立索引的原因 本来客户数据量就大  是直接用的JDBC连接 。谢谢楼上几位的建议。

Answer 4

你先建个索引看看，还慢的话应该考虑不能用子查询了搞了，用JDBC的s.abosolute()的方法去定位结果集试试看看行不行//返回的map中包含所需的结果集和总的记录数  
    public static Map getList(int pageSize,int currentPage){  
        String sql="select * from person";  
        Connection conn=null;  
        PreparedStatement ps=null;  
        ResultSet rs=null;  
        Map result=null;  
        List list=null;  
        Person p=null;  
        try{  
            conn=DBAccess.getConnection();  
            result=new HashMap();  
            list=new ArrayList();  
            ps=conn.prepareStatement(sql);  
            rs=ps.executeQuery();  
            if((currentPage-1)*pageSize!=0)  
                rs.absolute((currentPage-1)*pageSize);  
            int i=0;  
            while(rs.next()&&i++<pageSize){  
                p=new Person();  
                p.setId(rs.getString("id"));  
                p.setName(rs.getString("name"));  
                p.setSex(rs.getString("sex"));  
                p.setAge(Integer.parseInt(rs.getString("age")));  
                list.add(p);  
                  
            }  
            result.put("list", list);  
            rs.close();  
            ps.close();  
            sql="select count(*) from person";  
            ps=conn.prepareStatement(sql);  
            rs=ps.executeQuery(sql);  
            if(rs.next()){  
                result.put("tatalcount", rs.getInt(1));  
            }  
            rs.close();  
            ps.close();  
            conn.close();  
        }catch(Exception e){  
            e.printStackTrace();  
        }  
        return result;  
    }  

Answer 5

建议你用OracleSQL查询分页。简单易于调用，不会数据在客户端太多。
给你个例子启发下SELECT x.*
  from (SELECT z.*, rownum numbers
          from pro_dowload_doc_log z
         where rownum <= 10000) x
 where x.numbers > 0;/*第二页的记录*/SELECT x.*
  from (SELECT z.*, rownum numbers
          from arc_pro_dowload_doc_log z
         where rownum <= 2 * 10000) x
 where x.numbers > 1 * 10000;

Answer 6

我用上面的方法，使用JDBC的方法获取10万条的数据没有问题。

Answer 7

楼上的办法不错 但是若是需要在页面中将每一页都显示出来 不可能挨个这样写SQL吧？ 在显示的JSP页面中按下 下一页的按钮 就会转到下一页 这样有什么好的办法么？

Answer 8

数据量大了还是会有问题的
oracle 的demo里有个sales表，一百八十多万的销售数据吧
建立了索引还分了区
差最后几页还是很慢
毕竟
rownum是根据结果集的数量计算的，也就是说查最有一页数据会把这个表的所有数据扫描一遍

Answer 9

rownum的分页算法不适合大数据量。我们早就做个测试。当数据量较大时，查询最后几页的时间每页都等同于查询所有数据的时间。

Answer 10

你直接将页码的参数传入SQL语句中执行查询返回ResultSet就行了。

Answer 11

SELECT x.*
  from (SELECT z.*, rownum numbers
          from pro_dowload_doc_log z
         where rownum <= i*onePageNum) x
 where x.numbers > (i-1) * onePageNum;
i：是传入的页码数值。
onePageNum：是设置一页显示的记录数量。

Answer 12

数据查询性能不一定跟你sql代码有关，你现在的rownum伪列的分页使用没有什么问题让Oracle的DBA协助你从数据库层面查找性能低下的原因，很有可能是数据库统计信息没更新，没有索引，或者join方式等等

Answer 13

   oracle是所有数据库分页中最麻烦的，但也是效率最好的！