oracle經常使用到的hint

tolilong發表於2012-11-06
Oracle
可以在system級別,session級別，statement級別設定最佳化器
[@more@]
  • system
alter system set optimizer_mode=all_rows(first_rows)
  • session
alter session set optimizer_mode=all_rows(first_rows)
  • statement
select /*+first_rows(10)*/
select /*+all_rows(10)*/
1)SQL> select /*+rule*/ count(*) from tt;
2)select /*+full(tt)*/ * from tt where object_id=10;
3)select /*+index(tt tt_index)*/ * from tt;
4)select /*+no_index(tt tt_index)*/ * from tt where object_id=10;
5)select /*+index_desc(tt tt_index)*/ * from tt where object_id<10; 如果導致結果集不完整，會忽略這個hint
6)select /*+index_combine(tt tt_index1)*/ * from tt where object_type='TABLE';
表示強制使用點陣圖索引，如果導致結果集不完整，會忽略這個hint
7)select /*+index_ffs(tt tt_index)*/ * from tt where object_id<100; index fast full scan
8)select /*+index_join(tt tt_index tt_index1)*/ * from tt where object_id<500 and object_type='TABLE';
where後面的列都有索引的時候，可以透過索引關聯的方式來訪問資料
9)index_ss index skip scan當在一個聯合索引中，where後面引用的列不在combine index的第一列的時候
可以透過index skip scan的方式訪問。
當combine index第一列的唯一值和小的時候，使用這種方式
表全表掃描效率更高。
SQL> create table tt1 as select object_type,object_name from dba_objects;
Table created.
SQL> create index tt1_index on tt1(object_type,object_name);
Index created.
QL> select * from tt1 where object_name='TT';

Execution Plan
----------------------------------------------------------
Plan hash value: 3728419071

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 64 | 33 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TT1_INDEX | 2 | 64 | 33 (0)| 00:00:01 |
------------------------------------------------------------------------------
但是當combine index中的重複列很少的時候，fts比index_ss的效率要高。
SQL> create table tt2 as select object_id,object_name from dba_objects;

Table created.

SQL> create index tt2_index on tt2(object_id,object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats('sys','tt2');

PL/SQL procedure successfully completed.

SQL> select * from tt2 where object_name='TT';


Execution Plan
----------------------------------------------------------
Plan hash value: 1248358058

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 54 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TT2 | 2 | 58 | 54 (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='TT')


Statistics
----------------------------------------------------------
128 recursive calls
0 db block gets
260 consistent gets
0 physical reads
0 redo size
647 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> select /*+index_ss(tt2 tt2_index)*/ * from tt2 where object_name='TT';


Execution Plan
----------------------------------------------------------
Plan hash value: 3854296115

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 50060 (1)| 00:10:01 |
|* 1 | INDEX SKIP SCAN | TT2_INDEX | 2 | 58 | 50060 (1)| 00:10:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_NAME"='TT')
filter("OBJECT_NAME"='TT')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
283 consistent gets
0 physical reads
0 redo size
647 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
表關聯順序hint
10) leading 指定哪個表作為驅動表，即告訴最佳化器先訪問那個表上的資料。
SQL> select /*+leading(tt1,tt)*/* from tt,tt1 where tt.object_name=tt1.object_name and tt1.object_name='TT1'; (指定先訪問tt1標上的資料)
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3953670397
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 375 | 187 (2)| 00:00:03 |
|* 1 | HASH JOIN | | 3 | 375 | 187 (2)| 00:00:03 |
|* 2 | INDEX SKIP SCAN | TT1_INDEX | 2 | 64 | 33 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TT | 2 | 186 | 154 (2)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TT"."OBJECT_NAME"="TT1"."OBJECT_NAME")
2 - access("TT1"."OBJECT_NAME"='TT1')
filter("TT1"."OBJECT_NAME"='TT1')
3 - filter("TT"."OBJECT_NAME"='TT1')
11)ordered 表示按照from後面表的順序來選擇驅動表.
select /*+ordered*/ * from tt2,tt where tt.object_id=tt2.object_id and tt.object_id<400;
表關聯操作的hint
11)use_hash,use_nl,use_merge
當兩張表都比較大的時候，hash join的效率要高於nested loop
      • hash join 的工作方式是將一個表做hash運算，將列資料儲存到hash列表中，從另一個表中抽取記錄，做hash運算，到hash列表中找到相應的值做匹配。
      • nested loop 是將一張表中讀取資料，訪問另一張表(通常是index)來做匹配，nested loop使用的場合是一個關聯表比較小的時候，效率會更高
      • merge join首先將關聯表的關聯列各自做排序，然後從各自的排序列中抽取資料，到另一個表中做匹配，應該merge需要做更多的排序，所以消耗的資源會更多一些，通常來講，用merge join的地方，用hash join會更好一些。
-----==================================================
select /*+use_hash(tt tt3)*/* from tt,tt3 where tt.object_id=tt3.object_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.01 0.02 0 765 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.02 0 765 0 999
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
999 HASH JOIN (cr=765 pr=0 pw=0 time=6818 us)
999 INDEX FAST FULL SCAN TT3_INDEX (cr=8 pr=0 pw=0 time=94 us)(object id 52263)
49975 TABLE ACCESS FULL TT (cr=757 pr=0 pw=0 time=194 us)
----=====================================================
select /*+use_nl(tt tt3)*/* from tt,tt3 where tt.object_id=tt3.object_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.10 0.13 0 50870 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.10 0.13 0 50870 0 999
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
999 NESTED LOOPS (cr=50870 pr=0 pw=0 time=7091 us)
49975 TABLE ACCESS FULL TT (cr=757 pr=0 pw=0 time=74 us)
999 INDEX RANGE SCAN TT3_INDEX (cr=50113 pr=0 pw=0 time=99513 us)(object id 52263)
---=====================================================
select /*+use_merge(tt tt3)*/* from tt,tt3 where tt.object_id=tt3.object_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 159 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 159 0 999
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
999 MERGE JOIN (cr=159 pr=0 pw=0 time=6841 us)
1000 TABLE ACCESS BY INDEX ROWID TT (cr=151 pr=0 pw=0 time=4100 us)
1000 INDEX FULL SCAN TT_INDEX (cr=71 pr=0 pw=0 time=1047 us)(object id 52177)
999 SORT JOIN (cr=8 pr=0 pw=0 time=2316 us)
999 INDEX FAST FULL SCAN TT3_INDEX (cr=8 pr=0 pw=0 time=33 us)(object id 52263)
12) no_use_nl,no_use_hash,no_use_merge
13) select /*+parallel(tt 2)*/ * from tt;
14) select /*+no_parallel(tt)*/ * from tt; 當表的degree>1的時候，可以使用no_parallel取消並行。
15) insert /*+append*/ into tt select * from tt;
alter table tt parallel (degree 1);
16) select /*+dynamic_sampling(tt 4)*/* from tt where object_id<100;
17) driving site() 在分散式資料庫操作中比較有用。
18) select /*+cache(tt3)*/ * from tt3 where object_id<100; 如果使用了這個hint，
會將掃描的塊放到LRU列表中的最被使用端（即資料塊最活躍端）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24237320/viewspace-1059739/，如需轉載，請註明出處，否則將追究法律責任。

相關文章