Oracle的语句中的提示

  1. 云栖社区>
  2. 博客>
  3. 正文

Oracle的语句中的提示

yangyi_dba 2010-05-20 18:13:11 浏览399
展开阅读全文

 这所提到的提示,都是Oracle9i的提示。一个语句中,可以含有一个或多个提示,当含有多个提示时,某些或者全部提示会失效。

       提示中表名可以是别名。
为了更好的理解这些提示,先建立以下一些表。为了更加容易理解,会对一些常用提示给出例子,并给出对比,加深对提示的理解。
create table t_huang (f1_num number(10)  not  null,
f2_char varchar2(20)  not  null,
f3_numnull number(10) null,
f4_num  number(10),
f5_char  varchar2(20));
alter table t_huang
   add constraint PK_T_HUANG primary key (f1_num)
using index;
create index ix_huang_f2_char  on t_huang (
   f2_char ASC
);
create index ix_huang_f23_char  on t_huang (
   f2_char, f3_numnull ASC
);
create index ix_huang_f4_num  on t_huang (
   f4_num DESC
);
begin
for i in 1..1000000 loop
    insert into t_huang values(
        i, to_char(dbms_random.random()),
dbms_random.random(), dbms_random.random(),
to_char(dbms_random.random()));
end loop;
commit;
end;
/
 
create table t_wei (f1_num2 number(10)  not  null,
f2_char2 varchar2(20)  not  null,
f3_numnull2 number(10) null,
f4_num2  number(10),
f5_char2  varchar2(20));
alter table t_wei
   add constraint PK_T_WEI primary key (f1_num2)
using index;
create index ix_wei_f234_char  on t_wei (
   f2_char2, f3_numnull2, f4_num2 ASC
);
begin
for i in 1..10000 loop
    insert into t_wei values(
        i, to_char(dbms_random.random()),
dbms_random.random(), dbms_random.random(),
to_char(dbms_random.random()));
end loop;
commit;
end;
/
 
create table t_fuyuncat (f1_num3 number(10)  not  null,
f2_char3 varchar2(20)  not  null,
f3_numnull3 number(10) null,
f4_num3  number(10),
f5_char3  varchar2(20));
alter table t_fuyuncat
   add constraint PK_T_FUYUNCAT primary key (f1_num3)
using index;
create index ix_fuyuncat_f23_char  on t_fuyuncat (
   f2_char3, f3_numnull3 ASC
);
begin
for i in 1..100000 loop
    insert into t_fuyuncat values(
        i, to_char(dbms_random.random()),
dbms_random.random(), dbms_random.random(),
to_char(dbms_random.random()));
end loop;
commit;
end;
/
 
并且做好以下准备工作:
$sqlplus “/as sysdba”
SQL>@/opt/oracle/product/9.2/sqlplus/admin/plustrce
SQL>grant plustrace to hw;
SQL>conn hw/hw
SQL>@/opt/oracle/product/9.2/rdbms/admin/utlxplan
SQL>set timing on
SQL>set autot trace
常用提示
CHOOSE
作用:强制使用CHOOSE为优化器规则;
例子:
SQL>select /*+choose*/ f1_num from t_huang;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T_HUANG'
RULE
作用:强制使用RULE为优化器规则;
例子:
SQL>select /*+rule*/ f1_num from t_huang;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'T_HUANG'
FIRST_ROWS[(n)]
作用:强制使用FIRST_ROW为优化器规则,以最快速度检索第一行,以优化查询。(n)没有则默认为1。这个提示在系统用户通过Oracle Form查询单条或少量数据时特别有用。
注意:在使用update、delete、group by、intersect、minus、union时,first_row提示无效。
例子:
SQL>select /*+first_rows(10000)*/ * from t_huang where f1_num Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=1 Card=4 B
          ytes=252)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=4
          Bytes=252)
   2    1     INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
          =1)
