SQL语句开并行与不开并行在执行计划中的体现
ENODS.WS_JIAOYI 数据量小于ENODS.WS_OPER_LOG 数据量,且ENODS.WS_JIAOYI 有唯一索引
SQL>set autot on;
SQL> SELECT COUNT(distinct A.MJ_LOGIN_ID) AS SEND_GOODS_MEMBER_CNT
FROM ENODS.WS_JIAOYI A, ENODS.WS_OPER_LOG B
WHERE A.ID = B.TRADE_ID
AND B.ACTION_TYPE IN
('front_seller_send_all_goods', 'front_sellerAnnounceSendGoods')
AND TRUNC(B.GMT_CREATE) = trunc(sysdate)-2;
SEND_GOODS_MEMBER_CNT
---------------------
833
1 row selected.
Elapsed: 00:00:12.64
Execution Plan
----------------------------------------------------------
Plan hash value: 1358353880
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 71 | 17371 (1)| 00:05:13 |
| 1 | SORT GROUP BY | | 1 | 71 | | |
| 2 | NESTED LOOPS | | 190 | 13490 | 17371 (1)| 00:05:13 |
|* 3 | TABLE ACCESS FULL | WS_OPER_LOG | 190 | 7600 | 17181 (1)| 00:05:10 |
| 4 | TABLE ACCESS BY INDEX ROWID| WS_JIAOYI | 1 | 31 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_WS_JIAOYI | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("B"."ACTION_TYPE"='front_sellerAnnounceSendGoods' OR
"B"."ACTION_TYPE"='front_seller_send_all_goods') AND
TRUNC(INTERNAL_FUNCTION("B"."GMT_CREATE"))=TRUNC(SYSDATE@!)-2)
5 - acc