相同语句，时间段不一样，不同执行计划分析心得
来源：互联网 发布：list数据排序 编辑：程序博客网 时间：2024/05/22 12:19
已知NT_AS_SERVICE_ORDERS是主表，TMP1是主表。它们之间连接通过字段DEALER_ID、SO_NO进行关联。在从表中是通过 时间进行过滤的，而主表中就没有过滤条件。
案例一：
SELECT *FROM (SELECT DISTINCT A.DEALER_ID,
A.CO_NO,
A.RU_UPLOADED_DATE,
A.CLAIM_CATEGORY,
A.CO_STATUS,
A.INQUIRY_RESULT,
C.VIN,
C.PLATE_NO,
C.OWNER_NAME,
C.COMING_TIME,
D.DEALER_SHORT_NAME AS DEALER_NAME,
D.DEALER_CODE,
D.SV_CENTER_ID,
D.PROVINCE,
T3.OPINIONS_DLR,
T3.HANDLED_DATE_DLR,
T3.HANDLER_DLR,
A.CLAIMING_TYPE,
A.CI_HANDLE_RESULT
FROM TMP1 A
JOIN NT_AS_SERVICE_ORDERS C
ON A.DEALER_ID = C.DEALER_ID
AND A.SO_NO = C.SO_NO
JOIN NT_BB_SERVICE_STATIONS D
ON A.DEALER_ID = D.DEALER_ID
LEFT JOIN (SELECT DEALER_ID,
CO_NO,
INQUIRED_DATE,
OPINIONS_DLR,
HANDLED_DATE_DLR,
HANDLER_DLR,
ROW_NUMBER() OVER(PARTITION BY DEALER_ID, CO_NO ORDER BY
INQUIRED_DATE DESC) AS VALUE1
FROM NT_CI_CLAIMING_INQUIRIES) T3
ON A.DEALER_ID = T3.DEALER_ID
AND A.CO_NO = T3.CO_NO
AND T3.VALUE1 = 1
WHERE (A.COMPANY_ID = 1002 OR A.COMPANY_ID = 0)
AND A.IS_INACTIVE = 0
AND A.RU_UPLOADED_DATE >= to_date('2013-07-01', 'yyyy-mm-dd')
AND A.RU_UPLOADED_DATE <= to_date('2013-07-02', 'yyyy-mm-dd')
ORDER BY C.COMING_TIME, C.VIN DESC)
WHERE ROWNUM <= 5000
执行计划：
Plan hash value: 53527625
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 966 | 2218K| | 7802 (13)| 00:01:50 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 966 | 2218K| | 7802 (13)| 00:01:50 | | |
|* 3 | SORT UNIQUE STOPKEY | | 966 | 1228K| 1312K| 7633 (13)| 00:01:47 | | |
| 4 | NESTED LOOPS | | | | | | | | |
| 5 | NESTED LOOPS | | 966 | 1228K| | 7464 (13)| 00:01:45 | | |
|* 6 | HASH JOIN | | 966 | 1161K| | 5531 (18)| 00:01:18 | | |
| 7 | TABLE ACCESS FULL | NT_BB_SERVICE_STATIONS | 1690 | 55770 | | 73 (0)| 00:00:02 | | |
| 8 | NESTED LOOPS OUTER | | 966 | 1130K| | 5457 (18)| 00:01:17 | | |
| 9 | PARTITION RANGE SINGLE | | 966 | 73416 | | 625 (0)| 00:00:09 | 8 | 8 |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TMP1 | 966 | 73416 | | 625 (0)| 00:00:09 | 8 | 8 |
|* 11 | INDEX RANGE SCAN | IDX_TMP1 | 966 | | | 3 (0)| 00:00:01 | 8 | 8 |
|* 12 | VIEW PUSHED PREDICATE | | 1 | 1122 | | 5 (20)| 00:00:01 | | |
|* 13 | WINDOW SORT PUSHED RANK | | 1 | 45 | | 5 (20)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | NT_CI_CLAIMING_INQUIRIES | 1 | 45 | | 4 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | IF_FK_CI_CO_INQUIRIES | 1 | | | 3 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | PK_NT_AS_SERVICE_ORDERS | 1 | | | 1 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | NT_AS_SERVICE_ORDERS | 1 | 71 | | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5000)
3 - filter(ROWNUM<=5000)
6 - access("A"."DEALER_ID"="D"."DEALER_ID")
10 - filter("A"."IS_INACTIVE"=0 AND ("A"."COMPANY_ID"=0 OR "A"."COMPANY_ID"=1002))
11 - access("A"."RU_UPLOADED_DATE">=TIMESTAMP' 2013-07-01 00:00:00' AND "A"."RU_UPLOADED_DATE"<=TIMESTAMP' 2013-07-02 00:00:00')
12 - filter("T3"."VALUE1"(+)=1)
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEALER_ID","CO_NO" ORDER BY INTERNAL_FUNCTION("INQUIRED_DATE") DESC )<=1)
15 - access("CO_NO"="A"."CO_NO" AND "DEALER_ID"="A"."DEALER_ID")
16 - access("A"."SO_NO"="C"."SO_NO" AND "A"."DEALER_ID"="C"."DEALER_ID")
注：此时通过NESTED LOOPS方式进行关联。
案例二：
SELECT *
FROM (SELECT DISTINCT A.DEALER_ID,
A.CO_NO,
A.RU_UPLOADED_DATE,
A.CLAIM_CATEGORY,
A.CO_STATUS,
A.INQUIRY_RESULT,
C.VIN,
C.PLATE_NO,
C.OWNER_NAME,
C.COMING_TIME,
D.DEALER_SHORT_NAME AS DEALER_NAME,
D.DEALER_CODE,
D.SV_CENTER_ID,
D.PROVINCE,
T3.OPINIONS_DLR,
T3.HANDLED_DATE_DLR,
T3.HANDLER_DLR,
A.CLAIMING_TYPE,
A.CI_HANDLE_RESULT
FROM TMP1 A
JOIN NT_AS_SERVICE_ORDERS C
ON A.DEALER_ID = C.DEALER_ID
AND A.SO_NO = C.SO_NO
JOIN NT_BB_SERVICE_STATIONS D
ON A.DEALER_ID = D.DEALER_ID
LEFT JOIN (SELECT DEALER_ID,
CO_NO,
INQUIRED_DATE,
OPINIONS_DLR,
HANDLED_DATE_DLR,
HANDLER_DLR,
ROW_NUMBER() OVER(PARTITION BY DEALER_ID, CO_NO ORDER BY
INQUIRED_DATE DESC) AS VALUE1
FROM NT_CI_CLAIMING_INQUIRIES) T3
ON A.DEALER_ID = T3.DEALER_ID
AND A.CO_NO = T3.CO_NO
AND T3.VALUE1 = 1
WHERE (A.COMPANY_ID = 1002 OR A.COMPANY_ID = 0)
AND A.IS_INACTIVE = 0
AND A.RU_UPLOADED_DATE >= to_date('2013-07-01', 'yyyy-mm-dd')
AND A.RU_UPLOADED_DATE <= to_date('2013-10-18', 'yyyy-mm-dd')
ORDER BY C.COMING_TIME, C.VIN DESC)
WHERE ROWNUM <= 5000
执行计划：
Plan hash value: 2332382148
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 11M| | 211K (1)| 00:49:28 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 315K| 707M| | 211K (1)| 00:49:28 | | |
|* 3 | SORT UNIQUE STOPKEY | | 315K| 74M| 79M| 201K (1)| 00:47:01 | | |
|* 4 | HASH JOIN | | 315K| 74M| | 191K (1)| 00:44:35 | | |
| 5 | TABLE ACCESS FULL | NT_BB_SERVICE_STATIONS | 1690 | 55770 | | 73 (0)| 00:00:02 | | |
|* 6 | HASH JOIN OUTER | | 315K| 64M| 42M| 190K (1)| 00:44:34 | | |
|* 7 | HASH JOIN | | 252K| 39M| 25M| 177K (1)| 00:41:27 | | |
| 8 | PARTITION RANGE ITERATOR| | 252K| 22M| | 2427 (1)| 00:00:34 | 8 | 9 |
|* 9 | TABLE ACCESS FULL | TMP1 | 252K| 22M| | 2427 (1)| 00:00:34 | 8 | 9 |
| 10 | TABLE ACCESS FULL | NT_AS_SERVICE_ORDERS | 11M| 757M| | 136K (1)| 00:31:47 | | |
|* 11 | VIEW | | 921K| 41M| | 9264 (1)| 00:02:10 | | |
|* 12 | WINDOW SORT PUSHED RANK | | 921K| 39M| 56M| 9264 (1)| 00:02:10 | | |
| 13 | TABLE ACCESS FULL | NT_CI_CLAIMING_INQUIRIES | 921K| 39M| | 2690 (2)| 00:00:38 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5000)
3 - filter(ROWNUM<=5000)
4 - access("A"."DEALER_ID"="D"."DEALER_ID")
6 - access("A"."CO_NO"="T3"."CO_NO"(+) AND "A"."DEALER_ID"="T3"."DEALER_ID"(+))
7 - access("A"."DEALER_ID"="C"."DEALER_ID" AND "A"."SO_NO"="C"."SO_NO")
9 - filter("A"."IS_INACTIVE"=0 AND ("A"."COMPANY_ID"=0 OR "A"."COMPANY_ID"=1002) AND "A"."RU_UPLOADED_DATE"<=TIMESTAMP'
2013-10-18 00:00:00')
11 - filter("T3"."VALUE1"(+)=1)
12 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEALER_ID","CO_NO" ORDER BY INTERNAL_FUNCTION("INQUIRED_DATE") DESC )<=1)
注：通过hash join进行两个表间的关联。
总结以下几个结论：
1 主从表的连接方式，是这样的，先过滤掉从表的数据，能过滤的越多就越好。
2 主从表连接方式都是通过外键进行
3 如果从表过滤的数据很多，如果从表查出的记录为1条，它的性能是最优，通过NESTED LOOPS方式，这样与主表通过外键连接，效率是最高的
4 如果从表过滤的数据并不是很多，如果从表查出的记录为很多很多，这种情况下，优化器默认会选择hash join的方式，这种情况下就会对主表进行全表扫描，考虑这种方案的优化，尽量在主表中加个过滤条件，从而能提升性能
- 相同语句，时间段不一样，不同执行计划分析心得
- 返回相同结果，用不同SQL的执行计划
- 一个sql语句执行计划的分析
- SQL语句执行计划
- sql语句执行计划
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL
- [转]分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL
- [转]通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL(三)
- 通过分析SQL语句的执行计划优化SQL(五)
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL(总结)
- 通过分析SQL语句的执行计划优化SQL
- 通过分析SQL语句的执行计划优化SQL(总结)
- 通过分析SQL语句的执行计划优化SQL(二)
- 对象的序列化
- Php生成缩略图
- Inject EJB to Struts 2 Action and Custom Validators
- 网络游戏服务器架构2
- 全新整理：微软、谷歌、百度等公司经典面试100题[第101-160题]
- 相同语句，时间段不一样，不同执行计划分析心得
- BackTrack5 PIN码破解无线路由器
- Java大文件上传
- ubuntu tomcat6 设置JAVA_HOME
- 用JavaScript(js)对时间格式化
- 三大框架整合ssh（二）------DAO层 源码
- 十道海量数据处理面试题与十个方法大总结
- weblogic12.1.1.0部署项目时报Exception in AppMerge flows' progression错误
- hadoop集群管理之SecondaryNameNode与NameNode