ALL_ROWS
作用:强制使用ALL_ROW为优化器规则,以最快速度检索所有行,以优化查询。当处理大批量数据时,这个提示特别有用。
例子:
SQL>select /*+all_rows*/ * from t_huang where f1_num Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=HINT: ALL_ROWS (Cost=1 Card=4 Byt
          es=252)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=4
          Bytes=252)
   2    1     INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
          =1)
FULL
作用:对所指定的表进行全表扫描。在查询表的大部分数据时使用该索引,将不会扫描索引,而直接进行全表扫描。
例子:
SQL>select /*+full(t_wei)*/ * from t_wei where f1_num2 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=252)
   1    0   TABLE ACCESS (FULL) OF 'T_WEI' (Cost=2 Card=4 Bytes=252)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        663  consistent gets
          0  physical reads
          0  redo size
     541708  bytes sent via SQL*Net to client
       7982  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
    对比:
SQL>select * from t_wei where f1_num2 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
   2    1     INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1270  consistent gets
          0  physical reads
          0  redo size
     541708  bytes sent via SQL*Net to client
       7982  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
    返回的数据为表的大部分数据,在没有使用强制全表扫描的情况下会先扫描索引,比使用全表扫描的方式多出了近一半的consistent gets
 
INDEX[(table index1, index2…)]
作用:强制使用一个或多个索引。在某些情况下(特别是在使用基于成本的优化规则下),Oracle优化器不能正确选择所有,可以通过使用这个提示强制指定使用某一个或多个索引。
例子:
SQL>select /*+index(t_huang PK_T_HUANG)*/ * from t_huang where f1_num ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=66 Bytes=415
          8)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card=6
          6 Bytes=4158)
   2    1     INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
          =4764)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       2009  bytes sent via SQL*Net to client
        667  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed
对比:
SQL>select * from t_huang where f1_num ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
   2    1     INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     251405  consistent gets
          0  physical reads
          0  redo size
       2009  bytes sent via SQL*Net to client
        667  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed
       在强制使用了正确索引后,效果非常明显。
NO_INDEX(table index1, index2 …)
作用:强制使某一个或多个索引失效。
例子:
SQL>select /*+no_index(t_wei PK_T_WEI)*/ * from t_wei where f1_num2 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=252)
   1    0   TABLE ACCESS (FULL) OF 'T_WEI' (Cost=2 Card=4 Bytes=252)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        663  consistent gets
          1  physical reads
          0  redo size
     487612  bytes sent via SQL*Net to client
       7245  bytes received via SQL*Net from client
        601  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8999  rows processed
    对比:
SQL>select * from t_wei where f1_num2 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
   2    1     INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1270  consistent gets
          0  physical reads
          0  redo size
     487612  bytes sent via SQL*Net to client
       7245  bytes received via SQL*Net from client
        601  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       8999  rows processed
INDEX_JOIN(table index1, index2)
作用:将同一个表的不同索引合并,这样就只需要访问这些索引就行了。
例子:
SQL> analyze table t_huang compute statistics;
SQL>select /*+index_join(t_huang PK_T_HUANG, ix_huang_f23_char)*/ f1_num, f2_char, f3_numnull from t_huang where f1_num ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=4392 Card=33 Bytes=6
          93)
   1    0   VIEW OF 'index$_join$_001' (Cost=4392 Card=33 Bytes=693)
   2    1     HASH JOIN
   3    2       INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=4290
          3 Card=33 Bytes=693)
   4    2       INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
           (Cost=42903 Card=33 Bytes=693)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2590  consistent gets
          0  physical reads
          0  redo size
       1514  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)
         27  rows processed
    对比:
SQL>select f1_num, f2_char, f3_numnull from t_huang where f1_num ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
   2    1     INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     251405  consistent gets
          0  physical reads
          0  redo size
       1449  bytes sent via SQL*Net to client
        667  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed
