Index
為什麼我的基於函式的索引沒有被使用?
我有一個
EVENT
帶有列的表DATE_RECEIVED
(該列的類型是DATE
):CREATE TABLE "EVENT" ("EVENT_ID" VARCHAR2(60 BYTE) NOT NULL ENABLE, [...] "DATE_RECEIVED" DATE, [...] );
我還在以下位置創建了一個基於函式的索引
DATE_RECEIVED
:CREATE INDEX IDX_EVENT_TRUC_DATE_RECEIVED ON EVENT(TRUNC("DATE_RECEIVED"));
但是沒有使用這個索引。當我執行以下請求時,我得到了一個 TABLE ACCESS FULL:
SELECT * FROM event WHERE trunc(date_received) = TO_DATE('30/05/2016', 'DD/MM/YYYY');
在計劃表中,我得到:
---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| CORE | ---------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 - filter(TRUNC(INTERNAL_FUNCTION("DATE_RECEIVED"))=TO_DATE(' 2016-05-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Note ----- - rule based optimizer used (consider using cbo)
我發現這
INTERNAL_FUNCTION
意味著存在隱式轉換。但我不知道為什麼,因為TRUNC
接受DATE
類型。我可以做些什麼來使用我的索引?
正如@JSapkota 在評論中所說,我需要使用 CBO 而不是 RBO。為此,我需要在
EVENT
表格上收集統計資訊:EXEC DBMS_STATS.gather_table_stats('USER', 'EVENT');
這一次,使用了索引:
------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 20000 | 25M| 2166 (1)| 00:00:26 | | 1 | TABLE ACCESS BY INDEX ROWID| EVENT | 20000 | 25M| 2166 (1)| 00:00:26 | |* 2 | INDEX RANGE SCAN | IDX_EVENT_TRUC_DATE_RECEIVED | 8000 | | 88 (0)| 00:00:02 | ------------------------------------------------------------------------------------------------------------