ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 连接取值

连接取值

原创 Linux操作系统 作者:anlinew 时间:2009-08-28 16:47:58 0 删除 编辑

源于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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27378/viewspace-613507/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-06

  • 博文量
    28
  • 访问量
    71048