注意:index_join提示只有在基于成本的优化器规则下才有意义。
请对比在基于规则和基于成本优化器下的physical reads
SQL> analyze table t_huang delete statistics;
SQL>select /*+index_join(t_huang PK_T_HUANG, ix_huang_f23_char)*/ f1_num, f2_char, f3_numnull from t_huang where f1_num ‘10000’ and f3_numnull >1000;
Statistics
----------------------------------------------------------
         62  recursive calls
          0  db block gets
       2595  consistent gets
       1890  physical reads
          0  redo size
       1514  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)
         27  rows processed
AND_EQUAL(table index1, index2)
作用:指定多个索引,让优化器使用所指定的索引。它与INDEX_JOIN的区别在于:AND_EQUAL将指定索引合并后再访问表,而INDEX_JOIN提示则只访问索引。
注意:对于位图索引,应该使用INDEX_COMBINE。
SQL> analyze table t_huang compute statistics;
SQL>select /*+and_equal(t_huang ix_huang_f23_char, ix_huang_f4_num)*/ f4_num, f2_char, f3_numnull from t_huang where f2_char > ‘1’ and f3_numnull >1 and f4_num > 100000000000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=66 Card=66 Bytes=250
          8)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=66 Card=6
          6 Bytes=2508)
   2    1     INDEX (RANGE SCAN) OF 'IX_HUANG_F4_NUM' (NON-UNIQUE) (Cost=26 Card=26464)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        403  bytes sent via SQL*Net to client
        460  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
    对比:
SQL>select f4_num, f2_char, f3_numnull from t_huang where f2_char > ‘1’ and f3_numnull >1 and f4_num > 100000000000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
   2    1     INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     252349  consistent gets
          0  physical reads
          0  redo size
        403  bytes sent via SQL*Net to client
        460  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
注意:如果WHERE自己中访问了主键,则该提示将不能正常运行
SQL>select /*+and_equal(t_huang ix_huang_f23_char, ix_huang_f4_num)*/ f4_num, f2_char, f3_numnull from t_huang where f1_num 1 and f4_num > 100000000000;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=66 Bytes=336
          6)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card=6
          6 Bytes=3366)
   2    1     INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
          =4764)
INDEX_COMPILE
作用:与INDEX相同,但是只用于位图索引
例子:略
INDEX_ASC
作用:与INDEX类似,只不过对索引按升序扫描。
例子:略
INDEX_DESC
作用:与INDEX类似,只不过对索引按降序扫描。
例子:略
INDEX_FFS
作用:执行一次索引的快速全局扫描。这个提示只访问索引,而不访问表。只有当要查询的内容都属于索引时,这个提示才有意义。
例子:
SQL>select /*+index_ffs(t_wei pk_t_wei)*/ f1_num2 from t_wei;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=5310 Bytes=69
          030)
   1    0   INDEX (FAST FULL SCAN) OF 'PK_T_WEI' (UNIQUE) (Cost=5 Card
          =5310 Bytes=69030)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        689  consistent gets
          0  physical reads
          0  redo size
     172965  bytes sent via SQL*Net to client
       7981  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
    对比:
SQL>select f1_num2 from t_wei;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T_WEI'
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        730  consistent gets
          0  physical reads
          0  redo size
     172965  bytes sent via SQL*Net to client
       7981  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed
ORDERED
作用:按照From子句中的表的顺序来访问表。Oracle会将表按照它们各自要连接的顺序排列,然后将已经排序的源表合并。
 
提示:在Oracle 8 release 8.0.5中引入了两个参数OPTIMIZER_MAX_PERMUTATIONS 和 OPTIMIZER_SEARCH_LIMIT。
optimizer_search_limit 参数指定了在决定连接多个数据表的最好方式时,CBO需要衡量的数据表连接组合的最大数目。该参数的缺省值是5。如果连接表的数目小于 optimizer_search_limit 参数,那么Oracle会执行所有可能的连接。可能连接的组合数目是数据表数目的阶乘。
假如我们有7张表,那么有7!(5040)种组合。
optimizer_max_permutations参数定义了CBO所考虑的连接排列的最大数目的上限。当我们给这个参数设置很小的一个值的时候,Oracle的计算比较很快就可以被遏制。然后执行计划,给出结果。
optimizer_search_limit参数和optimizer_max_permutations参数和Ordered参数不相容,如果定义了ordered提示,那么optimizer_max_permutations参数将会失效。
实际上,当你定义了ordered提示时,oracle已经无需计算了。
 
