oracle - Function-based Index using Substr and Instr -

oracle - Function-based Index using Substr and Instr -

i have created query doing in oracle:

select substr(title,1,instr(title,' ',1,1)) first_word, count(*) word_count cinema grouping substr(title,1,instr(title,' ',1,1)) having count(*) >= 20;

results after running: 539 rows selected. elapsed: 00:00:00.22

i need improve performance of , created function-based index so:

create index indx_firstwrd on film(substr(title,1,instr(title,' ',1,1)));

after running same query @ top of post, still same performance: 539 rows selected. elapsed: 00:00:00.22

is index not beingness applied or overwritten or doing wrong?

thanks help provide. :)

edit:

execution plan: ---------------------------------------------------------- plan hash value: 2033354507 ---------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | ---------------------------------------------------------------------------- | 0 | select statement | | 20000 | 2968k| 138 (2)| 00:00:02 | |* 1 | filter | | | | | | | 2 | hash grouping | | 20000 | 2968k| 138 (2)| 00:00:02 | | 3 | table access full| cinema | 20000 | 2968k| 136 (0)| 00:00:02 | ---------------------------------------------------------------------------- predicate info (identified operation id): --------------------------------------------------- 1 - filter(count(*)>=20) statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 471 consistent gets 0 physical reads 0 redo size 14030 bytes sent via sql*net client 908 bytes received via sql*net client 37 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 539 rows processed

the problem value you're using index may null - if there no space in title (i.e. it's one-word title "jaws") substr evaluates null. isn't want, incidentally - want end position conditional on whether there space @ all, that's beyond scope of question. (and if right logic, oracle may still not able trust result can't null, if underlying column not nullable). edit: see below more on using nvl handle single-word titles.

since nulls aren't included in indexes, single-title rows won't indexed. you're asking rows, , oracle knows index doesn't hold rows, can't utilize index fulfil query - if add together hint telling to, has ignore hint.

the time index used if include filter references indexed value too, , explicitly or implicitly exclude nulls, e.g.:

select substr(title,1,instr(title,' ',1,1)) first_word, count(*) word_count cinema substr(title,1,instr(title,' ',1,1)) not null grouping substr(title,1,instr(title,' ',1,1)) having count(*) >= 20;

(which isn't want).

sql fiddle queries , without filter, , , without index hint. (click 'execution plan' link against each result section see whether it's doing total table scan or total index scan).

and another fiddle showing index can't used filter if filter still allows null values, 1 time again since not in index.

since sylvainleroux brought up, oracle isn't quite clever plenty know computed value can't null if coalesce it, if underlying column not-null (as function-based index or as virtual column). perchance because there lot of branches evaluate. clever plenty if utilize simpler , proprietary nvl instead:

create index indx_firstwrd on film(nvl(substr(title,1,instr(title,' ',1,1)),title)); select nvl(substr(title,1,instr(title,' ',1,1)),title) first_word, count(*) word_count cinema grouping nvl(substr(title,1,instr(title,' ',1,1)),title) having count(*) >= 20;

but if title defined not-null. , coalesce work if the virtual column also declared not-null (thanks sylvain).

sql fiddle function-based index , another virtual column.

oracle performance indexing