不是笛卡尔积的问题

最新推荐文章于 2022-09-01 18:07:24 发布

指尖上的程序

最新推荐文章于 2022-09-01 18:07:24 发布

阅读量639

点赞数

分类专栏： ORACLE TURNNING

本文链接：https://blog.csdn.net/xiaoxing1521025/article/details/17264307

版权

ORACLE TURNNING 专栏收录该内容

53 篇文章 2 订阅

订阅专栏

优化前：

explain plan for merge into dw_cmcc_split_detail_t a
using (select /*+ opt_param('_optimizer_mjc_enabled', 'false') */t.id, t.cp_id, t.cp_name, t.price
from inf_cmcc_split_t t, dw_cmcc_split_detail_t t1
where t.id=t1.id and t1.business_date = '20130101' and t.business_date='20130101'
and t1.state = '0'
group by t.id, t.cp_id, t.cp_name, t.price) ng
on (ng.id = a.id)
when matched then
update
set a.cp_id = ng.cp_id, a.cp_name = ng.cp_name, a.value3 = ng.price
where a.business_date = '20130101';

select * from table(dbms_xplan.display);

Plan hash value: 4244061039

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 384 | 471K (2)| 01:34:18 | | |
| 1 | MERGE | DW_CMCC_SPLIT_DETAIL_T | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | SORT GROUP BY | | 1 | 424 | 471K (2)| 01:34:18 | | |
|* 4 | HASH JOIN | | 1 | 424 | 471K (2)| 01:34:18 | | |
| 5 | NESTED LOOPS | | 1 | 323 | 471K (2)| 01:34:18 | | |
| 6 | PARTITION RANGE SINGLE | | 1 | 27 | 0 (0)| 00:00:01 | 13 | 13 |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| DW_CMCC_SPLIT_DETAIL_T | 1 | 27 | 0 (0)| 00:00:01 | 13 | 13 |
|* 8 | INDEX RANGE SCAN | INX_CM_SPLIT_DATE_CNAME_GP | 1 | | 0 (0)| 00:00:01 | 13 | 13 |
| 9 | PARTITION RANGE ALL | | 42M| 11G| 471K (2)| 01:34:18 | 1 | 37 |
| 10 | TABLE ACCESS FULL | DW_CMCC_SPLIT_DETAIL_T | 42M| 11G| 471K (2)| 01:34:18 | 1 | 37 |
|* 11 | TABLE ACCESS FULL | INF_CMCC_SPLIT_T | 1 | 101 | 19 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("T"."ID"="A"."ID" AND "T"."ID"="T1"."ID")
7 - filter(TO_NUMBER("T1"."STATE")=0)
8 - access("T1"."BUSINESS_DATE"='20130101')
11 - filter("T"."BUSINESS_DATE"='20130101')

Note
-----
- dynamic sampling used for this statement (level=2)

优化一：

explain plan for MERGE INTO (SELECT *

FROM dw_cmcc_split_detail_t
WHERE business_date = '20130101'
AND state = 0) a
USING (SELECT /*+ opt_param('_optimizer_mjc_enabled', 'false') */ ----------这里的去掉笛卡尔用不到
t.id, t.cp_id, t.cp_name, t.price,t.business_date
FROM inf_cmcc_split_t t
WHERE t.business_date = '20130101'
GROUP BY t.id, t.cp_id, t.cp_name, t.price,t.business_date) ng
ON (ng.id = a.id and a.business_date=ng.business_date)
WHEN MATCHED THEN
UPDATE
SET a.cp_id = ng.cp_id, a.cp_name = ng.cp_name, a.value3 = ng.price;

select * from table(dbms_xplan.display);

Plan hash value: 752456823

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 131 | 20 (5)| 00:00:01 | | |
| 1 | MERGE | DW_CMCC_SPLIT_DETAIL_T | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 1546 | 20 (5)| 00:00:01 | | |
| 5 | VIEW | | 1 | 101 | 20 (5)| 00:00:01 | | |
| 6 | SORT GROUP BY | | 1 | 101 | 20 (5)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | INF_CMCC_SPLIT_T | 1 | 101 | 19 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE SINGLE | | 1 | | 0 (0)| 00:00:01 | 13 | 13 |
|* 9 | INDEX RANGE SCAN | INX_CM_SPLIT_DATE_GP | 1 | | 0 (0)| 00:00:01 | 13 | 13 |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| DW_CMCC_SPLIT_DETAIL_T | 1 | 1445 | 0 (0)| 00:00:01 | 13 | 13 |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("T"."BUSINESS_DATE"='20130101')
9 - access("BUSINESS_DATE"='20130101')
10 - filter(TO_NUMBER("STATE")=0 AND "NG"."ID"="DW_CMCC_SPLIT_DETAIL_T"."ID")

Note
-----

- dynamic sampling used for this statement (level=2)

优化二：

explain plan for merge into dw_cmcc_split_detail_t a
using (select /*+ opt_param('_optimizer_mjc_enabled', 'false') */ -----------------这里的去掉笛卡尔用不到
t.id, t.cp_id, t.cp_name, t.price,t1.business_date
from inf_cmcc_split_t t, dw_cmcc_split_detail_t t1
where t1.business_date = '20130101' and t.business_date='20130101'
and t1.state = 0
group by t.id, t.cp_id, t.cp_name, t.price,t1.business_date) ng
on (ng.id = a.id and a.business_date=ng.business_date)
when matched then
update
set a.cp_id = ng.cp_id, a.cp_name = ng.cp_name, a.value3 = ng.price
where a.business_date = '20130101';

select * from table(dbms_xplan.display);

Plan hash value: 1868255070

---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 384 | 20 (5)| 00:00:01 | | |
| 1 | MERGE | DW_CMCC_SPLIT_DETAIL_T | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | SORT GROUP BY | | 1 | 1560 | 20 (5)| 00:00:01 | | |
| 4 | NESTED LOOPS | | | | | | | |
| 5 | NESTED LOOPS | | 1 | 1560 | 19 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 115 | 19 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE | | 1 | 14 | 0 (0)| 00:00:01 | 13 | 13 |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| DW_CMCC_SPLIT_DETAIL_T | 1 | 14 | 0 (0)| 00:00:01 | 13 | 13 |
|* 9 | INDEX RANGE SCAN | INX_CM_SPLIT_DATE_CNAME_GP | 1 | | 0 (0)| 00:00:01 | 13 | 13 |
|* 10 | TABLE ACCESS FULL | INF_CMCC_SPLIT_T | 1 | 101 | 19 (0)| 00:00:01 | | |
| 11 | PARTITION RANGE SINGLE | | 1 | | 0 (0)| 00:00:01 | 13 | 13 |
|* 12 | INDEX RANGE SCAN | INX_CM_SPLIT_DATE_GP | 1 | | 0 (0)| 00:00:01 | 13 | 13 |
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | DW_CMCC_SPLIT_DETAIL_T | 1 | 1445 | 0 (0)| 00:00:01 | 13 | 13 |
---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter(TO_NUMBER("T1"."STATE")=0)
9 - access("T1"."BUSINESS_DATE"='20130101')
10 - filter("T"."BUSINESS_DATE"='20130101')
12 - access("A"."BUSINESS_DATE"='20130101')
13 - filter("T"."ID"="A"."ID")

Note
-----
- dynamic sampling used for this statement (level=2)