使用Index提示強制使用索引

2024-07-21 02:32:48

字體：大中小

來源：轉載

供稿：網友

雖然索引并不總會快于全表掃描,但是很多時候我們希望Oracle使用索引來執行某些SQL,這時候我們可以通過index hints來強制SQL使用index.Index Hints的格式如下:

/*+ INDEX ( table [index [index]...] ) */

我們簡單看一下這個提示的用法(范例為Oracle10g數據庫):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
----------------------------------------------------------
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          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 這里的查詢使用了索引.需要注重的是使用CTAS方式創建數據表,新建表會繼續原表的約束屬性:SQL> desc t
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
PASSWORD                                           VARCHAR2(30)
假如不使用Hints,此處Oracle不會使用索引:SQL> select * from t where username='EYGLE';Execution Plan
----------------------------------------------------------
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 for this statement 索引和全表掃描的選擇和取舍并非簡單,本文不作進一步探討.

上一篇：基于自定義函數的Function-Based索引創建

下一篇：使用DBMS_UTILITY轉換Block地址