Hi Svein,

> Christian,
> please show us table, index and view definitions (you may
> remove irrelevant
> columns) as well as the sql in question and an approximate number of
> records in each table/view.

Okay, here they are:

this query joins two tables, runs fast and generates this
plan: PLAN SORT (JOIN (B NATURAL, P INDEX(RDB$PRIMARY16))).

SELECT B.NUMMER
, B.OBJEKT
, B.MENGE
, B.GESAMT_WERT
, B.LIEFERTERMIN
, B.BESTELL_DATUM
, B.DRINGEND
, B.BESTELLER
, B.STATUS
, P.PROJOBNR
, P.PROJOBNAME
FROM T_BESTELLUNGEN B
LEFT OUTER JOIN T_Projekte P
ON B.JOB = P.NUMMER
ORDER BY B.NUMMER DESC
------------------------------------
This query joins a table and a view, runs slow and generates
this plan:
PLAN SORT (JOIN (B NATURAL, T_PROJEKTE NATURAL)).

This is quite strange, because the view V_BESCHR_PROJEKTE is
a simple view for T_Projekte which is used in the first query.

SELECT B.NUMMER
, B.OBJEKT
, B.MENGE
, B.GESAMT_WERT
, B.LIEFERTERMIN
, B.BESTELL_DATUM
, B.DRINGEND
, B.BESTELLER
, B.STATUS
, P.JOBNR
, P.JOBNAME
FROM T_BESTELLUNGEN B
LEFT OUTER JOIN V_BESCHR_PROJEKTE P
ON B.JOB = P.NUMMER
ORDER BY B.NUMMER DESC
------------------------------------
/* Table: T_PROJEKTE */ [4.660 records]
CREATE TABLE T_PROJEKTE (
NUMMER INTEGER NOT NULL,
PROJOBNR VARCHAR(13) CHARACTER SET NONE NOT NULL,
PROJOBNAME VARCHAR(50) CHARACTER SET NONE NOT NULL,
[snip]);

/* Unique constraints definition */
ALTER TABLE T_PROJEKTE ADD UNIQUE (PROJOBNR);

/* Primary keys definition */
ALTER TABLE T_PROJEKTE ADD PRIMARY KEY (NUMMER);

/* Indices definition */
CREATE DESCENDING INDEX IDX_PROJEKTE_JOBNUMMER_DESC ON T_PROJEKTE
(PROJOBNR);
------------------------------------
/* View: V_BESCHR_PROJEKTE */
CREATE VIEW V_BESCHR_PROJEKTE(
JOBNAME,
JOBNR,
NUMMER,
[snip])
AS
SELECT
PROJOBNAME,
PROJOBNR,
NUMMER,
[snip]
FROM T_Projekte;
------------------------------------
/* Table: T_BESTELLUNGEN */ [150 records]
CREATE TABLE T_BESTELLUNGEN (
NUMMER INTEGER NOT NULL,
OBJEKT VARCHAR(50) CHARACTER SET NONE NOT NULL,
MENGE INTEGER NOT NULL,
GESAMT_WERT NUMERIC(18,2),
LIEFERTERMIN DATE,
BESTELL_DATUM DATE,
DRINGEND VARCHAR(1) CHARACTER SET NONE DEFAULT 'N' NOT NULL,
BESTELLER USER_NAME NOT NULL,
STATUS VARCHAR(1) CHARACTER SET NONE DEFAULT 'X' NOT NULL,
[snip]);

/* Primary keys definition */
ALTER TABLE T_BESTELLUNGEN ADD PRIMARY KEY (NUMMER);

/* Indices definition */
CREATE INDEX T_BESTELLUNGEN_LIEFERANT ON T_BESTELLUNGEN (LIEFERANT);
CREATE INDEX T_BESTELLUNGEN_OBJEKT ON T_BESTELLUNGEN (OBJEKT);

This is really a lot of information, thanks to all who spend their
time looking on this :-)


Christian