Hello Carsten,

> 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)

Given that you're creating your own resultcolumn values, any queries that
ONLY use this column cannot use an index on that column.

How do you expect this to go any faster?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com