ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Tuning Outer Joins

Tuning Outer Joins

原创 Linux操作系统 作者:yyp2009 时间:2012-04-18 16:50:43 0 删除 编辑

Tuning  Left join

SQL> /

已选择1777行。

已用时间:  00: 00: 15.82
执行计划
----------------------------------------------------------
Plan hash value: 3132434474

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |   102M|    23G|       | 23561  (98)| 00:04:43 |
|*  1 |  FILTER                  |      |       |       |       |            |          |
|   2 |   HASH GROUP BY          |      |   102M|    23G|       | 23561  (98)| 00:04:43 |
|*  3 |    HASH JOIN             |      |   102M|    23G|       |  2407  (75)| 00:00:29 |
|*  4 |     TABLE ACCESS FULL    | A    |  2790 |   514K|       |   156   (2)| 00:00:02 |
|*  5 |     HASH JOIN RIGHT OUTER|      |   515K|    25M|  1016K|   486   (4)| 00:00:06 |
|   6 |      TABLE ACCESS FULL   | B    | 41410 |   525K|       |   155   (2)| 00:00:02 |
|   7 |      TABLE ACCESS FULL   | A    | 46902 |  1786K|       |   156   (2)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   1 - filter("A"."CREATED">=MIN("A"."CREATED"))
   3 - access("A"."OWNER"="A"."OWNER")
   4 - filter("A"."OBJECT_TYPE"='INDEX')
   5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID"(+))

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2091  consistent gets          0  physical reads
          0  redo size
     116065  bytes sent via SQL*Net to client
       1683  bytes received via SQL*Net from client
        120  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1777  rows processed
SQL> l
  1   select  *
  2       from a,
  3            (select  a.owner, min(a.created) created
  4               from a
  5               left outer join b on a.object_id = b.data_object_id  6              group by a.owner) temp
  7      where a.owner = temp.owner
  8        and a.object_type = 'INDEX'
  9*       and a.created >= temp.created

我改后:

SQL> l
  1  select  *
  2      from a,
  3           (select  a.owner, min(a.created) created
  4              from a,b
  5              where a.object_id = b.data_object_id(+)  6             group by a.owner) temp
  7     where a.owner = temp.owner
  8       and a.object_type = 'INDEX'
  9*      and a.created >= temp.created
SQL> /

已选择1777行。

已用时间:  00: 00: 00.12
执行计划
----------------------------------------------------------
Plan hash value: 3851281404

-----------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |   467K|    90M|       |   802  (23)| 00:00:10 |
|*  1 |  HASH JOIN               |      |   467K|    90M|       |   802  (23)| 00:00:10 |
|*  2 |   TABLE ACCESS FULL      | A    |  2790 |   482K|       |   156   (2)| 00:00:02 |
|   3 |   VIEW                   |      | 46902 |  1190K|       |   485   (4)| 00:00:06 |
|   4 |    HASH GROUP BY         |      | 46902 |  2381K|       |   485   (4)| 00:00:06 |
|*  5 |     HASH JOIN RIGHT OUTER|      | 46902 |  2381K|  1016K|   478   (2)| 00:00:06 |
|   6 |      TABLE ACCESS FULL   | B    | 41410 |   525K|       |   155   (2)| 00:00:02 |
|   7 |      TABLE ACCESS FULL   | A    | 46902 |  1786K|       |   156   (2)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   1 - access("A"."OWNER"="TEMP"."OWNER")
       filter("A"."CREATED">="TEMP"."CREATED")
   2 - filter("A"."OBJECT_TYPE"='INDEX')
   5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID"(+))

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2091  consistent gets          0  physical reads
          0  redo size
      88024  bytes sent via SQL*Net to client
       1683  bytes received via SQL*Net from client
        120  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1777  rows processed

 

Tuning  Right join

 

SQL> /

 

已选择1694行。

 

已用时间:  00: 00: 01.50

 

执行计划

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

Plan hash value: 1064154308

 

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

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

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

|   0 | SELECT STATEMENT      |      |   102M|    23G|       | 23561  (98)| 00:04:43 |

