SQL優化經典案例----外連接配接where條件位置優化

  ---轉載http://blog.sina.com.cn/s/blog_61cd89f60102efc9.html

    在RBO模式上，可以調整多表之間的先後順序進行優化，但是在CBO模式上這條優化已經不在适用，但是where條件的位置不同會不會導緻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

          left join T_D_VIDEO_PLAYER a

            on a.USER_ID = e.user_id

           and e.ISDEFAULT = 1

           and e.ISVALID = 1

           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: 3938743742  

-------------------------------------------------------------------------------------------- 

| Id  | Operation               | Name             | Rows  | Bytes | Cost (%CPU)| Time     | 

-------------------------------------------------------------------------------------------- 

|   0 | SELECT STATEMENT        |                  |   142 | 10366 |   170   (3)| 00:00:03 | 

|   1 |  SORT ORDER BY          |                  |   142 | 10366 |   170   (3)| 00:00:03 | 

|   2 |   HASH GROUP BY         |                  |   142 | 10366 |   170   (3)| 00:00:03 | 

|*  3 |    HASH JOIN RIGHT OUTER|                  |   672 | 49056 |  168   (2)| 00:00:03 | 

|*  4 |     TABLE ACCESS FULL   | T_D_USER         |   690 |  5520 |     5   (0)| 00:00:01 | 

|   5 |     NESTED LOOPS OUTER  |                  |   672 | 43680 |  162   (1)| 00:00:02 | 

|*  6 |      HASH JOIN OUTER    |                  |   672 | 37632 |    14   (8)| 00:00:01 | 

|*  7 |       TABLE ACCESS FULL | T_B_UNIVERSITY   |    50 |  2050 |     8  (0)| 00:00:01 | 

|   8 |       TABLE ACCESS FULL | T_D_EDUCATION    |   672 | 10080 |     5  (0)| 00:00:01 | 

|   9 |      VIEW               |                  |    1 |     9 |     0   (0)| 00:00:01 | 

|* 10 |       FILTER            |                  |      |       |            |          | 

|* 11 |        TABLE ACCESS FULL| T_D_VIDEO_PLAYER |     1 |    15 |     3  (0)| 00:00:01 | 

--------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id): 

---------------------------------------------------  

   3 - access("A"."USER_ID"="C"."ID"(+)) 

   4 - filter("C"."ISVALID"(+)=1) 

   6 - access("E"."UNIVERSITY_ID"(+)="U"."ID") 

   7 - filter("U"."REGION_CODE" LIKE '43%') 

  10 - filter("E"."ISVALID"=1 AND "E"."ISDEFAULT"=1) 

  11 - filter("A"."USER_ID"="E"."USER_ID" AND "A"."AUDITSTATUS"=1 AND 

              "A"."ISVALID"=1) 

這條SQL優化前需要20秒才能出結果，之是以慢，是因為條件的位置寫的有問題，進行了優化，優化後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執行時間在1秒内即可以出結果，該方法也是優化思路中的一種，在此記錄一下！