关闭

HINT的神奇

588人阅读 评论(0) 收藏 举报
分类:

橙子和落落帮我优化的SQL,使用HINT

分区大表100W数据,优化前70秒,优化后0.19秒。


explain plan for SELECT T2."TYPE_NAME",
       SUM(T1."USERS_ACCESS"),
       SUM(T1."USERS_USE"),
       SUM(T1."USERS_USE_PAY"),
       SUM(T1."USE_ACCORD"),
       SUM(T1."ACCORD_COST"),
       SUM(((T1."USERS_USE" / 7) * 100))
  FROM CNTV.DW_CMCC_NODE_BASE_T T1, CNTV.UMS_BUSI_TYPE_NODE T2
 WHERE (T2."NODE1" = T1."NODE_ONE")
   AND (T2."NODE2" = T1."NODE_TWO")
   AND (T2."NODE3" = T1."NODE_THREE")
   AND (T2."NODE4" = T1."NODE_FOUR")
   AND (T1."STATE" = '0')
   AND (T1."ITEM1_NAME" LIKE '%所有%')
   AND (T1."BUSINESS_DATE" BETWEEN '20131121' AND '20131127')
   AND (T2."TYPE_NAME" NOT LIKE '%直播%') AND
       (T2."TYPE_NAME" NOT LIKE '%免费%')
 GROUP BY T2."TYPE_NAME"
 ORDER BY T2."TYPE_NAME";
 
select * from table(dbms_xplan.display);


Plan hash value: 1326282012
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |   371 |     4  (25)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                      |                     |     1 |   371 |     4  (25)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                      |                     |       |       |            |          |       |       |
|   3 |    NESTED LOOPS                     |                     |     1 |   371 |     3   (0)| 00:00:01 |       |       |
|*  4 |     TABLE ACCESS FULL               | UMS_BUSI_TYPE_NODE  |   421 | 23997 |     3   (0)| 00:00:01 |       |       |
|   5 |     PARTITION RANGE SINGLE          |                     |     1 |       |     0   (0)| 00:00:01 |    23 |    23 |
|*  6 |      INDEX RANGE SCAN               | INX_CM_NODE_DATE_GP |     1 |       |     0   (0)| 00:00:01 |    23 |    23 |
|*  7 |    TABLE ACCESS BY LOCAL INDEX ROWID| DW_CMCC_NODE_BASE_T |     1 |   314 |     0   (0)| 00:00:01 |    23 |    23 |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("T2"."TYPE_NAME" NOT LIKE '%直播%' AND "T2"."TYPE_NAME" NOT LIKE '%免费%' AND "T2"."TYPE_NAME" IS NOT 
              NULL AND "T2"."TYPE_NAME" IS NOT NULL)
   6 - access("T1"."BUSINESS_DATE">='20131121' AND "T1"."BUSINESS_DATE"<='20131127')
   7 - filter("T1"."ITEM1_NAME" LIKE '%所有%' AND "T1"."STATE"='0' AND "T2"."NODE1"="T1"."NODE_ONE" AND 
              "T2"."NODE2"="T1"."NODE_TWO" AND "T2"."NODE3"="T1"."NODE_THREE" AND "T2"."NODE4"="T1"."NODE_FOUR")
              
              
              
优化后        
方法一:0.19秒 
explain plan for SELECT /*+ use_hash(t1,t2) */T2."TYPE_NAME",
       SUM(T1."USERS_ACCESS"),
       SUM(T1."USERS_USE"),
       SUM(T1."USERS_USE_PAY"),
       SUM(T1."USE_ACCORD"),
       SUM(T1."ACCORD_COST"),
       SUM(((T1."USERS_USE" / 7) * 100))
  FROM CNTV.DW_CMCC_NODE_BASE_T T1, CNTV.UMS_BUSI_TYPE_NODE T2
 WHERE (T2."NODE1" = T1."NODE_ONE")
   AND (T2."NODE2" = T1."NODE_TWO")
   AND (T2."NODE3" = T1."NODE_THREE")
   AND (T2."NODE4" = T1."NODE_FOUR")
   AND (T1."STATE" = '0')
   AND (T1."ITEM1_NAME" LIKE '%所有%')
   AND (T1."BUSINESS_DATE" BETWEEN '20131121' AND '20131127')
   AND (T2."TYPE_NAME" NOT LIKE '%直播%') AND
       (T2."TYPE_NAME" NOT LIKE '%免费%')
 GROUP BY T2."TYPE_NAME"
 ORDER BY T2."TYPE_NAME";
 
