HINT的神奇

来源：互联网发布：淘宝客服后台操作视频编辑：程序博客网时间：2024/05/17 06:06

橙子和落落帮我优化的SQL，使用ＨＩＮＴ

分区大表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")