oracle 笛卡尔联结

用scott/tiger登录。

笛卡尔连接是指在sql语句中没有写出表连接的条件或者表的连接条件不能约束两个表的连接，优化器把第一个表的每一条记录和第二个表的所有记录相连接。如果第一个表的记录数为m, 第二个表的记录数为n,则会产生m*n条记录数。

SQL> set autotrace traceonly
SQL> alter system flush buffer_cache;

系统已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select empno, ename, dname, loc from dept, emp;

已选择56行。


执行计划
----------------------------------------------------------
Plan hash value: 2034389985

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |  1568 |     9   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |  1568 |     9   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    72 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |   140 |     6   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |   140 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


统计信息
----------------------------------------------------------
       1557  recursive calls
          0  db block gets
        312  consistent gets
         43  physical reads
          0  redo size
       1876  bytes sent via SQL*Net to client
        418  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
         39  sorts (memory)
          0  sorts (disk)
         56  rows processed

SQL>