indexing - first character count navigation with Oracle: count queries dont't use functional indices -

oracle 9i. have large table (~1m rows) containing our content carries title , author columns. we'd write views offer a-z navigation titles , authors content ( a:1300, b:45000,...)

first preparation without indexing:

select * content substr(upper(title),0,1) = 'm' 

performs little better than

select * content upper(title) 'm%' 

explan those:

table access content full cost=1624 

both pretty fast without indices. slow part:

select count(*) content substr(upper(title),0,1) = 'a'; 

explan:

sort aggregate else above. 

now cumulation (this want, it's slow):

select substr(upper(title),0,1) , count(*) content group substr(upper(title),0,1); 

explan:

sort: group cost=8069 / table access on content full cost=1624 

so started creating functional index:

create index content_title_letter_idx on content(substr(upper(title),0,1)); 

this speeds single letter count query dramatically:

select count(*) content substr(upper(title),0,1) = 'a'; 

explan (it responds in realtime):

sort aggregate cost=1 / index content_title_letter_idx range scan cost=1 

but cumulation query querying same thing not using index (it shows same explain plan above). tried hint:

select /*+ index(content content_title_letter_idx) */ substr(upper(title),0,1) , count(*) content group substr(upper(title),0,1); 

but still slow. assume might due unordered index i'd guess if ran loop around 26 possible letters single query ( = 'letter' ) faster!

who knows how tell oracle use index (or alternative way besides creating one-char columns or tables) ?

take @ query:

select substr(upper(title),0,1) , count(*)  content  group substr(upper(title),0,1) 

note absence of where clause. in fact, tell database engine take all rows , count how many rows there each initial letter. cannot skip row because else can't count it. don't think index stores such information readily, full scan fastest can have. if asked specific letter, using range scan on index make sense.

if need information often, create summary table updated triggers on main table.