select * from table(dbms_xplan.display);
 执行计划:
 Plan hash value: 335643943
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |     1 |   371 |     5  (40)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                       |                     |     1 |   371 |     5  (40)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                          |                     |     1 |   371 |     4  (25)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE SINGLE            |                     |     1 |   314 |     0   (0)| 00:00:01 |    23 |    23 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| DW_CMCC_NODE_BASE_T |     1 |   314 |     0   (0)| 00:00:01 |    23 |    23 |
|*  5 |      INDEX RANGE SCAN                | INX_CM_NODE_DATE_GP |     1 |       |     0   (0)| 00:00:01 |    23 |    23 |
|*  6 |    TABLE ACCESS FULL                 | UMS_BUSI_TYPE_NODE  |   421 | 23997 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T2"."NODE1"="T1"."NODE_ONE" AND "T2"."NODE2"="T1"."NODE_TWO" AND "T2"."NODE3"="T1"."NODE_THREE" AND 
              "T2"."NODE4"="T1"."NODE_FOUR")
   4 - filter("T1"."ITEM1_NAME" LIKE '%所有%' AND "T1"."STATE"='0')
   5 - access("T1"."BUSINESS_DATE">='20131121' AND "T1"."BUSINESS_DATE"<='20131127')
   6 - filter("T2"."TYPE_NAME" NOT LIKE '%直播%' AND "T2"."TYPE_NAME" NOT LIKE '%免费%' AND "T2"."TYPE_NAME" IS NOT 
              NULL AND "T2"."TYPE_NAME" IS NOT NULL)
 
 
方法二:15秒
explain plan for  SELECT /*+ leading(t1) use_nl(t1,t2) */ T2."TYPE_NAME",
       SUM(T1."USERS_ACCESS"),
       SUM(T1."USERS_USE"),
       SUM(T1."USERS_USE_PAY"),
       SUM(T1."USE_ACCORD"),
       SUM(T1."ACCORD_COST"),
       SUM(((T1."USERS_USE" / 7) * 100))
  FROM CNTV.DW_CMCC_NODE_BASE_T T1, CNTV.UMS_BUSI_TYPE_NODE T2
 WHERE (T2."NODE1" = T1."NODE_ONE")
   AND (T2."NODE2" = T1."NODE_TWO")
   AND (T2."NODE3" = T1."NODE_THREE")
   AND (T2."NODE4" = T1."NODE_FOUR")
   AND (T1."STATE" = '0')
   AND (T1."ITEM1_NAME" LIKE '%所有%')
   AND (T1."BUSINESS_DATE" BETWEEN '20131121' AND '20131127')
   AND ((T2."TYPE_NAME" NOT LIKE '%直播%') AND
       (T2."TYPE_NAME" NOT LIKE '%免费%'))
 GROUP BY T2."TYPE_NAME"
 ORDER BY T2."TYPE_NAME";
 
 执行计划:
 Plan hash value: 2741559061
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |     1 |   371 |     4  (25)| 00:00:01 |       |       |
|   1 |  SORT GROUP BY                       |                     |     1 |   371 |     4  (25)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                       |                     |     1 |   371 |     3   (0)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE SINGLE            |                     |     1 |   314 |     0   (0)| 00:00:01 |    23 |    23 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| DW_CMCC_NODE_BASE_T |     1 |   314 |     0   (0)| 00:00:01 |    23 |    23 |
|*  5 |      INDEX RANGE SCAN                | INX_CM_NODE_DATE_GP |     1 |       |     0   (0)| 00:00:01 |    23 |    23 |
|*  6 |    TABLE ACCESS FULL                 | UMS_BUSI_TYPE_NODE  |     1 |    57 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("T1"."ITEM1_NAME" LIKE '%所有%' AND "T1"."STATE"='0')
   5 - access("T1"."BUSINESS_DATE">='20131121' AND "T1"."BUSINESS_DATE"<='20131127')
   6 - filter("T2"."TYPE_NAME" NOT LIKE '%直播%' AND "T2"."TYPE_NAME" NOT LIKE '%免费%' AND "T2"."TYPE_NAME" IS NOT 
              NULL AND "T2"."TYPE_NAME" IS NOT NULL AND "T2"."NODE1"="T1"."NODE_ONE" AND "T2"."NODE2"="T1"."NODE_TWO" AND 
              "T2"."NODE3"="T1"."NODE_THREE" AND "T2"."NODE4"="T1"."NODE_FOUR")
 
 


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:407149次
    • 积分:6527
    • 等级:
    • 排名:第3818名
    • 原创:228篇
    • 转载:275篇
    • 译文:24篇
    • 评论:7条
    最新评论