Hi,
i have a view
CREATE VIEW V_DMS(
NR)
AS
select
CASE
when (id_fertigungsauftrag is null) then t_apos.f_nr
else t_apos.f_nr || '/'||fa.f_nr
END
from t_apos
left join t_fertigungsauftrag fa on t_apos.id_apos = fa.f_id_apos;

where fa.f_id_apos has a foreign key (FK_FA_APOS) on table t_apos ,
where id_apos is the primary key.
(id_fertigungsauftrag is the primary key on t_fertigungsauftrag)
t_apos.f_nr and fa_.f_nr are varchar fields, both with indexes.

The following query is a very slow
select * from v_dms v where v.nr = '2007-0194231-1'.

it has this plan PLAN JOIN (V T_APOS NATURAL, V FA INDEX (FK_FA_APOS))
so it scans all rows from t_apos and all rows t_fertigungsauftrag.
Is there any chance to get the result faster ?
(beside the possiblility to query each field alone with 'and' connected)

mfg
Carsten