SQL语句优化

最新推荐文章于 2024-03-04 10:45:54 发布

Snowballl

最新推荐文章于 2024-03-04 10:45:54 发布

阅读量459

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/minwang593/article/details/12851601

版权

oracle 专栏收录该内容

116 篇文章 1 订阅

订阅专栏

(一)语句一

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-17','yyyy-mm-dd')
ORDER BY C.COMING_TIME, C.VIN DESC)

WHERE ROWNUM <= 5000

(二)执行计划

explain plan for 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-17','yyyy-mm-dd')
ORDER BY C.COMING_TIME, C.VIN DESC)
WHERE ROWNUM <= 5000

(三）查看执行计划

select * from table(dbms_xplan.display); #必须要在同一会话中执行

Plan hash value: 2332382148

-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 11M| | 211K (1)| 00:49:26 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 313K| 703M| | 211K (1)| 00:49:26 | | |
|* 3 | SORT UNIQUE STOPKEY | | 313K| 73M| 79M| 201K (1)| 00:47:00 | | |
|* 4 | HASH JOIN | | 313K| 73M| | 190K (1)| 00:44:34 | | |
| 5 | TABLE ACCESS FULL | NT_BB_SERVICE_STATIONS | 1690 | 55770 | | 73 (0)| 00:00:02 | | |
|* 6 | HASH JOIN OUTER | | 313K| 63M| 42M| 190K (1)| 00:44:33 | | |
|* 7 | HASH JOIN | | 250K| 39M| 25M| 177K (1)| 00:41:27 | | |
| 8 | PARTITION RANGE ITERATOR| | 250K| 22M| | 2427 (1)| 00:00:34 | 8 | 9 |
|* 9 | TABLE ACCESS FULL | TMP1 | 250K| 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"."RU_UPLOADED_DATE"<=TIMESTAMP' 2013-10-17 00:00:00' AND "A"."IS_INACTIVE"=0 AND ("A"."COMPANY_ID"=0 OR
"A"."COMPANY_ID"=1002))
11 - filter("T3"."VALUE1"(+)=1)
12 - filter(ROW_NUMBER() OVER ( PARTITION BY "DEALER_ID","CO_NO" ORDER BY INTERNAL_FUNCTION("INQUIRED_DATE") DESC )<=1)

(4)解决方案

上面经过对tmp1的分区，降低很多的逻辑读现象，性能已经得到了很好的提升，但TMP1 通过外键与表NT_AS_SERVICE_ORDERS进行连接，

NT_AS_SERVICE_ORDERS C ON A.DEALER_ID = C.DEALER_ID
AND A.SO_NO = C.SO_NO

这样导致NT_AS_SERVICE_ORDERS 都是全表扫描，这里需要通过一个过滤条件，时间进行一个限制，这样也能解决表NT_AS_SERVICE_ORDERS 性能问题。COMING_TIME

alter table NT_AS_SERVICE_ORDERS
add constraint PK_NT_AS_SERVICE_ORDERS primary key (SO_NO, DEALER_ID)
using index
tablespace DCS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 80K
next 1M
minextents 1
maxextents unlimited
);