SQL优化之四两拨千金

最新推荐文章于 2024-04-07 10:50:37 发布

深圳gg

最新推荐文章于 2024-04-07 10:50:37 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle SQL优化

本文链接：https://blog.youkuaiyun.com/stevendbaguo/article/details/77849170

Oracle SQL优化专栏收录该内容

130 篇文章

订阅专栏

本文通过对比两种SQL分页查询方案的执行效率，展示了如何通过调整查询策略避免全表扫描，减少资源消耗，并提供了一种更为高效的分页查询优化方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MIS惯用分页，有的时候关联的表都非常大。如下所示的SQL，如果按照如下形式写，则会造成全表扫描。

原始SQL：

SQL> SELECT *
FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (SELECT GAC.CONFIRM_ID,
GAC.CONFIRM_NAME,
GAC.CONFIRM_DATE,
GAC.BUREAU_ID,
GAC.BUREAU_NAME,
GAC.CREATOR_ID,
GAC.CREATOR,
GAC.CREATE_DATE,
GAC.STATUS,
GAC.BID_TARGET,
GAC.MEETING_MINUTES,
GAC.F1,
GAC.F2,
GAC.REMARK,
GAC.VALIDATE_FLAG,
GAC.READ_FLAG,
GAC.BACK_FLAG,
GAC.CONFIRM_TYPE,
GAC.DATA_AREA,
GAC.PROJECT_ID,
GAC.PROJECT_NAME,
GAC.SUBPROJECT_ID,
GAC.SUBPROJECT_NAME,
GAC.OLD_CONFIRM_ID,
GAC.CONFIRM_CHANGE_REASON,
GAC.IS_STATISTIC,
GAC.UPDATE_FLAG,
GAC.AVOID_DUTY_REASON,
GAC.LOCAL_PROJECT_CODE,
GAC.UNIQUE_PROJECT_CODE,
MAX(GACI.REQUIREMENT_ID) AS REQUIREMENT_ID
FROM GG_ASSIGNMENT_CONFIRM GAC, GG_ASSIGNMENT_CONFIRM_ITEM GACI
WHERE GAC.CONFIRM_ID = GACI.CONFIRM_ID(+)
AND GAC.CREATE_DATE >= TO_DATE('2016-09-04', 'yyyy-mm-dd')
AND GAC.CREATE_DATE < TO_DATE('2017-09-04', 'yyyy-mm-dd') + (INTERVAL '1' DAY)
GROUP BY GAC.CONFIRM_ID,
GAC.CONFIRM_NAME,
GAC.CONFIRM_DATE,
GAC.BUREAU_ID,
GAC.BUREAU_NAME,
GAC.CREATOR_ID,
GAC.CREATOR,
GAC.CREATE_DATE,
GAC.STATUS,
GAC.BID_TARGET,
GAC.MEETING_MINUTES,
GAC.F1,
GAC.F2,
GAC.REMARK,
GAC.VALIDATE_FLAG,
GAC.READ_FLAG,
GAC.BACK_FLAG,
GAC.CONFIRM_TYPE,
GAC.DATA_AREA,
GAC.PROJECT_ID,
GAC.PROJECT_NAME,
GAC.SUBPROJECT_ID,
GAC.SUBPROJECT_NAME,
GAC.OLD_CONFIRM_ID,
GAC.CONFIRM_CHANGE_REASON,
GAC.IS_STATISTIC,
GAC.UPDATE_FLAG,
GAC.AVOID_DUTY_REASON,
GAC.LOCAL_PROJECT_CODE,
GAC.UNIQUE_PROJECT_CODE
ORDER BY GAC.CONFIRM_TYPE ASC NULLS LAST) INNER_TABLE
WHERE ROWNUM <=25) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM >0;
已选择25行。
已用时间: 00: 00: 30.58
执行计划
----------------------------------------------------------
Plan hash value: 1427996981
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 109K| | 160K (1)| 00:32:06 | | |
|* 1 | VIEW | | 25 | 109K| | 160K (1)| 00:32:06 | | |
|* 2 | COUNT STOPKEY | | | | | | | | |
| 3 | VIEW | | 589K| 2512M| | 160K (1)| 00:32:06 | | |
|* 4 | SORT GROUP BY STOPKEY | | 589K| 196M| 209M| 160K (1)| 00:32:06 | | |
|* 5 | HASH JOIN OUTER | | 589K| 196M| 32M| 116K (1)| 00:23:17 | | |
| 6 | PARTITION RANGE ITERATOR| | 105K| 31M| | 3310 (1)| 00:00:40 | 7 | 8 |
| 7 | PARTITION LIST ALL | | 105K| 31M| | 3310 (1)| 00:00:40 | 1 | 27 |
|* 8 | TABLE ACCESS FULL | GG_ASSIGNMENT_CONFIRM | 105K| 31M| | 3310 (1)| 00:00:40 | 163 | 216 |
| 9 | PARTITION RANGE ALL | | 3276K| 112M| | 103K (1)| 00:20:48 | 1 |1048575|
| 10 | PARTITION LIST ALL | | 3276K| 112M| | 103K (1)| 00:20:48 | 1 | 27 |
| 11 | TABLE ACCESS FULL | GG_ASSIGNMENT_CONFIRM_ITEM | 3276K| 112M| | 103K (1)| 00:20:48 | 1 |1048575|
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OUTER_TABLE_ROWNUM">0)
2 - filter(ROWNUM<=25)
4 - filter(ROWNUM<=25)
5 - access("GAC"."CONFIRM_ID"="GACI"."CONFIRM_ID"(+))
8 - filter("GAC"."CREATE_DATE">=TO_DATE(' 2016-09-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"GAC"."CREATE_DATE"<TO_DATE(' 2017-09-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
374591 consistent gets
307518 physical reads
14208 redo size
6776 bytes sent via SQL*Net to client
483 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
25 rows processed

优化后：四两拨千金就是先把主表的数据先查出25条，然后跟从表关联，这是一种很重要的优化思路。
SQL> with GAC as (SELECT *
FROM (SELECT INNER_TABLE.*, ROWNUM OUTER_TABLE_ROWNUM
FROM (SELECT GAC.CONFIRM_ID,
GAC.CONFIRM_NAME,
GAC.CONFIRM_DATE,
GAC.BUREAU_ID,
GAC.BUREAU_NAME,
GAC.CREATOR_ID,
GAC.CREATOR,
GAC.CREATE_DATE,
GAC.STATUS,
GAC.BID_TARGET,
GAC.MEETING_MINUTES,
GAC.F1,
GAC.F2,
GAC.REMARK,
GAC.VALIDATE_FLAG,
GAC.READ_FLAG,
GAC.BACK_FLAG,
GAC.CONFIRM_TYPE,
GAC.DATA_AREA,
GAC.PROJECT_ID,
GAC.PROJECT_NAME,
GAC.SUBPROJECT_ID,
GAC.SUBPROJECT_NAME,
GAC.OLD_CONFIRM_ID,
GAC.CONFIRM_CHANGE_REASON,
GAC.IS_STATISTIC,
GAC.UPDATE_FLAG,
GAC.AVOID_DUTY_REASON,
GAC.LOCAL_PROJECT_CODE,
GAC.UNIQUE_PROJECT_CODE
FROM GG_ASSIGNMENT_CONFIRM GAC
WHERE GAC.CREATE_DATE >= TO_DATE('2016-09-04', 'yyyy-mm-dd')
AND GAC.CREATE_DATE < TO_DATE('2017-09-04', 'yyyy-mm-dd') + (INTERVAL '1' DAY)
order by GAC.CREATE_DATE desc) INNER_TABLE
WHERE ROWNUM <=25) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM >0)
select * from (select GAC.*,
row_number() over(partition by GAC.Confirm_Id order by REQUIREMENT_ID desc) rn,
GACI.REQUIREMENT_ID AS REQUIREMENT_ID
FROM GAC, GG_ASSIGNMENT_CONFIRM_ITEM GACI
WHERE GAC.CONFIRM_ID = GACI.CONFIRM_ID) where rn=1
order by CREATE_DATE desc;
已选择25行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 1757804957
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 140 | 614K| 112 (2)| 00:00:02 | | |
| 1 | SORT ORDER BY | | 140 | 614K| 112 (2)| 00:00:02 | | |
|* 2 | VIEW | | 140 | 614K| 111 (1)| 00:00:02 | | |
|* 3 | WINDOW SORT PUSHED RANK | | 140 | 614K| 111 (1)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 140 | 614K| 110 (0)| 00:00:02 | | |
|* 5 | VIEW | | 25 | 108K| 10 (0)| 00:00:01 | | |
|* 6 | COUNT STOPKEY | | | | | | | |
| 7 | VIEW | | 25 | 108K| 10 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| GG_ASSIGNMENT_CONFIRM | 105K| 31M| 10 (0)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN DESCENDING | INDEX_CREATE_DATE_AC_12 | 25 | | 3 (0)| 00:00:01 | | |
| 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_ASSIGNMENT_CONFIRM_ITEM | 6 | 216 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 11 | INDEX RANGE SCAN | IDX_GG_ASS_CONF_ID_12 | 6 | | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "OUTER_TABLE"."CONFIRM_ID" ORDER BY INTERNAL_FUNCTION("GACI"."REQUIREMENT_ID")
DESC )<=1)
5 - filter("OUTER_TABLE_ROWNUM">0)
6 - filter(ROWNUM<=25)
9 - access("GAC"."CREATE_DATE">=TO_DATE(' 2016-09-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "GAC"."CREATE_DATE"<TO_DATE('
2017-09-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("OUTER_TABLE"."CONFIRM_ID"="GACI"."CONFIRM_ID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
295 consistent gets
0 physical reads
0 redo size
8139 bytes sent via SQL*Net to client
483 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
25 rows processed