【IT168 技术文档】ORACLE数据库是一个复杂的、高度可调的软件产品。对于Oracle的系统参数是高度可调的，对于单个SQL语句也能通过使用提示来调整其优化规则。要了解Oracle的提示，首先要了解Oracle的优化器。
Oracle的优化器共有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。
RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。
CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。
优化模式包括Rule,Choose,First rows,All rows这四种方式：
Rule:基于规则的方式。
Choolse:默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。
First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。
All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。
通常情况下，oracle会按照当前优化器规则来形成一条SQL语句的查询计划。我们可以在SQL语句中使用提示来强制Oracle不按照优化器规则产生的查许计划执行语句，而按照我们在提示中给出的方式执行。
在SQL语句中，提示必须是在SELECT, UPDATE, INSERT或DELETE这些关键字后面。 以下是使用提示的语法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
或
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
在以下几种情况下，我们可能会使用到提示：
强制使用非系统指定的优化器规则。比如，在使用CBO方式下，对于某些特殊类型（如table），在执行查询计划时，Oracle会把它视为表来对待，但是又没有相关的统计信息，这时形成的查询计划可能会是一个很差的查询计划。反而，使用RBO能够形成更佳的查询计划。
在WHERE条件中含有多个索引的字段，Oracle无法正确选择合理索引
在连接查询时，指定更合理的连接方式
分布式查询时，指定更合理的排序站点
某些可以牺牲空间换取时间的地方
… …
另外要说明的是，并不建议过多使用提示，而是要尽量避免使用。对于查询效率差的语句，我们首先应该想到的是从写法上去优化它，而不是去改变系统的优化规则。
准备
为了更好的理解这些提示，先建立以下一些表。为了更加容易理解，会对一些常用提示给出例子，并给出对比，加深对提示的理解。
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 Optimizer=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 Optimizer=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 < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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 < 100000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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 < 9000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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 < 9000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=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 < 100 and f2_char > ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
0 SELECT ST
