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/，如需轉載，請註明出處，否則將追究法律責任。