10g新增列方式指定HINT

 

看优化文档的时候发现，指定索引的HINT还可以通过列方式。

 

 

在9i和以前的版本，索引提示的格式为/*+ index(table_alias) */或/*+ index(table_alias index_name) */，但是在10g中不仅可以通过索引名称来确定HINT的索引，还可以通过指定列名的方式。

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')

除了常规的索引名称方式，还可以通过owner或owner、object_type列的方式指定：

SQL> select /*+ index(t (t.owner)) */ *

  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')

SQL> select /*+ index(t (owner, object_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')

可以通过索引的所有列来指定，也可以通过前缀列的方式，索引列前面可以添加表的别名，如果省略则默认是前面指定别名的表。

但是，不能通过非前缀列指定索引，甚至指定列的次序与索引列顺序不一致都会导致HINT失效：

SQL> select /*+ index(t (object_type)) */ *

  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 (object_type, owner)) */ *

  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')

 

 

（以上内容摘于网络，如有侵权，请告之，将第一时间删除）