Oracle technology: Using index hints to force indexes

Although indexes are not always faster than full table scans, many times we want Oracle to use indexes to execute certain SQL, at which point we can force SQL to use index through the index hints.

The format of the Index hints is as follows:

/*+ index (table [index [index] ...]) * * Let's take a quick look at the use of this hint (the example is the oracle10g database):

Sql> CREATE table T as select Username,password from Dba_users; Table created. Sql> CREATE index i_t on T (username), index created. sql> set autotrace trace explainsql> Select/*+ Index (t i_t) * * from T where username= ' eygle '; Execution Plan----------------------------------------------------------The plan hash value:2928007915--------------- ---------------------------------------------------------------------| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU) | Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |      |     1 |    34 |     2   (0) | 00:00:01 | |   1 |  table ACCESS by INDEX rowid| T    |     1 |    34 |     2   (0) | 00:00:01 | | *  2 |   INDEX RANGE SCAN &NBSp        | i_t  |     1 |       |     1   (0) | 00:00:01 |------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------  2-access (" USERNAME "= ' eygle ') Note----- -Dynamic sampling used for this statement the query here uses the index.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Note that you create a datasheet using the CTAs method, and the new table inherits the constraint properties of the original table:

Sql> desc t Name Null?                                  Type-----------------------------------------------------------------------------USERNAME Not NULL VARCHAR2 (a) PASSWORD VARCHAR2 (30)

If you do not use hints, the index is not used here by Oracle:

Sql> select * from t where username= ' eygle '; Execution Plan----------------------------------------------------------The plan hash value:1601196873--------------- -----------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU) | Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    34 |     2   (0) | 00:00:01 | | *  1 |  table ACCESS full| T    |     1 |    34 |     2   (0) | 00:00:01 |--------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------  1-filter ("USERNAME" = ' eygle ') Note----- -Dynamic sampling used the selection and trade-offs of the statement index and full table scan are not simple, this article does not discuss further.

Author: 51cto Oracle small-mixed son