本帖最后由 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