Oracle Functional Index 將兩個欄位結合再一起成為索引的方法

可以試試這個方法

SQL>

create index hr.substr_idx on hr.test((SUBSTR(END_TIME,1,6)||SUBSTR(PRODUCT_TYPE, -1,1))) nologging parallel 2;

把兩個欄位的substring 結合成為index

以後就利用這個index來刪資料

--END TIME  :  150420 170000

--FILE_TYPE :  xxxxT , 取最後一碼

delete from hr.test where (SUBSTR(END_TIME,1,6)||SUBSTR(PRODUCT_TYPE, -1,1) = '150420T';

如何驗證有走index:

SQL>

set autotrace on

select * from hr.test where (SUBSTR(END_TIME,1,6)||SUBSTR(PRODUCT_TYPE, -1,1))='150420T';

Execution Plan

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

Plan hash value: 2414353919

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

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

| Id  | Operation                           | Name       | Rows  | Bytes | Cost

(%CPU)| Time     |

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

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

|   0 | SELECT STATEMENT                    |            |     1 |    24 |     2

   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST       |     1 |    24 |     2

   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | SUBSTR_IDX |     1 |       |     1

   (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

   2 - access(SUBSTR(END_TIME,1,6)||SUBSTR(PRODUCT_TYPE, -1,1)

Statistics

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

          1  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        612  bytes sent via SQL*Net to client

        551  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

SQL>