This might be a duplicate, posted this earlier today, about 8 hours ago and it still doens't show nor did I get a reject or other error message.

So, if you don't need a (complex) logic for returning a result set,

>
> you'd better use a view, because a view can use an index for a provided
> WHERE clause.
>

I am trying the view approach but must still be missing something.

Table def is:
CREATE TABLE SUBREGION_L (
NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
etc etc

The above table contains translations for the column name, e.g. French and German.

CREATE TABLE SUBREGION (
NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
etc etc

The "subregion" table contains the default language, in this case English and other other columns which don't get translated.

Both tables have an index on the NAME column.

The select on subregion uses the index, i.e.:

select * from subregion where name starting with 'mur';

Plan
PLAN (SUBREGION INDEX (IX_SUBREGION_NAME))

Then I build a view like this:

CREATE OR ALTER VIEW subregion_LVtest(
ID,
NAME,
SEARCHNAME,
FK_LANGUAGE_ID,
FK_SUBREGION_L_ID
)
AS
SELECT
o.ID,
COALESCE(o.name, t.name),
COALESCE(o.searchname, t.searchname),
COALESCE(1, t.FK_LANGUAGE_ID),
t.ID

FROM subregion o
LEFT OUTER JOIN subregion_l t ON t.fk_subregion_id=o.id
and t.fk_language_id=rdb$get_context('USER_SESSION', 'LANGUAGE_ID');


Then doing this select:

select * from subregion_lvtest where name starting with 'mur';

I get this plan:
Plan
PLAN JOIN (SUBREGION_LVTEST O NATURAL, SUBREGION_LVTEST T INDEX (FK_SUBREGION_L_SUBREGION_ID, FK_SUBREGION_L_LANGUAGE_ID))

I guess it is due to the use of "COALESCE" on the column used for the where clause.

Is there something I can do to get the index used for the "name" column.

Werner