连接取值

最新推荐文章于 2022-08-31 14:51:33 发布

cujiaowu9180

最新推荐文章于 2022-08-31 14:51:33 发布

阅读量90

点赞数

源于pub一个帖子：

http://www.itpub.net/thread-1209587-1-1.html

SQL> select object_id,
  2       (select /*+USE_HASH(b c) FULL(C)*/ b.object_name
  3          from test1 b, test1 c
  4          where a.object_id = b.object_id
  5          and b.object_id = c.object_id
  6          and c.owner is not null
  7          and b.owner is not null)
  8 from test1 a
  9 where a.owner is not null;

已选择13101行。

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

--------------------------------------------------------------------------------
----------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)|
Time    |

--------------------------------------------------------------------------------
----------

| 0 | SELECT STATEMENT          |          | 13101 | 140K| 41 (0)|
00:00:01 |

|*  1 |  HASH JOIN                |          |    1 | 40 | 44 (3)|
00:00:01 |

|*  2 | TABLE ACCESS BY INDEX ROWID| TEST1    |    1 | 29 |    2 (0)|
00:00:01 |

|*  3 | INDEX RANGE SCAN       | I_TEST1_1 |    1 |    |    1 (0)|
00:00:01 |

|*  4 | TABLE ACCESS FULL       | TEST1    |    1 | 11 | 41 (0)|
00:00:01 |

|*  5 |  TABLE ACCESS FULL          | TEST1    | 13101 | 140K| 41 (0)|
00:00:01 |

--------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("B"."OBJECT_ID"="C"."OBJECT_ID")
2 - filter("B"."OWNER" IS NOT NULL)
3 - access("B"."OBJECT_ID"=:B1)
4 - filter("C"."OBJECT_ID"=:B1 AND "C"."OWNER" IS NOT NULL)
5 - filter("A"."OWNER" IS NOT NULL)

统计信息
----------------------------------------------------------
      1  recursive calls
      0  db block gets
2267537  consistent gets
      0  physical reads
      0  redo size
   411568  bytes sent via SQL*Net to client
   10003  bytes received via SQL*Net from client
      875  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   13101  rows processed

SQL> ed
已写入 file afiedt.buf

  1  select object_id,
  2       (select  b.object_name
  3          from test1 b, test1 c
  4          where a.object_id = b.object_id
  5          and b.object_id = c.object_id
  6          and c.owner is not null
  7          and b.owner is not null)
  8 from test1 a
  9* where a.owner is not null
SQL> /

已选择13101行。

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

--------------------------------------------------------------------------------
-----------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)|
Time    |

--------------------------------------------------------------------------------
-----------

| 0 | SELECT STATEMENT             |          | 13101 | 140K| 41 (0)|
00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID  | TEST1    |    1 | 11 |    2 (0)|
00:00:01 |

| 2 | NESTED LOOPS             |          |    1 | 40 |    4 (0)|
00:00:01 |

|*  3 | TABLE ACCESS BY INDEX ROWID| TEST1    |    1 | 29 |    2 (0)|
00:00:01 |

|*  4 |    INDEX RANGE SCAN       | I_TEST1_1 |    1 |    |    1 (0)|
00:00:01 |

|*  5 | INDEX RANGE SCAN          | I_TEST1_1 |    1 |    |    1 (0)|
00:00:01 |

|*  6 |  TABLE ACCESS FULL          | TEST1    | 13101 | 140K| 41 (0)|
00:00:01 |

--------------------------------------------------------------------------------
-----------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("C"."OWNER" IS NOT NULL)
3 - filter("B"."OWNER" IS NOT NULL)
4 - access("B"."OBJECT_ID"=:B1)
5 - access("C"."OBJECT_ID"=:B1)
   filter("B"."OBJECT_ID"="C"."OBJECT_ID")
6 - filter("A"."OWNER" IS NOT NULL)

统计信息
----------------------------------------------------------
      1  recursive calls
      0  db block gets
  55245  consistent gets
      0  physical reads
      0  redo size
   411544  bytes sent via SQL*Net to client
   10003  bytes received via SQL*Net from client
      875  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   13101  rows processed

才一万多数据，还没将另一个也弄成full table scan

换成另一种join方式
这样才能用起来并行、hash join

SQL> select a.object_id,b.object_name
  2  from test1 a, test1 b, test1 c
  3          where a.object_id = b.object_id(+)
  4          and b.object_id = c.object_id
  5          and b.owner is not null
  6          and c.owner is not null
  7          and a.owner is not null;

已选择13101行。

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

-----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    | 13101 | 652K| 125 (2)| 00:00:02 |
|*  1 |  HASH JOIN       |    | 13101 | 652K| 125 (2)| 00:00:02 |
|*  2 | TABLE ACCESS FULL | TEST1 | 13101 | 140K| 41 (0)| 00:00:01 |
|*  3 | HASH JOIN       |    | 13101 | 511K| 83 (2)| 00:00:01 |
|*  4 | TABLE ACCESS FULL| TEST1 | 13101 | 140K| 41 (0)| 00:00:01 |
|*  5 | TABLE ACCESS FULL| TEST1 | 13101 | 371K| 41 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("B"."OBJECT_ID"="C"."OBJECT_ID")
2 - filter("C"."OWNER" IS NOT NULL)
3 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
4 - filter("A"."OWNER" IS NOT NULL)
5 - filter("B"."OWNER" IS NOT NULL)

统计信息
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   1377  consistent gets
      0  physical reads
      0  redo size
   411425  bytes sent via SQL*Net to client
   10003  bytes received via SQL*Net from client
      875  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   13101  rows processed