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


[Non-text portions of this message have been removed]