有条件的两个表关联查询为什么会出现 MERGE JOIN CARTESIAN操作
如下有条件的两个表关联查询为什么会出现 MERGE JOIN CARTESIAN操作呢？ 百思不得其解，请各位前辈指点SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 16 10:01:17 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SET AUTOT TRACEO EXPLAIN
SQL> SELECT
a.PRINT_LOG_ID,
a.ACCT_ID,
a.PRINT_NAME,
a.USER_ID,
a.EPARCHY_CODE,
a.SERIAL_NUMBER,
a.PRINT_FEE
FROM
TF_B_INVOICE_PRINTLOG a
TF_B_INVOICE_PRINTINFO b
WHERE
a.PRINT_LOG_ID=7614010687045719
and b.PRINT_LOG_ID = 7614010687045719
Execution Plan
----------------------------------------------------------
Plan hash value: 1025713493
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 71 | 19 (0)| 00:00:01 | | |
| 1 | MERGE JOIN CARTESIAN | | 1 | 71 | 19 (0)| 00:00:01 | | |
| 2 | PARTITION RANGE ALL | | 1 | 10 | 17 (0)| 00:00:01 | 1 | 12 |
|* 3 | TABLE ACCESS FULL | TF_B_INVOICE_PRINTINFO | 1 | 10 | 17 (0)| 00:00:01 | 1 | 12 |
| 4 | BUFFER SORT | | 1 | 61 | 2 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TF_B_INVOICE_PRINTLOG | 1 | 61 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 6 | INDEX RANGE SCAN | PK_TF_B_INVOICE_PRINTLOG | 1 | | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."PRINT_LOG_ID"=7614010687045719)
6 - access("A"."PRINT_LOG_ID"=7614010687045719)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
53 consistent gets
0 physical reads
0 redo size
1019 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
FROM
TF_B_INVOICE_PRINTLOG a
TF_B_INVOICE_PRINTINFO b
WHERE
a.PRINT_LOG_ID=7614010687045719
and b.PRINT_LOG_ID = 7614010687045719
你认为 存在隐式的 a.PRINT_LOG_ID=b.PRINT_LOG_ID 对吗？ Maclean Liu(刘相兵 发表于 2014-10-16 13:04 static/image/common/back.gif
FROM
TF_B_INVOICE_PRINTLOG a
TF_B_INVOICE_PRINTINFO b
是的，刘大，我认为这样的条件是存在隐式的 a.PRINT_LOG_ID=b.PRINT_LOG_ID
原本开发人员提的SQL 是这样的
SELECT distinct a.PRINT_LOG_ID,
a.ACCT_ID,
a.PRINT_NAME,
a.USER_ID,
a.EPARCHY_CODE,
a.SERIAL_NUMBER,
a.PRINT_FEE,
a.TEMPLET_TYPE,
a.TEMPLET_ID,
TO_CHAR(a.PRINT_TIME,
'YYYYMMDDHH24MISS')||'000' PRINT_TIME,
a.PRINT_STAFF_ID,
a.PRINT_DEPART_ID,
a.PRINT_CITY_CODE,
a.PRINT_EPARCHY_CODE,
a.INVOICE_CODE,
a.INVOICE_NO,
a.TAX_RGSTR_NO,
a.TAX_SCRTY_CODE,
a.RECYCLE_TAG,
a.TICKET_STATE_CODE
FROM
TF_B_INVOICE_PRINTLOG a,
TF_B_INVOICE_PRINTINFO b
WHERE
a.PRINT_LOG_ID=b.PRINT_LOG_ID
and b.PRINT_LOG_ID = :VINPUTSTR
AND a.RECYCLE_TAG =:VRECYCLE_TAG
这样的查询计划 也是一样的。
本帖最后由 lghwf 于 2014-10-16 14:48 编辑
又发现另外一条查询计划中 也有MERGE JOIN CARTESIAN，SQL 除了对分区表 实现分区裁剪，其他没发现啥问题，可为啥也执行MERGE JOIN CARTESIAN操作呢，实在不理解，请刘大及各位高手不吝赐教
SQL> set autotrace traceo
SQL> SELECT
2 B.USER_ID,
B.SERIAL_NUMBER,
B.NET_TYPE_CODE,
A.RELATION_TYPE_CODE
FROM
TF_F_USER_MEMBER A,
TF_F_USER B
WHERE
A.USER_ID = B.USER_ID
AND SYSDATE BETWEEN A.START_DATE AND A.END_DATE
AND A.USER_ID = 1
AND A.RELATION_TYPE_CODE in ('4400','8800') ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1933860866
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 23 (0)| 00:00:01 | | |
| 1 | MERGE JOIN CARTESIAN | | 1 | 56 | 23 (0)| 00:00:01 | | |
| 2 | PARTITION RANGE ALL | | 1 | 30 | 12 (0)| 00:00:01 | 1 | 10 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID | TF_F_USER_MEMBER | 1 | 30 | 12 (0)| 00:00:01 | 1 | 10 |
|* 4 | INDEX RANGE SCAN | PK_TF_F_USER_MEMBER | 2 | | 11 (0)| 00:00:01 | 1 | 10 |
| 5 | BUFFER SORT | | 1 | 26 | 11 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 1 | 26 | 11 (0)| 00:00:01 | 1 | 10 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TF_F_USER | 1 | 26 | 11 (0)| 00:00:01 | 1 | 10 |
|* 8 | INDEX RANGE SCAN | PK_TF_F_USER | 1 | | 10 (0)| 00:00:01 | 1 | 10 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("A"."RELATION_TYPE_CODE"='4400' OR "A"."RELATION_TYPE_CODE"='8800') AND "A"."END_DATE">=SYSDATE@!)
4 - access("A"."USER_ID"=1 AND "A"."START_DATE"<=SYSDATE@!)
filter("A"."START_DATE"<=SYSDATE@!)
8 - access("B"."USER_ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
565 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
页:
[1]