ITPub博客

首页 > Linux操作系统 > Linux操作系统 > exists、in、table join 实际案例

exists、in、table join 实际案例

原创 Linux操作系统 作者:anlinew 时间:2010-08-03 18:59:01 0 删除 编辑
 

SQL> select /*+ optimizer_features_enable('9.2.0') */

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where exists (select 1

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and a.pk_bdinfo = b.pk_bdinfo

 17             and (acc.pk_glorgbook = '0001AA1000000000S3MT'));

 

已选择16行。

 

已用时间:  00: 00: 00.98

 

执行计划

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

Plan hash value: 30633670

 

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

| Id  | Operation          | Name         | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT   |              |    15 |  1590 |    11 |

|*  1 |  FILTER            |              |       |       |       |

|   2 |   TABLE ACCESS FULL| BD_BDINFO    |    15 |  1590 |     5 |

|   3 |   NESTED LOOPS     |              |   802 | 67368 |     6 |

|*  4 |    INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4 |

|*  5 |    INDEX RANGE SCAN| I_BD_SUBJASS |     1 |    42 |     1 |

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

 

统计信息

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

          0  recursive calls

          0  db block gets

     412764  consistent gets

          0  physical reads

          0  redo size

       1861  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed

        

换成in,效率的到提高

 

SQL> select /*+optimizer_features_enable('9.2.0')*/

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where pk_bdinfo in (select b.pk_bdinfo

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and (acc.pk_glorgbook = '0001AA1000000000S3MT'));

 

 

SQL> /

 

已选择16行。

 

已用时间:  00: 00: 00.01

 

执行计划

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

Plan hash value: 2360601918

 

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT    |              |    22 |  2816 |    33 |

|*  1 |  HASH JOIN SEMI     |              |    22 |  2816 |    33 |

|   2 |   TABLE ACCESS FULL | BD_BDINFO    |   292 | 30952 |     5 |

|   3 |   VIEW              | VW_NSO_1     |  2346 | 51612 |    27 |

|   4 |    NESTED LOOPS     |              |  2346 |   192K|    27 |

|*  5 |     INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4 |

|*  6 |     INDEX RANGE SCAN| I_BD_SUBJASS |     3 |   126 |     1 |

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

 

统计信息

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

          0  recursive calls

          0  db block gets

       1525  consistent gets

          0  physical reads

          0  redo size

       1946  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed  

        

实际是CBO优化器没有对exists做unnest转换,提示其转换,效率得到提高              

 

SQL> ed

已写入 file afiedt.buf

 

  1  select /*+ optimizer_features_enable('9.2.0') */

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where exists (select /*+unnest*/ 1

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and a.pk_bdinfo = b.pk_bdinfo

 17*            and (acc.pk_glorgbook = '0001AA1000000000S3MT'))

SQL> /

 

已选择16行。

 

已用时间:  00: 00: 00.03

 

执行计划

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

Plan hash value: 2542036995

 

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

| Id  | Operation           | Name         | Rows  | Bytes | Cost  |

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

|   0 | SELECT STATEMENT    |              |    22 |  2816 |    33 |

|*  1 |  HASH JOIN SEMI     |              |    22 |  2816 |    33 |

|   2 |   TABLE ACCESS FULL | BD_BDINFO    |   292 | 30952 |     5 |

|   3 |   VIEW              | VW_SQ_1      |  2346 | 51612 |    27 |

|   4 |    NESTED LOOPS     |              |  2346 |   192K|    27 |

|*  5 |     INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4 |

|*  6 |     INDEX RANGE SCAN| I_BD_SUBJASS |     3 |   126 |     1 |

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

 

统计信息

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

          1  recursive calls

          0  db block gets

       1525  consistent gets

          0  physical reads

          0  redo size

       1946  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed

 

oracle 10G正常情况下会做unnest,如下

 

  1  select /*+ optimizer_features_enable('10.2.0.1') */

  2         pk_bdinfo,

  3         bdcode,

  4         bdname,

  5         bdtype,

  6         tablename,

  7         tablepkname,

  8         corpfieldname,

  9         codefieldname,

 10         namefieldname,

 11         refnodename

 12    from ufnc5610.bd_bdinfo a

 13   where exists (select  1

 14            from ufnc5610.bd_subjass b, ufnc5610.bd_accsubj acc

 15           where b.pk_accsubj = acc.pk_accsubj

 16             and a.pk_bdinfo = b.pk_bdinfo

 17*            and (acc.pk_glorgbook = '0001AA1000000000S3MT'))

SQL> /

 

已选择16行。

 

已用时间:  00: 00: 00.01

 

执行计划

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

Plan hash value: 2542036995

 

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

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

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

|   0 | SELECT STATEMENT    |              |    22 |  2816 |    36   (3)| 00:00:01 |

|*  1 |  HASH JOIN SEMI     |              |    22 |  2816 |    36   (3)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | BD_BDINFO    |   292 | 30952 |     8   (0)| 00:00:01 |

|   3 |   VIEW              | VW_SQ_1      |  2346 | 51612 |    27   (0)| 00:00:01 |

|   4 |    NESTED LOOPS     |              |  2346 |   192K|    27   (0)| 00:00:01 |

|*  5 |     INDEX RANGE SCAN| I_BD_ACCSUBJ |   765 | 32130 |     4   (0)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN| I_BD_SUBJASS |     3 |   126 |     1   (0)| 00:00:01 |

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

 

统计信息

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

          1  recursive calls

          0  db block gets

       1525  consistent gets

          0  physical reads

          0  redo size

       1946  bytes sent via SQL*Net to client

        250  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed

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

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

注册时间:2007-12-06

  • 博文量
    28
  • 访问量
    71218