注意:如果WHERE子句后面的条件中含有有索引的列,则该提示将不能正常运行
注意:使用ORDERED提示需要临时的内存块,因此SORT_AREA_SIZE必须足够大。
技巧:在基于成本的优化器规则下,效果更好。
例子:
SQL>select /*+ordered*/ a.f1_num2, b.f2_char from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=278 Card=26464 Bytes
          =1323200)
   1    0   NESTED LOOPS (Cost=278 Card=26464 Bytes=1323200)
   2    1     TABLE ACCESS (FULL) OF 'T_WEI' (Cost=12 Card=266 Bytes=6
          650)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=
          100 Bytes=2500)
   4    3       INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        120  consistent gets
          0  physical reads
          0  redo size
       1070  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
    对比:
SQL>select a.f1_num2, b.f2_char from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'T_HUANG'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1016495  consistent gets
          0  physical reads
          0  redo size
       1070  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
使用了提示,则from后面的第一个表t_wei是驱动表,没有使用提示,则按照PL/SQL的编译顺序,以后一张表t_huang作为驱动表。
LEADING(table)
作用:当查询复杂程度增加时,ORDERED按照FROM面的顺序指定访问顺序,即排在第一位作为驱动表。LEADING可以从中间指定某张表作为第一个访问的表。
例子:
SQL>select /*+leading(a)*/ a.f1_num2, b.f2_char, c.f3_numnull3 from t_fuyuncat c, t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=354 Card=99329 Bytes
          =7549004)
   1    0   HASH JOIN (Cost=354 Card=99329 Bytes=7549004)
   2    1     NESTED LOOPS (Cost=278 Card=26464 Bytes=1323200)
   3    2       TABLE ACCESS (FULL) OF 'T_WEI' (Cost=12 Card=266 Bytes
          =6650)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Car
          d=100 Bytes=2500)
   5    4         INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)
   6    1     TABLE ACCESS (FULL) OF 'T_FUYUNCAT' (Cost=39 Card=19930
          Bytes=518180)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        809  consistent gets
          0  physical reads
          0  redo size
       1256  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
    对比:
