笛卡爾積和NEST LOOP產生的影響
原創 轉載請註明出處
SQL 效能分析,笛卡爾積和NEST LOOP
SQL> SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5tq3dm3w602k0, child number 0
-------------------------------------
select count(*) total
from (select distinct (c.customer_id),
c.risk_level risk,
c.real_name cusname,
c.gender gender,
to_char(c.birthday, 'yyyy-mm-dd') birthday,
c.certi_code certicode,
cm.policy_code policycode,
cm.discount_prem prem,
to_char(cm.accept_date, 'yyyy-mm-dd') accept_date
from t_customer c,
t_certi_type ct,
t_contract_master cm,
t_contract_product cp
where exists
(select *
from (select t.applicant_id customer_id
from (select cm1.applicant_id, cm1.accept_date
from t_contract_master cm1
where not exists
(select cm2.applicant_id
from t_contract_master cm2
where cm1.applicant_id =
cm2.applicant_id
and (cm2.accept_date <
to_date('2009-10-01',
'yyyy-mm-dd')))) t
where t.accept_date >=
to_date('2009-10-01', 'yyyy-mm-dd')
and t.accept_date <
to_date('2009-12-31', 'yyyy-mm-dd') + 1) temp
where temp.customer_id = c.customer_id)
and c.certi_type = ct.type_id
and (c.customer_id = cm.applicant_id or
c.customer_id = cp.insured_1)
and cm.policy_id = cp.policy_id
and c.risk_level = 'D'
and cm.accept_date > = to_date('2009-10-01', 'yyyy-mm-dd')
and cm.accept_date <= to_date('2009-12-31', 'yyyy-mm-dd') + 1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 907659101
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:21:51.83 | 606M| 409 |
| 2 | VIEW | | 1 | 1 | 5295 |00:21:51.85 | 606M| 409 |
| 3 | HASH UNIQUE | | 1 | 1 | 5295 |00:21:51.84 | 606M| 409 |
| 4 | NESTED LOOPS | | 1 | 1 | 6307 |01:51:27.91 | 606M| 409 |
| 5 | MERGE JOIN CARTESIAN | | 1 | 1 | 303M|00:20:12.96 | 15644 | 374 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 6 | NESTED LOOPS | | 1 | 1 | 5282 |00:00:02.51 | 15445 | 374 |
|* 7 | HASH JOIN ANTI | | 1 | 1 | 5282 |00:00:00.08 | 4879 | 0 |
| 8 | TABLE ACCESS BY INDEX ROWID| T_CONTRACT_MASTER | 1 | 3379 | 5631 |00:00:00.05 | 1038 | 0 |
|* 9 | INDEX RANGE SCAN | IDX_CONTRACT_MASTER__ACP_DATE | 1 | 3379 | 5631 |00:00:00.01 | 17 | 0 |
|* 10 | TABLE ACCESS FULL | T_CONTRACT_MASTER | 1 | 43806 | 32177 |00:00:00.06 | 3841 | 0 |
|* 11 | TABLE ACCESS BY INDEX ROWID | T_CUSTOMER | 5282 | 1 | 5282 |00:00:00.66 | 10566 | 374 |
|* 12 | INDEX UNIQUE SCAN | PK_T_CUSTOMER | 5282 | 1 | 5282 |00:00:00.17 | 5284 | 26 |
| 13 | BUFFER SORT | | 5282 | 57406 | 303M|00:10:06.48 | 199 | 0 |
| 14 | INDEX FAST FULL SCAN | UNI_CONTRACT_PRODUCT__PRONUM | 1 | 57406 | 57406 |00:00:00.06 | 199 | 0 |
|* 15 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_MASTER | 303M| 1 | 6307 |01:29:21.72 | 606M| 35 |
|* 16 | INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER | 303M| 1 | 303M|00:39:35.06 | 303M| 0 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("CM1"."APPLICANT_ID"="CM2"."APPLICANT_ID")
9 - access("CM1"."ACCEPT_DATE">=TO_DATE('2009-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"CM1"."ACCEPT_DATE"
12 - access("CM1"."APPLICANT_ID"="C"."CUSTOMER_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15 - filter(("CM"."ACCEPT_DATE">=TO_DATE('2009-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"CM"."ACCEPT_DATE"<=TO_DATE('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
("C"."CUSTOMER_ID"="CM"."APPLICANT_ID" OR
"C"."CUSTOMER_ID"="CP"."INSURED_1")))
16 - access("CM"."POLICY_ID"="CP"."POLICY_ID")
49 rows selected.
語句在15步的時候耗用的大量的時間，應為NEST LOOP會去匹配先前結果中的每一行，而先前的得出行數是303M行，
這個結果就是由於MERGE JOIN CARTESIAN產生了笛卡爾積。
連線條件中出現了笛卡爾積，原因是連線條件不足，t_customer 沒有和所有的表進行關聯，
形如
select * from test,test2的語句也就是笛卡爾積，A表中的每一行會和B表中的所有行進行一次匹配，
如果TEST有3條資料，TEST2有3條資料就會形成3*3 9條資料。
此外這個語句在使用索引和訪問方式都比較合理，所以要改善效能必須去掉笛卡爾積產生的大量結果集，謝謝！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-659826/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- java 笛卡爾積（迴圈）Java
- SparkSQL中產生笛卡爾積的幾種典型場景以及處理策略SparkSQL
- 笛卡爾積與全連線
- 笛卡爾乘積的javascript版實現和應用JavaScript
- 笛卡爾積的應用——商品 SKU 計算
- Oracle的表連線方法（四）笛卡爾積Oracle
- SQL 語句調優_減少或者避免笛卡爾乘積的發生SQL
- [JavaScript] 求解任意n個集合的笛卡爾積JavaScript
- 二維陣列笛卡爾積js實現陣列JS
- 笛卡爾樹
- Python如何從列表中獲取笛卡爾積Python
- 基環樹和笛卡爾樹
- 【TUNE_ORACLE】列出走了笛卡爾積的SQL參考OracleSQL
- 成績錄入SQL語句 笛卡爾積 LEFT JOINSQL
- php計算多個集合的笛卡爾積例項詳解PHP
- 速看：大資料能在這幾個行業產生積極的影響！大資料行業
- 二叉查詢樹和笛卡爾樹
- 統計資訊不準確導致執行計劃走了笛卡爾積
- mysql的DDL操作對業務產生影響測試MySql
- 大資料對法律行業產生的影響大資料行業
- GSMA：非洲人工智慧：產生影響的用例人工智慧
- 理解笛卡爾積在資料庫查詢中的實際應用與最佳化資料庫
- 笛卡爾座標張量簡介7
- 形象化理解笛卡爾座標系和極座標系
- 比特幣ETF的積極影響比特幣
- 笛卡爾實驗室全面遷移至亞馬遜雲科技亞馬遜
- Facebook Gaming調查了新冠疫情對工作產生的影響GAM
- 測試部的技術如何在公司內外產生影響力
- ERP系統對企業哪些方面產生影響
- 修改欄位長度應用會影響到生產效能
- hash join\nest loop join\sort merge join的實驗OOP
- NEST LOOP改為HASH JOIN 效能提高6倍OOP
- 遊戲中的歷史觀念，會對玩家產生怎樣的影響？遊戲
- 科學的演變：從笛卡爾到生成式人工智慧人工智慧
- 笛卡爾：語言是思想唯一確定標誌？
- 在業務領域中產生重大影響的5種人工智慧技術人工智慧
- 人工智慧對金融從業人員會產生哪些影響？人工智慧
- InterruptedException異常會對併發程式設計產生哪些影響？Exception程式設計