笛卡尔积和NEST LOOP产生的影响-阿里云开发者社区

笛卡尔积和NEST LOOP产生的影响

2010-04-15 770

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 原创转载请注明出处 SQL 性能分析,笛卡尔积和NEST LOOP SQL> SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TAB...

原创转载请注明出处

SQL 性能分析,笛卡尔积和NEST LOOP

SQL> SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

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

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" 10 - filter("CM2"."ACCEPT_DATE" 11 - filter("C"."RISK_LEVEL"='D')
12 - access("CM1"."APPLICANT_ID"="C"."CUSTOMER_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条数据。
此外这个语句在使用索引和访问方式都比较合理，所以要改善性能必须去掉笛卡尔积产生的大量结果集，谢谢！

文章标签：

索引