SQL>select a.f1_num2, b.f2_char, c.f3_numnull3 from t_fuyuncat c, t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T_HUANG'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
   5    4         INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'
   7    6       INDEX (UNIQUE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1016530  consistent gets
          0  physical reads
         68  redo size
       1256  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
ORDERED_PREDICATES
作用:指示优化器按照WHERE子句的顺序来评估查询。
注意:如果WHERE子句后面的条件中含有有索引的列,则会先评估索引。
例子:略
ROWID(table)
作用:使Oracle通过rowid来访问确切的物理位置。
例子:
SQL>select /*+rowid(t_fuyuncat)*/* from t_fuyuncat where rowid not in (select a.rowid from t_fuyuncat a, t_wei b where a.f1_num3=b.f1_num2);
Elapsed: 00:00:01.41
Statistics
----------------------------------------------------------
         22  recursive calls
          0  db block gets
      10717  consistent gets
          0  physical reads
          0  redo size
    4959939  bytes sent via SQL*Net to client
      66644  bytes received via SQL*Net from client
       6001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      90000  rows processed
    对比:
SQL>select * from t_fuyuncat where rowid not in (select a.rowid from t_fuyuncat a, t_wei b where a.f1_num3=b.f1_num2);
Elapsed: 01:22:44.38
Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
 2179704387  consistent gets
          0  physical reads
          0  redo size
     773976  bytes sent via SQL*Net to client
      10940  bytes received via SQL*Net from client
        937  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14025  rows processed
       效果及其明显!
NO_EXPAND
作用:禁止优化器使用OR扩展。如果不使用NO_EXPAND,优化器会产生很长的执行计划。
例子:
SQL>select /*+no_expand*/* from t_fuyuncat where f1_num3 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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

USE_HASH(table)
作用:将指定表保存在内存hash表,快速的与其他表连接在一起。
注意:要使用USE_HASH,必须保证HASH_AREA_SIZE和PGA_AGGREGATE_TARGET足够大。
例子:
SQL>select /*+use_hash(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 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=13 Card=300 Bytes=11
          700)
   1    0   HASH JOIN (Cost=13 Card=300 Bytes=11700)
   2    1     INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE) (Cost=2 Card=2
          66 Bytes=3458)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card
          =1323 Bytes=34398)
   4    3       INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Ca
          rd=1)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         81  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 Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=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
PUSH_SUBQ
作用:可以尽可能早的评估子查询。当子查询返回比较少行时,这个提示比较有用。
注意:当查询使用合并连接和远程表连接时,这个提示无效。
例子:
SQL>select /*+push_subq*/ * from t_huang where f2_char Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=21 Card=1331 Bytes=1
          17128)
   1    0   HASH JOIN (Cost=21 Card=1331 Bytes=117128)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI' (Cost=10 Card=2
          66 Bytes=6650)
   3    2       INDEX (RANGE SCAN) OF 'IX_WEI_F234_CHAR' (NON-UNIQUE)
          (Cost=2 Card=1)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card
          =26464 Bytes=1667232)
   5    4       INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE) (Cost=2 Card=4764)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     508038  consistent gets
         33  physical reads
       2044  redo size
     136343  bytes sent via SQL*Net to client
       2470  bytes received via SQL*Net from client
        167  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2482  rows processed
    对比:
SQL>select * from t_huang where f2_char Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
   3    2       INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-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
    1008640  consistent gets
          0  physical reads
          0  redo size
     136343  bytes sent via SQL*Net to client
       2470  bytes received via SQL*Net from client
        167  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2482  rows processed
PARALLEL(table[, degree[, instances]])
作用:将全表扫描的查询分成多个部分,在不同进程中处理。
注意:该提示只对分区表有效。该提示对delete、update、insert同样有效
例子:略
NOPARALLEL(table)
作用:指定不使用并行查询。
注意:该提示只对分区表有效
例子:略
APPEND
作用:不检查是否有插入所需要的足够空间,直接添加到新块中。
注意:使用该提示必须保证有足够的空间
提示: 在insert中使用parallel提示默认会使用append
例子:略
NOAPPEND
作用:指定不使用append方式。当使用parallel提示时,会默认使用append,可以使用该提示使append无效
例子:略
CACHE(table)
作用:对指定进行全表扫描的表固定到内存中。对于经常要查询的小表可以使用CACHE提示。
提示: 在建表时使用cache子句,可以直接将表中数据存入内存
alter table t_wei cache;
例子:略
NOCACHE(table)
作用:对与已经指定CACHE将数据固定到内存中表,可以用NOCACHE从内存移出。
例子:略
CLUSTER
作用:强制使用聚簇扫描访问表。如果经常访问连接表,但又很少修改它,可以使用聚簇表。
例子:略
HASH(table)
作用:强制使用HASH聚簇。
注意:只有基于代价的优化器规则才能使用HASH聚簇。并且HASH_JOIN_ENABLED要为TRUE,PGAA_GGREGATE_TARGET、HASH_AREA_SIZE要足够大。
例子:略
CURSOR_SHAREING_EXACT
作用:强制使参数CURSOR_SHARING为FORCE或SIMILAR。
例子:略
RICHS_SECRET_HINT
作用:强制只访问内存中的数据。对于不使用索引的全表扫描有效。
例子:略


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zgeyzq/archive/2008/05/10/2430177.aspx

网友评论

登录后评论
0/500
评论
yangyi_dba
+ 关注