HINT的神奇

橙子和落落帮我优化的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")
 
 


阅读更多
个人分类: ORACLE TURNNING
想对作者说点什么? 我来说一句

js hint 提示 js hint 提示

2010年04月21日 161KB 下载

delphi 自制hint

2010年11月25日 166KB 下载

Oracle Hint的用法

2009年04月14日 35KB 下载

神奇的矩阵第二季

2017年02月15日 2.51MB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