Oracle的语句中的提示(4)
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
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
| 上一篇：Oracle的语句中的提示(5) | 下一篇：Oracle的语句中的提示(3) |
| 本类中的所有文章 |