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")
 
 


bzoj1416&1498: [NOI2006]神奇的口袋

传送门 傻逼题。 直接计算方案数,除上总方案数就可以了。 注意要用高精度。 为啥oj上有两道题?#include #include #include #include #include #de...
  • zhouyuyang233
  • zhouyuyang233
  • 2017年04月29日 15:42
  • 230

一段神奇的代码

做一个优雅的程序员,好像是很多程序员的一大目标,何为优雅,如何优雅?下面是一段神奇的代码,什么编码风格,什么编程规范,统统丢到一边吧,化腐朽为神奇才是更加难能可贵的! #define M 0...
  • u011836061
  • u011836061
  • 2015年06月04日 12:55
  • 1093

百练2755 神奇的口袋 【深搜】or【动规】or【普通递归】or【递推】

总Time Limit:  10000ms  Memory Limit:  65536kB 有一个神奇的口袋,总的容积是40,用这个口袋可以变出一些物品,这些物品的总体积必须是...
  • u012846486
  • u012846486
  • 2014年07月19日 18:39
  • 1179

记一次问题解决历程——脚本的神奇功能

今天一个测试的同事问我,你对adb熟悉吗?我说简单的会。然后说adb reboot(手机重启指令)知道吧?我说知道的。然后她的问题是:能不能一直让手机执行重启命令:我想了下,用脚本应该是可以的。之前接...
  • Simon_Crystin
  • Simon_Crystin
  • 2017年04月01日 20:42
  • 603

noi2006神奇口袋

神奇的口袋,考数学甚于考算法,【考虑无自由取球情况,那么,设A(X,Y)为第x次,取出颜色为Y的球,P{A(1,Y1),A(2,Y2)…}=P{A(1,Y1),A(2,Y1),A(3,Y1)}因为首先...
  • huyuncong
  • huyuncong
  • 2012年02月15日 18:51
  • 1633

NOI 2006 - 神奇的口袋 Bag

题目描述:输入:输出:(样例略)今天有同学问到这道题,我记得很久以前做这道题时用很优美的方法证明了一下,但今天仔细一想,却又纠结了(当时可能是伪证)。然后,今天花了半个上午+一个下午+半个晚上在这道题...
  • gy_jk
  • gy_jk
  • 2010年06月10日 20:58
  • 1935

NOIP2015 神奇的幻方 解题报告(水题模拟)

在线评测: http://codevs.cn/problem/4510/ 整体思路: 枚举一的位置,然后模拟就行了 失误之处: 数据<39,然后,我就把记录上一个点的数组...
  • qq_35772697
  • qq_35772697
  • 2016年08月27日 13:43
  • 966

Poj 2755:神奇的口袋

总时间限制: 10000ms 内存限制: 65536kB 描述有一个神奇的口袋,总的容积是40,用这个口袋可以变出一些物品,这些物品的总体积必须是40。John现在有n个想要得到的物品,每个...
  • qq_34125999
  • qq_34125999
  • 2016年05月19日 21:34
  • 560

神奇的fans

#include #include //用于qsort整数排序的比较函数 int IntCmp(const void *a, const void *b); int main(void) { in...
  • baidu_23955875
  • baidu_23955875
  • 2014年12月08日 21:23
  • 317

一行神奇的 javascript 代码

(!(~+[])+{})[--[~+""][+[]]*[~+[]] + ~~!+[]]+({}+[])[[~!+[]]*~+[]] 运行这段代码,结果有点意外。 其实这主要是运用到了js的类型转换...
  • miffy2016
  • miffy2016
  • 2017年04月13日 16:02
  • 331
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HINT的神奇
举报原因:
原因补充:

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