Oracle的语句中的提示(1)
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
前导
这所提到的提示，都是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 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