Hint can be specified by column in Oracle 10g

Looking at the optimized document, it is found that the hint of the specified index can also be column-style.

In 9i and previous versions, the index hint format is/*+ index (table_alias) * * * or/*+ index (table_alias index_name) * *, but in 10g it is possible to determine hint indexes not only through index names, You can also specify a column name by the way.

Sql> select * from V$version;

BANNER

----------------------------------------------------------------

Oracle database10genterpriseedition Release10.2.0.3.0-64bi

Pl/sql Release 10.2.0.3.0-production

CORE 10.2.0.3.0 Production

TNS for Linux:version 10.2.0.3.0-production

Nlsrtl Version 10.2.0.3.0-production

Sql> CREATE TABLE T

2 AS SELECT *

3 from Dba_objects;

Table created.

Sql> CREATE INDEX Ind_t_owner_type

2 on t (owner, object_type);

Index created.

sql> exec dbms_stats.gather_table_stats (user, ' T ', method_opt => ' for all indexed columns size 100 ')

Pl/sql procedure successfully completed.

sql> Set Autot trace exp

Sql> SELECT *

2 from T

3 Where Wner = ' SYS ';

Execution Plan

----------------------------------------------------------

Plan Hash value:1601196873

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

--------------------------------------------------------------------------

| 0 |      SELECT STATEMENT | |  23299 |   2138k| 180 (1) | 00:00:03 |

|* 1 | TABLE ACCESS full| T |  23299 |   2138k| 180 (1) | 00:00:03 |

--------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

1-filter ("OWNER" = ' SYS ')

Sql> Select/*+ Index (t ind_t_owner_type)/*

2 from T

3 Where Wner = ' SYS ';

Execution Plan

----------------------------------------------------------

Plan Hash value:393469706

--------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | bytes| Cost (%CPU) | Time |

--------------------------------------------------------------------------------------------

| 0 |                  SELECT STATEMENT | |   23299 |2138k| 721 (1) | 00:00:11 |

|  1 | TABLE ACCESS by INDEX rowid| T |   23299 |2138k| 721 (1) | 00:00:11 |

|* 2 | INDEX RANGE SCAN | Ind_t_owner_type |     23299 |    | 44 (0) | 00:00:01 |

--------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-access ("OWNER" = ' SYS ')

In addition to the normal way of indexing names, you can also specify by owner or owner, object_type columns: