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 < 10000;
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 < 100000;
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 < 9000;
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 < 9000;
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 < 100 and f2_char > ‘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 < 100 and f2_char > ‘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 < 9000;
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 < 9000;
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 < 100 and f2_char > ‘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 < 100 and f2_char > ‘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 < 100 and f2_char > ‘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 < 1000 and f3_numnull >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 < 100 or f2_char3 < ‘100’;
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 < 100 or f2_char3 < ‘100’;
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 < 10000;
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 < 10000;
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 < 1000 and b.f3_numnull < 10000;
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 < 1000 and b.f3_numnull < 10000;
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 < 1000 and b.f3_numnull < 10000;
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 < 1000 and b.f3_numnull < 10000;
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 < ‘1000’ and f1_num in (select f1_num2 from t_wei where f2_char2 < ‘1’);
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 < ‘1000’ and f1_num in (select f1_num2 from t_wei where f2_char2 < ‘1’);
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-663256/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 總結SQL語句中的優化提示SQL優化
- oracle 動態語句中的returning用法Oracle
- insert語句中append提示對欄位的檢查APP
- sql語句中as的用法SQL
- Oracle sql 語句中帶有特殊的字元處理OracleSQL字元
- Oracle SQL 語句中正規表示式的應用OracleSQL
- sql語句中JOIN ON 的使用SQL
- oracle 對於SQL語句中物件名的解析順序OracleSQL物件
- Oracle動態語句中返回遊標Oracle
- sql語句中#{}和${}的區別SQL
- sql語句中as的用法和作用SQL
- sql語句中select……as的用法SQL
- 在ORACLE SQL語句中，單引號和雙引號的使用OracleSQL
- SQL語句中exists和in的區別SQL
- SQL語句中not in 和not exist的區別SQL
- sql語句中as的意思是什麼SQL
- 在sql語句中替換Not In 的方法SQL
- sql語句中常量的處理SQL
- SQL 語句中關於 NULL 的那些坑SQLNull
- SQL語句中NULL的真實含義SQLNull
- 查詢語句中escape的轉義字元字元
- MyBatis在SQL語句中取list的大小MyBatisSQL
- SQL語句中SELECT語句的執行順序SQL
- 複用Oracle資料字典解析出SQL語句中用到的所有表OracleSQL
- MyBatis的使用三(在sql語句中傳值)MyBatisSQL
- Python中迴圈語句中的else用法Python
- Python 提取出SQL語句中Where的值的方法PythonSQL
- SQL Server SQL語句中的函式呼叫與Oracle SQL語句函式呼叫一個有趣的差別SQLServer函式Oracle
- SQL語句中的AND和OR執行順序問題SQL
- 如何自動填充SQL語句中的公共欄位SQL
- sql語句中較為重要的查詢邏輯SQL
- SQL語句中的單引號與雙引號SQL
- MV定義語句中包含Fact的VIEW，能否Rewrite ?View
- SQL語句中聚合函式忽略NULL值的總結SQL函式Null
- mysql語句中有引號的問題解決方案MySql
- Python迴圈語句中的索引變數作用域Python索引變數
- MyBatis從插入語句中檢索自動生成的IDMyBatis
- windows批處理之五-for語句中的檔名擷取Windows