近日客户说系统导出数据不正确，丢失数据，自己查了看了一下程序没问题，以前测试也没发现。后来定位到是sql语句问题，原来分页sql查询的时候，order by的字段
必须是唯一的字段。
网上有提到这样的，自己留个记号。
SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6236 BCB CCC
6235 AL_U1 CCC
6234 AL_P CCC
6240 BCF_U1 CCC
6239 BCF_P CCC
6238 BCF CCC
6237 BCB_U1 CCC
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
上面例子给出的就是分页查询的标准写法，对于查询前N条数据具有最高的效率。
但是这种分页排序语句存在一个问题：
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6245 BDF_P CCC
6243 BDF_I_BS_KEY CCC
6241 BCF_U2 CCC
6239 BCF_P CCC
6237 BCB_U1 CCC
6236 BCB CCC
6235 AL_U1 CCC
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
对比这次的结果和第一次的结果，就会发现ID为6235的数据出现了两次。第一次在前10条返回记录中，6235出现了，而第二次在11到第20条记录中，6235又出现了。一条数据重复出现两次，就必然意味着有数据在两次查询中都不会出现。
其实造成这个问题的原因很简单，是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性，也就是说，对于键值相等的数据，这种算法完成排序后，不保证这些键值相等的数据保持排序前的顺序。
在这个例子中，OWNER列包含了大量键值为CCC的数据，而且Oracle的排序算法不具有稳定性，因此前10行记录和前20行记录中键值的顺序不能保证一致。因此，就造成某些数据会重复出现，而有些数据不会出现的现象。
解决这个问题其实也很简单。有两种方法可以考虑。
一，在使用不唯一的字段排序时，后面跟一个唯一的字段。
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 10
11 )
12 WHERE RN >= 1;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6235 AL_U1 CCC
6236 BCB CCC
6237 BCB_U1 CCC
6238 BCF CCC
6239 BCF_P CCC
6240 BCF_U1 CCC
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=10 Bytes=1090)
1 0 VIEW (Cost=72 Card=10 Bytes=1090)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER, ID
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6241 BCF_U2 CCC
6242 BDF CCC
6243 BDF_I_BS_KEY CCC
6244 BDF_I_DF_KEY CCC
6245 BDF_P CCC
6246 BDF_U1 CCC
6247 BP CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6249 BP_P CCC
6250 BP_U1 CCC
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=20 Bytes=2180)
1 0 VIEW (Cost=72 Card=20 Bytes=2180)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY STOPKEY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
一般在排序字段后面跟一个主键就可以了，如果表不存在主键，跟ROWID也可以。
这种方法最简单，且对性能的影响最小。另一种方法就是使用前面给出过多次的BETWEEN AND的方法。
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 1 AND 10;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6231 AL CCC
6232 AL_I_2 CCC
6233 AL_I_FNAME_STATUS CCC
6234 AL_P CCC
6238 BCF CCC
6240 BCF_U1 CCC
6242 BDF CCC
6244 BDF_I_DF_KEY CCC
6246 BDF_U1 CCC
6255 BRL_U1 CCC
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
SQL> SELECT ID, OBJECT_NAME, OWNER
2 FROM
3 (
4 SELECT ROWNUM RN, ID, OBJECT_NAME, OWNER
5 FROM
6 (
7 SELECT OWNER, OBJECT_NAME, ID
8 FROM TEST ORDER BY OWNER
9 )
10 )
11 WHERE RN BETWEEN 11 AND 20;
ID OBJECT_NAME OWNER
---------- ------------------------------ ------------------------------
6254 BRL_P CCC
6253 BRL_I_DTS CCC
6252 BRL_I_BS_KEY CCC
6251 BRL CCC
6250 BP_U1 CCC
6249 BP_P CCC
6248 BP_I_DEVICE_HANDLE_STATUS CCC
6247 BP CCC
6264 CCF CCC
6263 CCB_U1 CCC
已选择10行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=6363 Bytes=693567)
1 0 VIEW (Cost=72 Card=6363 Bytes=693567)
2 1 COUNT
3 2 VIEW (Cost=72 Card=6363 Bytes=610848)
4 3 SORT (ORDER BY) (Cost=72 Card=6363 Bytes=165438)
5 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=20 Card=6363 Bytes=165438)
这种方式由于采用表数据的全排序，每次只取全排序中的某一部分数据，因此不会出现上面提到的重复数据问题。
但是正是由于使用了全排序，而且ROWNUM信息无法推到查询内部，导致这种写法的执行效率很低。对比二种效率的例子，前面分页查询的文章中以及有很多了，这里就不在重复描述了。虽然这种方式也可以避免重复数据问题，但是不推荐使用这种方式。
分享到：
相关推荐
本文主要解决 Oracle 分页查询中排序与效率问题，通过实践和分析，提供了两种解决方案，并对比了两种方法的优缺点。 知识点 1: Oracle 分页查询的基本概念 Oracle 分页查询是指在查询结果中，通过限制行数来实现...
### 解决Oracle分页查询中排序与效率问题 在Oracle数据库中进行分页查询时，经常会出现性能瓶颈，尤其是在处理大数据量的情况下。本篇文章将详细探讨如何优化Oracle分页查询中的排序与效率问题。 #### 一、理解...
【描述】：这个描述暗示了我们在开发过程中可能遇到的问题——如何在网页上以分页的形式展示大量数据，并允许用户通过点击表头进行数据排序，同时提供查询功能以便快速定位所需信息。在DWZ.NET中，这些功能可以通过...
1. 创建数据访问层（DAL）的方法，处理数据库查询，包括分页和排序。 2. 在业务逻辑层（BLL）中调用DAL方法，添加必要的业务逻辑。 3. 在ASP.NET页面中，配置DataList或Repeater控件，设置分页和排序属性，编写事件...
可以使用Session或ViewState来存储排序信息，或者如果使用的是服务器端分页，可以在查询时直接带上排序参数。 总结起来，Asp.Net中的分页和排序技术是提高数据展示性能和交互性的重要手段。通过使用Repeater控件，...
在数据库层面，SQL语言提供了ORDER BY子句用于数据排序，LIMIT和OFFSET组合则用于实现分页查询。对于大数据处理，还可以利用数据库的索引优化排序和分页的效率。 在移动端开发中，iOS的Swift和Android的Java/Kotlin...
下面我们将详细探讨SQLite中的查询所有、分页查询以及查询个数的相关知识点。 1. 查询所有数据： 在SQLite中，你可以使用SQL的SELECT语句来查询表中的所有数据。基本语法如下： ```sql SELECT * FROM 表名; ``` ...
分页和排序，应该是软件开发中，需要必知必会的技能了，对于分页，网上很多教程，当然，别人终究是别人的，只有自己理解，会了，并且吸收之后，再用自己的语言，传授出来，这才是硬道理。好了，废话说多了。现在我们...
本文将深入探讨一个在 SQL Server 2005 中实现高效分页查询的存储过程 `UP_GetRecordByPage`，该过程不仅支持基本的分页功能，还能够根据用户提供的参数对查询结果进行灵活的排序。此存储过程通过动态构建 SQL 语句...
在本主题“XML分页排序数据岛”中，我们将深入探讨如何利用XML数据岛实现数据的分页和排序功能，特别是在与后台数据源结合的情况下。 首先，理解XML数据岛的概念至关重要。XML是一种结构化数据的标记语言，用于存储...
本篇文章将详细探讨如何基于Spring MVC实现分页查询，这对于任何处理大量数据的Web应用都是至关重要的。 首先，理解分页的基本概念。分页是将大型数据集划分为较小、更易管理的部分，以提高用户体验并减少服务器...
在这个场景中，我们将探讨如何使用JPA进行分页查询以及带有条件的分页查询。 首先，为了使用Spring Data JPA，我们需要在项目中引入相应的依赖。在Maven的pom.xml文件中，你需要添加`spring-boot-starter-data-jpa`...
因此，设计一种能够支持多表查询和任意排序功能的分页存储过程变得尤为重要。 ### 二、千万数量级分页存储过程的关键技术 #### 1. 多表查询支持 - **定义**：多表查询是指在一个SQL语句中同时查询多个表的数据。 -...
在网页开发中，数据展示是不可或缺的一部分，尤其是当数据量较大时，动态表格数据分页检索排序就显得尤为重要。这个项目“jQuery动态表格数据分页检索排序代码”提供了一个纯前端实现的解决方案，无需服务器端的支持...
在这个资源包中，我们主要探讨四个核心概念：分页、模糊查询、排序以及前后端交互，这些都与高效的数据处理紧密相关。 首先，**分页**是一种优化用户体验和提高系统性能的技术。在大量数据的情况下，一次性加载所有...
在ExtJS中，分页排序是实现大规模数据展示的重要功能，它允许用户在大量数据中高效地浏览和管理信息。 **分页** 分页是将大数据集分割成较小的部分，每次只加载一部分数据到页面上，以提高性能和用户体验。在ExtJS...
在MySQL中，一个经典的分页排序存储过程可能如下： ```sql DELIMITER // CREATE PROCEDURE GetPagedSortedData(IN page INT, IN pageSize INT, IN sortColumn VARCHAR(255), IN sortOrder ENUM('ASC', 'DESC')) ...
// 重新排序查询并绑定数据 } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { // 更新页码并重新绑定数据 } ``` 4. **前端定制**： GridView默认的样式可能不...
在本文中，我们将深入探讨如何在ASP.NET MVC4框架中实现分页、排序和搜索功能，这些功能对于任何Web应用程序的用户体验都至关重要。MVC4是一个流行且强大的开发平台，用于构建可伸缩、高性能的Web应用。我们将通过一...
jQuery分页排序插件是前端开发中常用的工具，它极大地提升了网页数据展示的用户体验，尤其在处理大量数据时。这款插件允许用户对HTML结构进行排序、分页和过滤，无需复杂的后端处理，减轻了服务器负担，同时也提高了...