HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle的语句中的提示(4)

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2006-07-07 14:50:11

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

NO_EXPAND

作用:禁止优化器使用OR扩展。如果不使用NO_EXPAND,优化器会产生很长的执行计划。

例子:

SQL>select /*+no_expand*/* from t_fuyuncat where f1_num3 < 100 or f2_char3 < ‘100’;

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=1943 Bytes=1

          22409)

   1    0   TABLE ACCESS (FULL) OF 'T_FUYUNCAT' (Cost=39 Card=1943 Byt

          es=122409)

Statistics

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

          0  recursive calls

          0  db block gets

       4009  consistent gets

          0  physical reads

          0  redo size

    2773060  bytes sent via SQL*Net to client

      37285  bytes received via SQL*Net from client

       3332  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49953  rows processed

    对比:

SQL>select * from t_fuyuncat where f1_num3 < 100 or f2_char3 < ‘100’;

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   CONCATENATION

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'

   3    2       INDEX (RANGE SCAN) OF 'IX_FUYUNCAT_F23_CHAR' (NON-UNIQUE)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'

   5    4       INDEX (RANGE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE)

Statistics

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

          0  recursive calls

          0  db block gets

      53427  consistent gets

          0  physical reads

          0  redo size

    2773060  bytes sent via SQL*Net to client

      37285  bytes received via SQL*Net from client

       3332  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      49953  rows processed

DRIVING_SITE

作用:DRIVING_SITE作用和ORDERED类似。DRIVING_SITE通常在分布式查询中使用。如果没有这个提示,Oracle会先从远程点检索,并将它们连接到本地站点中。通过使用DRIVING_SITE,我们可以先在本地进行检索,将检索后的数据发送到远程节点进行连接。

提示:合理使用DRIVING_SITE,可以在分布式查询中大大减少网络流量。

例子:略

USE_MERGE(table1, table2…)

作用:使用Merge Join方式进行连接。先对指定的表进行排序,然后再和其他表合并在一起组成结果集。Merger Join再合并的表的所返回数据量差不多的时候比较有效。

例子:

SQL>select /*+use_merge(a, c)*/ a.f1_num2, c.f3_numnull3 from t_wei a, t_fuyuncat c where a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’ and c.f1_num3 < 10000;

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=14 Bytes=714

          )

   1    0   MERGE JOIN (Cost=26 Card=14 Bytes=714)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT' (Cost=10 C

          ard=997 Bytes=25922)

   3    2       INDEX (RANGE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE) (Cost=2

           Card=179)

   4    1     SORT (JOIN) (Cost=16 Card=13 Bytes=325)

   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI' (Cost=10 Card

          =13 Bytes=325)

   6    5         INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE) (Cost=2 Ca

          rd=1)

Statistics

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

          0  recursive calls

          0  db block gets

        160  consistent gets

          0  physical reads

          0  redo size

       1019  bytes sent via SQL*Net to client

        666  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         16  rows processed

    对比:

SQL> select a.f1_num2, c.f3_numnull3 from t_wei a, t_fuyuncat c where a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’ and c.f1_num3 < 10000;

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   NESTED LOOPS

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'

   3    2       INDEX (RANGE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'

   5    4       INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)

Statistics

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

          0  recursive calls

          0  db block gets

      20089  consistent gets

          0  physical reads

          0  redo size

       1019  bytes sent via SQL*Net to client

        666  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         16  rows processed

USE_NL(table)

作用:使用Nested Loop方式进行连接。以指定的表为驱动表进行嵌套循环查询。Nested Loop对于嵌套查询一张大表和一张小表时比较有效,指定小表为驱动表。

例子:

SQL>select /*+use_nl(a)*/ a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=300 Bytes=11

          700)

   1    0   NESTED LOOPS (Cost=10 Card=300 Bytes=11700)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card

          =1323 Bytes=34398)

   3    2       INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Ca

          rd=4764)

   4    1     INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)

Statistics

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

          0  recursive calls

          0  db block gets

        608  consistent gets

          0  physical reads

          0  redo size

      13072  bytes sent via SQL*Net to client

       1018  bytes received via SQL*Net from client

         35  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        496  rows processed

    对比:

SQL>select a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   NESTED LOOPS

   2    1     INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'

   4    3       INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)

Statistics

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

          0  recursive calls

          0  db block gets

       3069  consistent gets

          0  physical reads

          0  redo size

      13072  bytes sent via SQL*Net to client

       1018  bytes received via SQL*Net from client

         35  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        496  rows processed

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat