oracle hints index格式

只是简单记录关于加index hints的格式

标准加hint方式
/*+index(表名 索引名)*/
SQL> create table t5 (a int,b int);

Table created.

declare
begin
for i in 1..5000 loop
insert into t5 values(i,i+1);
end loop;
commit;
end;

SQL> execute dbms_stats.gather_table_stats('SYS','T5');

PL/SQL procedure successfully completed.

SQL> create index t5_id on t5(a);

Index created.

SQL> select /*+index(t5 t5_id)*/* from t5 where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 711254476

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4001 | 28007 |    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5    |  4001 | 28007 |    18   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T5_ID |  4001 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A">1000)

SQL> select /*+index(t5 t5_id)*/* from t5 t55 where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4001 | 28007 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T5   |  4001 | 28007 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A">1000)

有别名就是 别名 索引名
SQL> select /*+index(t55 t5_id)*/* from t5 t55 where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 711254476

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4001 | 28007 |    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5    |  4001 | 28007 |    18   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T5_ID |  4001 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A">1000)

直接用表名，会选择where上的index（自动）根据cost 选择，如果cost都一样，那么此时候按index名字顺序 比如a,z选a
SQL> select /*+index(t5)*/* from t5  where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 711254476

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4001 | 28007 |    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5    |  4001 | 28007 |    18   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T5_ID |  4001 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A">1000)

 

有时候rename index是件很麻烦的事 尤其对于加hint引用这个index的sql，10g开始,oracle可以直接 表名（表名.列名） 这样 好处是不依赖于这个index name而是这个列上的index
SQL> select /*+index(t5(t5.a))*/* from t5  where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 711254476

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4001 | 28007 |    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5    |  4001 | 28007 |    18   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T5_ID |  4001 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A">1000)

SQL> select /*+index(t55(t55.a))*/* from t5 t55 where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4001 | 28007 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T5   |  4001 | 28007 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A">1000)

对于有别名的表 就是(别名（表名.列名）)
SQL> select /*+index(t55(t5.a))*/* from t5 t55 where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 711254476

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4001 | 28007 |    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5    |  4001 | 28007 |    18   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T5_ID |  4001 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A">1000)

SQL> select /*+index(t5(t5.a))*/* from t5 t55 where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4001 | 28007 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T5   |  4001 | 28007 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A">1000)

SQL> select /*+index(t5(t55.a))*/* from t5 t55 where a>1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4001 | 28007 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T5   |  4001 | 28007 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A">1000)

SQL>

 

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

转载于:http://blog.itpub.net/12020513/viewspace-631365/