Не могу понять, почему у меня странно работает функциональный индекс:
1.
2.
3.
CREATE TABLE tmp_table (id NUMBER, val VARCHAR2(100));
CREATE INDEX idx_tmp_table ON tmp_table( CASE WHEN MOD(id,1000)=0 THEN 1 ELSE NULL END );
INSERT INTO tmp_table SELECT level, 'id='||LEVEL FROM dual CONNECT BY level <= 100000;
Если делать просто запросы к таблице с данным условием - всё работает:
1.
SELECT * FROM tmp_table WHERE CASE WHEN MOD(id,1000)=0 THEN 1 ELSE NULL END = 1;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Plan Hash Value : 1294569558
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 68000 | 1 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TMP_TABLE | 1000 | 68000 | 1 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IDX_TMP_TABLE | 1000 | | 1 | 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access(CASE MOD("ID",100) WHEN 0 THEN 1 ELSE NULL END =1)
Если потом к данной таблице джойнить обычные таблицы - всё тоже очень хорошо - индекс подхватывается.
Стоит только добавить в запрос объектный тип - всё, как отрубает, даже хинтами не получается заставить использовать индекс.
1.
2.
3.
4.
SELECT /*+ leading(t1) index(t1 idx_tmp_table) */ t1.*
FROM tmp_table t1 , TABLE(SYS.Odcinumberlist(1000,2000,3000)) t2
WHERE CASE WHEN MOD(t1.id,1000)=0 THEN 1 ELSE NULL END = 1
AND t2.column_value = t1.id
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
Plan Hash Value : 2034260201
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 469 | 9398 | 00:00:04 |
| * 1 | HASH JOIN | | 7 | 469 | 9398 | 00:00:04 |
| * 2 | TABLE ACCESS FULL | TMP_TABLE | 8229 | 534885 | 9366 | 00:00:04 |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 8168 | 16336 | 32 | 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("T1"."ID"=VALUE(KOKBF$))
* 2 - filter(MOD("T1"."ID",1000)=0)
Сбор статистики не помогает - даже если сделать в таблице 10 миллионов записей - и индекс такой, что под функциональный индекс попадает только сотня (т.е селективность огромная), а всё равно не хочет выбирать по индексу.
Это что какое-то ограничение оракловое ?
1.
2.
3.
4.
5.
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
--------------------------------------------------------------
Запомните, товарищи офицеры, чтобы ничего не делать, надо уметь делать все.
