Oracle的语句中的提示(4)

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

[@more@]

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

转载于:http://blog.itpub.net/9650775/viewspace-920365/