常见Oracle HINT的用法

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

常见Oracle HINT的用法

重庆八怪 2009-05-05 11:04:06 浏览303
展开阅读全文

转自

http://www.hackhome.com/InfoView/Article_166086.html

   1. /*+ALL_ROWS*/
  表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
  例如:
  SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
  2. /*+FIRST_ROWS*/
  表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
  例如:
  SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
  3. /*+CHOOSE*/
  表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
  表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
  例如:
  SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

  4. /*+RULE*/
  表明对语句块选择基于规则的优化方法.
  例如:
  SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

  5. /*+FULL(TABLE)*/
  表明对表选择全局扫描的方法.
  例如:
  SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

  6. /*+ROWID(TABLE)*/
  提示明确表明对指定表根据ROWID进行访问.
  例如:
  SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
  AND EMP_NO='SCOTT';

  7. /*+CLUSTER(TABLE)*/
  提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
  例如:
  SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
  WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  8. /*+INDEX(TABLE INDEX_NAME)*/
  表明对表选择索引的扫描方法.
  例如:
  SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

  9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
  表明对表选择索引升序的扫描方法.
  例如:
  SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

  10. /*+INDEX_COMBINE*/
  为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
  例如:
  SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
    WHERE SAL

  11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
  提示明确命令优化器使用索引作为访问路径.
  例如:
  SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
  FROM BSEMPMS WHERE SAL

  12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
  表明对表选择索引降序的扫描方法.
  例如:
  SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

  13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
  对指定的表执行快速全索引扫描,而不是全表扫描的办法.
  例如:
  SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TE垃圾产品';

  14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
  提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
  例如:
  SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';

  15. /*+USE_CONCAT*/
  对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
  例如:
  SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

  16. /*+NO_EXPAND*/
  对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
  例如:
  SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';


  17. /*+NOWRITE*/
  禁止对查询块的查询重写操作.

  18. /*+REWRITE*/
  可以将视图作为参数.

  19. /*+MERGE(TABLE)*/
  能够对视图的各个查询进行相应的合并.
  例如:
  SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO
  ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO
  AND A.SAL>V.AVG_SAL;

  20. /*+NO_MERGE(TABLE)*/
  对于有可合并的视图不再合并.
  例如:
  SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

  21. /*+ORDERED*/
    根据表出现在FROM中的顺序,ORDERED使Oracle依此顺序对其连接.
  例如:
  SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

  22. /*+USE_NL(TABLE)*/
  将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
  例如:
  SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  23. /*+USE_MERGE(TABLE)*/
  将指定的表与其他行源通过合并排序连接方式连接起来.
  例如:
  SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  24. /*+USE_HASH(TABLE)*/
  将指定的表与其他行源通过哈希连接方式连接起来.
  例如:
  SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

  25. /*+DRIVING_SITE(TABLE)*/
  强制与ORACLE所选择的位置不同的表进行查询执行.
  例如:
  SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

  26. /*+LEADING(TABLE)*/
  将指定的表作为连接次序中的首表.

  27. /*+CACHE(TABLE)*/
  当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
  例如:
  SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

  28. /*+NOCACHE(TABLE)*/
  当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
  例如:
  SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

  29. /*+APPEND*/
  直接插入到表的最后,可以提高速度.
  insert /*+append*/ into test1 select * from test4 ;

  30. /*+NOAPPEND*/
  通过在插入语句生存期内停止并行模式来启动常规插入.
  insert /*+noappend*/ into test1 select * from test4 ;

 

Oracle的语句中的提示


前导

       这所提到的提示,都是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

作用:强制只访问内存中的数据。对于不使用索引的全表扫描有效。

例子:略

 

网友评论

登录后评论
0/500
评论
重庆八怪
+ 关注