一个rownum查询慢的sql优化过程

1、sql1：这种写法，查询速度不稳定，某些情况下很快，某些情况下很慢（就是快的时候很快，慢的时候很慢，主要是两表关联时，两个表都有where条件时，会有这种情况）

SELECT *   FROM (SELECT TMP_PAGE.*,ROWNUM ROW_ID           FROM (select RCVFEE.Company_Cd,                                   p.*                                         from T_CMS_RCVFEE_ORG RCVFEE,T_CMS_RCVFEE p                  where p.RCVFEE_ORG_ID = RCVFEE.RCVFEE_ORG_ID                   AND RCVFEE.BU_CD = '2427'                   AND RCVFEE.TRANSSIGN_FLAG = '0'                   AND p.CONFIRM_FLAG = '0'                                                           ) TMP_PAGE          WHERE ROWNUM <= 15)  WHERE ROW_ID > 0;

2、sql2：改成这种写法，查询速度基本稳定，速度较快（就是不会很慢，但是有时没有1的写法快）

SELECT *   FROM (SELECT TMP_PAGE.*,ROWNUM ROW_ID           FROM (select RCVFEE.Company_Cd,                                   p.*                                         from T_CMS_RCVFEE_ORG RCVFEE,T_CMS_RCVFEE p                  where p.RCVFEE_ORG_ID = RCVFEE.RCVFEE_ORG_ID                   AND RCVFEE.BU_CD = '2427'                   AND RCVFEE.TRANSSIGN_FLAG = '0'                          AND p.CONFIRM_FLAG = '0'                                                           ) TMP_PAGE)  WHERE ROW_ID > 0 AND ROW_ID <= 15;

寻找解决方法：

由于分页组件是公司框架封装，且使用的开源组件，不能轻易修改，所以尝试其他解决方式，经过查找和试验，发现在sql1上添加如下HINT速度会提升不少

sql1优化：

SELECT *   FROM (SELECT  TMP_PAGE.*,ROWNUM ROW_ID           FROM (select /*+ ALL_ROWS */ RCVFEE.Company_Cd,                            p.*                                         from T_CMS_RCVFEE_ORG RCVFEE,T_CMS_RCVFEE p                  where p.RCVFEE_ORG_ID = RCVFEE.RCVFEE_ORG_ID                   AND RCVFEE.BU_CD = '2427'                   AND RCVFEE.TRANSSIGN_FLAG = '0'                   AND p.CONFIRM_FLAG = '0'                                                   ) TMP_PAGE          WHERE ROWNUM <= 150)  WHERE ROW_ID > 0;

优化前后执行时间对比：

sql1（未优化）的第一次执行时间：

Sql1（优化后）的第一次执行时间：

总结：

在两表关联查询，且两表分别经过where过滤后数据也都比较大，但是交集数据量不大，这时不添加rownum查询速度较快，但是添加了rownum时，速度会很慢，此时可以尝试添加HINT的优化方法。

补充：后来在实际环境中测试使用USE_HASH效果更好（因为实际环境中sql更复杂）

/*+ USE_HASH(RCVFEE,p) */

参考文档：

为什么SQL 语句加上 WHERE ROWNUM < 10 速度非常慢 - Oracle数据库管理 - Oracle数据库数据恢复、性能优化来问问AskMaclean - ParnassusData诗檀软件旗下网站 - Maclean Liu