Oracle分页查询与优化技巧-优快云博客

本文介绍了Oracle数据库的分页查询实现，包括查询格式、性能对比以及分页查询的优化。强调了ROWNUM在查询中的作用，指出在CBO优化模式下，合理的ROWNUM使用能提高查询效率。同时讨论了Oracle实现Top n查询的方法，并提醒了ROWNUM与排序的注意事项。

分页前端显示：

<% 
	int i = 1; 
	int numPerPage = 14;//每页数据量
	String pages = request.getParameter("page") ; 
	int currentPage = 1;
	currentPage = (pages==null)?1:Integer.parseInt(pages);
	sql = "select count(*) from tables"; 
	ResultSet rs = DBLink.executeQuery(sql) ; 
	while(rs.next()){
		i = rs.getInt(1);
	} 
	int yeshu = 1;//页数
	yeshu = (i%numPerPage==0)?(i/numPerPage):(i/numPerPage+1); 
	int nextPage ; 
	int upPage; 
	nextPage = currentPage + 1; 
	if (nextPage>=yeshu){
		nextPage=yeshu;
	}
	upPage = currentPage-1; 
	if (upPage<=1){
		upPage=1;
	} 
	rs.close(); 
	sql="select * from tables"; 
	rs=DBLink.executeQuery(sql); 
	i=0; 
	while((i<numPerPage*(currentPage-1))&&rs.next()){
		i++;
	} 
%> 
	//输出内容 
	//输出翻页连接 
	合计:<%=currentPage%>/<%=yeshu%><a href="List.jsp?page=1">第一页</a><a href="List.jsp?page=<%=upPage%>">上一页</a> 
<% 
	for(int j=1;j<=yeshu;j++){ 
		if(currentPage!=j){ 
%> 
			<a href="list.jsp?page=<%=j%>">[<%=j%>]</a> 
<% 
 		}else{ 
 			out.println(j); 
 		}
 	} 
%>
	<a href="List.jsp?page=<%=nextPage%>">下一页</a><a href="List.jsp?page=<%=yeshu%>">最后页</a>

一、分页实现及性能

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条记录存在两种方法：

1、在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。

2、去掉查询第二层的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 /*+ FIRST_ROWS */ * FROM 
(
 SELECT A.*, ROWNUM RN 
 FROM (SELECT * FROM TABLE_NAME) A 
 WHERE ROWNUM <= 40
)
 WHERE RN >= 21

二、Oracle Top n

实现top n的功能

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

以下sql

SELECT A.*, ROWNUM RN 
 FROM (SELECT * FROM TABLE_NAME) A 
 WHERE ROWNUM between 2 and 100

总是返回空记录

原因：
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。

举例说明：
例：student(学生)表：

create table student (ID char(6), name VARCHAR2(100));
insert into sale values('200001',‘张一’);
insert into sale values('200002',‘王二’);
insert into sale values('200003',‘李三’);
insert into sale values('200004',‘赵四’);
commit;

(1) rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。

SQL> select rownum,id,name from student where rownum=1;

（2）rownum对于大于某值的查询条件
如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录
SQL> select rownum,id,name from student where rownum >2;
那如何才能找到第二行以后的记录呢。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。

SQL>select * from (select rownum no ,id,name from student) where no>2;

（3）rownum对于小于某值的查询条件
如果想找到第三条记录以前的记录，当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。
SQL> select rownum,id,name from student where rownum <3;
综上几种情况，可能有时候需要查询rownum在某区间的数据，从上可以看出rownum对小于某值的查询条件是人为true的，rownum对于大于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。

SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;

（4）rownum和排序
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。

SQL> select rownum ,id,name from student order by name;

可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询

SQL> select rownum ,id,name from (select * from student order by name);

这样就成了按name排序，并且用rownum标出正确序号（有小到大）

其实, 理解rownum的关键是Oracle 如何执行查询语句. 如果先执行笛卡尔集运算,再执行where条件限制,那么rownum就可以实现 rownum> n(n>=1)的功能. 但oralce是边执行笛卡尔集运算,边应用选择条件,所以rownum>n(n>1=)永远不成立

oracle分页