oracle经常使用到的hint

  • tolilong

    2012-11-06 11:30:26
  • 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列表中的最被使用端（即数据块最活跃端）
请使用浏览器的分享功能分享到微信等