对三个表进行联合查询，其中ref_glbal和ref_al数据量都不大，有五百多条数据。TABLE1有75万数据。
将三个表进行内连接，产生了如下所示的执行计划。非常不正常。
SQL> explain plan for
2 select count(1) from SCHEMA1.TABLE1 inner join ref_glbal rg on trim(TABLE1.ND_AIR) = rg.air_code
3 inner join ref_al ra on rg.glbal_seq = ra.glbal_seq
4 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3439537040
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byt
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | |
| 3 | NESTED LOOPS | | 1 |
| 4 | MERGE JOIN CARTESIAN | | 1 |
| 5 | INDEX FULL SCAN | IDX_ref_al_glbal | 1 |
| 6 | BUFFER SORT | | 757K| 44
| 7 | INDEX FAST FULL SCAN | TABLE1I2 | 757K| 44
|* 8 | INDEX UNIQUE SCAN | PK_ref_glbal | 1 |
|* 9 | TABLE ACCESS BY INDEX ROWID| ref_glbal | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
8 - access("RG"."glbal_SEQ"="RA"."glbal_SEQ")
9 - filter("RG"."AIR_CODE"=TRIM("TABLE1"."ND_AIR"))
22 rows selected
ra、rg、TABLE1做内连接，并不缺少连接条件，为什么产生了“MERGE JOIN CARTESIAN”呢？
查看了数据字典，发现table1表nd_air列的统计信息不对。本来是三位的字段，统计的最大值和最小值居然都是10位左右。
dba_tab_columns部分列如下：
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY LAST_ANALYZED SAMPLE_SIZE
SCHEMA1 TABLE1 ND_AIR CHAR 50 3030312020 3938382020 6.53115E-07 2012-7-8 7:53:40 6556
查看该表的最大值和最小值：
SQL> select max(nd_Air),min(nd_air) from SCHEMA1.TABLE1;
MAX(ND_AIR) MIN(ND_AIR)
----------- -----------
988 001
尝试重新收集统计信息，无权限：
SQL> exec dbms_stats.gather_table_stats(ownname => 'SCHEMA1',tabname => 'TABLE1',cascade => true);
begin dbms_stats.gather_table_stats(ownname => 'SCHEMA1',tabname => 'TABLE1',cascade => true); end;
ORA-20000: Unable to analyze TABLE "SCHEMA1"."TABLE1", insufficient privileges or does not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 23818
ORA-06512: 在 "SYS.DBMS_STATS", line 23880
ORA-06512: 在 line 1
以上是第一天的记录，由于没有仔细看执行计划，只看到merge join castesian就觉得有问题，而且非常自然地想到是最大的表table1的问题，研究不够仔细。
今天在组长的提示下，看到了工作中的不仔细。
上面提到过，ref_glbal和ref_al数据量都有五百多条数据，但在最初的执行计划中却都认为只有一条数据。由于两个表中的数据都是新导入的，因此出现了问题。
再来说merge join castesian，由于对笛卡尔积的偏见，直接认为这个执行计划不对。察看了一下网上的文档[http://blog.csdn.net/heqiyu34/article/details/7543778]
--------------------------------------------------------------------------------
Plan hash value: 3439537040
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byt
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | |
| 3 | NESTED LOOPS | | 1 |
| 4 | MERGE JOIN CARTESIAN | | 1 |
| 5 | INDEX FULL SCAN | IDX_ref_al_glbal | 1 |
| 6 | BUFFER SORT | | 757K| 44
| 7 | INDEX FAST FULL SCAN | TABLE1I2 | 757K| 44
|* 8 | INDEX UNIQUE SCAN | PK_ref_glbal | 1 |
|* 9 | TABLE ACCESS BY INDEX ROWID| ref_glbal | 1 |
--------------------------------------------------------------------------------
通过问题的复现，确认了以上执行计划就是由于ref_glbal和ref_al的统计信息不准确造成的。
但是网上大部分关于sort join cartesian的解释都是说两个非常小的表进行此连接比较合适。
而在我的例子中，是一个大表和一个小表连接。
ref_glbal作为一个中间连接被最后执行了，这个问题最匪夷所思的地方是：为什么一个757K数据量的表，oracle会认为带有排序操作更加高效呢？
SQL> explain plan for select /*+use_nl(ra rg)*/*
2 from ref_al ra
3 inner join ref_glbal rg on rg.glbal_seq = ra.glbal_seq;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1473932376
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 138K| 574 (1)| 00:00:07 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 555 | 138K| 574 (1)| 00:00:07 |
| 3 | TABLE ACCESS FULL | REF_al | 555 | 77145 | 18 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_REF_GLBal | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| REF_GLBal | 1 | 117 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("RG"."GLBal_SEQ"="RA"."GLBal_SEQ")
已选择17行。
SQL> explain plan for select *
2 from ref_al ra
3 inner join ref_glbal rg on rg.glbal_seq = ra.glbal_seq;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 360641786
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 555 | 138K| 32 (4)| 00:00:01 |
| 1 | MERGE JOIN | | 555 | 138K| 32 (4)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| REF_GLBal | 569 | 66573 | 13 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_REF_GLBal | 569 | | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 555 | 77145 | 19 (6)| 00:00:01 |
| 5 | TABLE ACCESS FULL | REF_al | 555 | 77145 | 18 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("RG"."GLBal_SEQ"="RA"."GLBal_SEQ")
filter("RG"."GLBal_SEQ"="RA"."GLBal_SEQ")
已选择18行。
===================================
错误的执行计划：
SQL> explain plan for select count(1)
2 from ref_al ra
3 inner join ref_glbal rg on rg.glbal_seq = ra.glbal_seq
4 inner join SCHEMA1.TABLE1 on trim(TABLE1.ND_AIR) = rg.air_code;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3439537040
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |36 | 929 (2)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 |36 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 |36 | 929 (2)| 00:00:12 |
| 4 | MERGE JOIN CARTESIAN | | 1 |19 | 929 (2)| 00:00:12 |
| 5 | INDEX FULL SCAN | IDX_REF_al_GLBal | 1 |13 | 1 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 757K| 4437K| 928 (2)| 00:00:12 |
| 7 | INDEX FAST FULL SCAN | TABLE1I2 | 757K| 4437K| 928 (2)| 00:00:12 |
|* 8 | INDEX UNIQUE SCAN | PK_REF_GLBal | 1 | | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| REF_GLBal | 1 |17 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("RG"."GLBal_SEQ"="RA"."GLBal_SEQ")
9 - filter("RG"."AIR_CODE"=TRIM("TABLE1"."ND_AIR"))
已选择22行。
=================================
使用hint禁用了merge join castrsian
SQL> explain plan for select /*+ OPT_PARAM('_optimizer_mjc_enabled','false')*/count(1)
2 from ref_al ra
3 inner join ref_glbal rg on rg.glbal_seq = ra.glbal_seq
4 inner join SCHEMA1.TABLE1 on trim(TABLE1.ND_AIR) = rg.air_code;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 13807094
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |36 | 929 (2)| 00:00:12 |
| 1 | SORT AGGREGATE | | 1 |36 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 |36 | 929 (2)| 00:00:12 |
| 4 | NESTED LOOPS | | 1 |19 | 929 (2)| 00:00:12 |
| 5 | INDEX FULL SCAN | IDX_REF_al_GLBal | 1 |13 | 1 (0)| 00:00:01 |
| 6 | INDEX FAST FULL SCAN | TABLE1I2 | 757K| 4437K| 928 (2)| 00:00:12 |
|* 7 | INDEX UNIQUE SCAN | PK_REF_GLBal | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| REF_GLBal | 1 |17 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
7 - access("RG"."GLBal_SEQ"="RA"."GLBal_SEQ")
8 - filter("RG"."AIR_CODE"=TRIM("TABLE1"."ND_AIR"))
已选择21行。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26451536/viewspace-755442/ ，如需转载，请注明出处，否则将追究法律责任。
