HINT的神奇

原创 2013年12月02日 17:57:16

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


相关文章推荐

带图标的Hint窗口

  • 2006年02月23日 09:05
  • 995B
  • 下载

android:hint属性对TextView(或者EditText)的影响--源码分析

textView.setText("哈哈"); textView.setHint("哈哈哈哈哈哈"); hint属性对TextView(或者EditText)的影响,直接看下图: 使用上下两个Tex...

Oracle 中HINT的使用

  • 2014年02月13日 14:33
  • 8.88MB
  • 下载

delphi自定义的label_hint单元

  • 2012年11月08日 11:01
  • 172KB
  • 下载

常见Oracle HINT的用法

在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. /*+ALL_ROWS*/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐...
  • aiaix
  • aiaix
  • 2011年10月05日 10:46
  • 235

Oracle Hint的用法

  • 2009年04月14日 23:20
  • 35KB
  • 下载

oracle_hint

  • 2014年02月14日 10:54
  • 66KB
  • 下载

TextInputLayout 设置hint和EditText之间的距离的一种方案

在EditText设置自定义背景的时候,很明显的出现 hint和EditText之间的距离一点都没有,确实有点不太美观试验一些方法不行。。。希望有眼前一亮的方法,tell me现在个人是从drawa...

EditText聚焦时hint消失的简单代码

  • 2015年09月22日 14:59
  • 439B
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HINT的神奇
举报原因:
原因补充:

(最多只允许输入30个字)