Fernando Medeiros wrote:

> how you solved the problem ?
>

I think that what Fernando intended to suggest was:

Create an integer column and a trigger that invokes
your stored procedure to populate that column from
the field that has mixed numbers and letters. Index
the new column.

But I think your problem is actually that you need
a descending index to resolve a MAX query. Firebird
indexes are uni-directional, so an ascending index
can optimize a MIN but you need a descending index
to optimize a MAX.

Good luck,

Ann

> regards
>
> 2009/12/29 Fernando Medeiros <fdomds@...>
>
>> Hi Tomek,
>>
>>> but query don't use this index.
>>> I've tried even to force use this index: 'PLAN (tab INDEX ("TAB_IDX1"))',
>> but it says: 'index > cannot be used in the specified plan'.
>>> Any idea?
>> I think it would be better to create a decimal field (or smallint /
>> integer) and index this field.
>> Performance would be better.
>>
>> Regards.
>>
>>
>>
>> 2009/12/28 tomjanczkadao <tomjancz@...>
>>
>> Hi
>>> I need to select max from field, but using only decimal chars from this
>>> field:
>>>
>>> Field1 varchar(20):
>>> 'ab2548'
>>> 'ag9887'
>>> 'cd8745' ...
>>>
>>> I wrote SP only_decmial_char which extracts only decimal chars.
>>> My query:
>>> 'SELECT MAX((select result from only_decmial_char(Field1)))FROM tab'
>>>
>>> It works ok, returns 9887, but it's slow (it reads all records).
>>> I've tried to use computed index:
>>>
>>> CREATE INDEX TAB_IDX1 ON TAB COMPUTED BY ((SELECT result FROM
>>> only_decmial_char(Field1)));
>>>
>>> but query don't use this index.
>>> I've tried even to force use this index: 'PLAN (tab INDEX ("TAB_IDX1"))',
>>> but it says: 'index cannot be used in the specified plan'.
>>>
>>> Any idea?
>>>
>>> Regards, Tomek
>>>
>>>
>>>
>>> ------------------------------------
>>>
>>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>
>>> Visit http://www.firebirdsql.org and click the Resources item
>>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>>
>>> Also search the knowledgebases at http://www.ibphoenix.com
>>>
>>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>> Yahoo! Groups Links
>>>
>>>
>>>
>>>
>>
>> --
>> http://twitter.com/fernandomds
>> http://fernandomedeiros.com.br/blog
>>
>
>
>