Oracle的语句中的提示(2)

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 Optimizer=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 Optimizer=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 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

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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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)

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9650775/viewspace-920363/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9650775/viewspace-920363/