大家能发现这个SQL的问题吗?这个SQL之所以跑得慢是因为开发人员把SQL的条件写错位置了
正确的写法应该是下面这样的
select * from (select u.NAME UniversityName, u.id UniversityId, count(a.SIGNUPNUMBER) playercnt from T_B_UNIVERSITY u left join T_D_EDUCATION e on e.UNIVERSITY_ID = u.id and e.ISDEFAULT = 1 and e.ISVALID = 1 left join T_D_VIDEO_PLAYER a on a.USER_ID = e.user_id and a.AUDITSTATUS = 1 and a.ISVALID = 1 left join T_D_USER c on a.USER_ID = c.id and c.ISVALID = 1 where u.REGION_CODE like '43%' group by u.NAME, u.id) order by playercnt desc; |
执行计划如下
执行计划 ---------------------------------------------------------- Plan hash value: 2738827747
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 142 | 11218 | 25 (16)| 00:00:01 | | 1 | SORT ORDER BY | | 142 | 11218 | 25 (16)| 00:00:01 | | 2 | HASH GROUP BY | | 142 | 11218 | 25 (16)| 00:00:01 | |* 3 | HASH JOIN RIGHT OUTER | | 301 | 23779 | 23 (9)| 00:00:01 | |* 4 | TABLE ACCESS FULL | T_D_USER | 690 | 5520 | 5 (0)| 00:00:01 | |* 5 | HASH JOIN RIGHT OUTER| | 301 | 21371 | 17 (6)| 00:00:01 | |* 6 | TABLE ACCESS FULL | T_D_VIDEO_PLAYER | 78 | 1170 | 3 (0)| 00:00:01 | |* 7 | HASH JOIN OUTER | | 301 | 16856 | 14 (8)| 00:00:01 | |* 8 | TABLE ACCESS FULL | T_B_UNIVERSITY | 50 | 2050 | 8 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL | T_D_EDUCATION | 301 | 4515 | 5 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("A"."USER_ID"="C"."ID"(+)) 4 - filter("C"."ISVALID"(+)=1) 5 - access("A"."USER_ID"(+)="E"."USER_ID") 6 - filter("A"."AUDITSTATUS"(+)=1 AND "A"."ISVALID"(+)=1) 7 - access("E"."UNIVERSITY_ID"(+)="U"."ID") 8 - filter("U"."REGION_CODE" LIKE '43%') 9 - filter("E"."ISDEFAULT"(+)=1 AND "E"."ISVALID"(+)=1) |
之前SQL要跑至少5秒以上,现在0.1秒能出结果。
各位童鞋,SQL有外连接的时候,要注意过滤条件的位置,记住啦!!!