> I do a query like this:
>
> SELECT subregion.NAME
> FROM subregion
> WHERE subregion.name collate UNICODE_CI_AI starting with 'abona'
>
> And I get a plan:
> PLAN (SUBREGION NATURAL)
>
> I tried with these indexes, obviously would like to end up with just one
> of them):
>
> CREATE INDEX SUBREGION_IDX1 ON SUBREGION (NAME);

This index should be enough when using STARTING WITH, although I guess
the guilty part in your statement is the COLLATE clause, which prevents
the optimizer to use the index.

Just a wild guess.



--
With regards,
Thomas Steinmaurer

* Upscene Productions - Database Tools for Developers
http://www.upscene.com/

* My Blog
http://blog.upscene.com/thomas/index.php

* Firebird Foundation Committee Member
http://www.firebirdsql.org/en/firebird-foundation/

> CREATE INDEX SUBREGION_IDX2 ON SUBREGION COMPUTED BY (lower(NAME));
> CREATE DESCENDING INDEX SUBREGION_IDX3 ON SUBREGION (NAME);
>
> 'subregion' is a table ultimately I will have a view for subregion which
> brings in another table, but still like to search by name and preferably
> with the use of an index.
>
> This is with FB 2.5.
>
> Looking for tips and/or documentation links to read for better
> understanding on when indexes are used or what to do to get them used.
>
> Werner
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>