|*  1 |  FILTER               |      |       |       |       |            |          |

|   2 |   HASH GROUP BY       |      |   102M|    23G|       | 23561  (98)| 00:04:43 |

|*  3 |    HASH JOIN          |      |   102M|    23G|       |  2407  (75)| 00:00:29 |

|*  4 |     TABLE ACCESS FULL | A    |  2790 |   514K|       |   156   (2)| 00:00:02 |

|*  5 |     HASH JOIN         |      |   515K|    25M|  1016K|   486   (4)| 00:00:06 |

|   6 |      TABLE ACCESS FULL| B    | 41410 |   525K|       |   155   (2)| 00:00:02 |

|   7 |      TABLE ACCESS FULL| A    | 46902 |  1786K|       |   156   (2)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("A"."CREATED">=MIN("A"."CREATED"))

   3 - access("A"."OWNER"="A"."OWNER")

   4 - filter("A"."OBJECT_TYPE"='INDEX')

   5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID")

 

Note

-----

   - dynamic sampling used for this statement

 

 

统计信息

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

          0  recursive calls

          0  db block gets

       2091  consistent gets

          0  physical reads

          0  redo size

     110465  bytes sent via SQL*Net to client

       1617  bytes received via SQL*Net from client

        114  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1694  rows processed

 

SQL>        select    *

  2       from a,

  3            (select  a.owner, min(a.created) created

  4               from a

  5               right outer join b on a.object_id = b.data_object_id

  6              group by a.owner) temp

  7      where a.owner = temp.owner

  8        and a.object_type = 'INDEX'

  9        and a.created >= temp.created

 10

SQL>       /

 

我改后:

 

SQL> /

 

已选择1694行。

 

已用时间:  00: 00: 00.10

 

执行计划

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

Plan hash value: 1845623786

 

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

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

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

|   0 | SELECT STATEMENT      |      |   412K|    79M|       |   783  (21)| 00:00:10 |

|*  1 |  HASH JOIN            |      |   412K|    79M|       |   783  (21)| 00:00:10 |

|*  2 |   TABLE ACCESS FULL   | A    |  2790 |   482K|       |   156   (2)| 00:00:02 |

|   3 |   VIEW                |      | 41410 |  1051K|       |   484   (4)| 00:00:06 |

|   4 |    HASH GROUP BY      |      | 41410 |  2102K|       |   484   (4)| 00:00:06 |

|*  5 |     HASH JOIN OUTER   |      | 41410 |  2102K|  1016K|   478   (2)| 00:00:06 |

|   6 |      TABLE ACCESS FULL| B    | 41410 |   525K|       |   155   (2)| 00:00:02 |

|   7 |      TABLE ACCESS FULL| A    | 46902 |  1786K|       |   156   (2)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - access("A"."OWNER"="TEMP"."OWNER")

       filter("A"."CREATED">="TEMP"."CREATED")

   2 - filter("A"."OBJECT_TYPE"='INDEX')

   5 - access("A"."OBJECT_ID"(+)="B"."DATA_OBJECT_ID")

 

Note

-----

   - dynamic sampling used for this statement

 

 

统计信息

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

          0  recursive calls

          0  db block gets

       2091  consistent gets

          0  physical reads

          0  redo size

      84027  bytes sent via SQL*Net to client

       1617  bytes received via SQL*Net from client

        114  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1694  rows processed

 

SQL> l

  1   select  *

  2       from a,

  3            (select  a.owner, min(a.created) created

  4               from a,b

  5               where a.object_id(+) = b.data_object_id

  6              group by a.owner) temp

  7      where a.owner = temp.owner

  8        and a.object_type = 'INDEX'

  9*       and a.created >= temp.created

 

 

 

Conclusion:

1 tun Outer Joins to +  will be possible more efficient;

2  but it will be stands in the way of VIEW MERGE。

 

 

-----------end-----------------

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

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

注册时间:2008-10-17

  • 博文量
    330
  • 访问量
    1017807