A big hello to all experienced users !

I have a big problem - Plan optimizer makes a bad plan on very simple
Query's that use IS NULL or IS NOT NULL in where clauses.

Unfortunately i have a big application running since
3 Years with around 80 tables and about 400 different Selects
that are dynamically built upon the user interface.
(The user can change ORDER, and FILTER Functionality resulting in
changed
SQL's)

Here is a Select and what it does: (Using the Join .. on ... Syntax
does not
change anything)

CASE A)
----------
select a.f1,a.f2,b.f1,b.f2,c.f1,c.f2 from t1 a, t2 b, t3 c
where a.id=b.t1id and b.id =c.t2id
and a.f1 is null and a.f2 is not null
-----------

This Select needs with large Tables (60000 Records per Table) around
100 Seconds to perform - becaues the PLAN from the optimizer is:
-> PLAN JOIN (C NATURAL,B INDEX (PK_T2),A INDEX (T1_IDX1,PK_T1))

Wich is BAD.

CASE B) Using this Select:
-------------------
select a.f1,a.f2,b.f1,b.f2,c.f1,c.f2 from t1 a, t2 b, t3 c
where a.id=b.t1id and b.id =c.t2id
and a.f1 = 2223 and a.f2 is not null
----------------------------------
The result is done under one Second !!
Plan is:
-> PLAN JOIN (A INDEX (T1_IDX1),B INDEX (FK_T2),C INDEX (FK_T3))

CASE C) Now doing this:

select a.f1,a.f2,b.f1,b.f2,c.f1,c.f2 from t1 a, t2 b, t3 c
where a.id=b.t1id and b.id =c.t2id
and a.f1 is null and a.f2 is not null
PLAN JOIN (A INDEX (T1_IDX1),B INDEX (FK_T2),C INDEX (FK_T3))

The Same Result as in CASE A) is obtainend in under one Second !

To Speedup the Application i have IMHO the following choices:

1) Use a Plan on my Selects ->
Does not work because the SELECTS are generated Dynamically on
the GUI
(Dynamicall Order by and Where Clauses) -> so one Plan does not
fit and
not work.

2) Do not use NULLS in all TABLES. (Substitute NULL to -1 for INTS,
and to
1.1.1899 for DATES ... and so on)
-> 2 Month's work and a BAD Solution.

3) Try to Retrieve a PLAN from the engine for a SELECT with NULLS
Substituted to Values and used the RETRIEVED
PLAN for the SELECT with the NULL Tests.
Maybe a Solution / Workaround but with very unpredictable side
effects.

4) Hope that this BUG? is fixed in a future Version.

Does anyone have
(but please no trivial answers like don't use FK's or dont use
NULL :-))
Ideas, how to avoid, fix this behaviour ?
Or does anyone now how to get the PLAN for a given Select ?

thanks a lot,

Helmut


For reference, the test database without ROW Values:

SET SQL DIALECT 3;
SET NAMES NONE;
CREATE DATABASE 'C:\BAD.GDB'
USER 'SYSDBA'
PAGE_SIZE 1024
DEFAULT CHARACTER SET NONE;
CREATE TABLE T1 (
ID INTEGER NOT NULL,
F1 INTEGER,
F2 INTEGER
);
CREATE TABLE T2 (
ID INTEGER NOT NULL,
T1ID INTEGER,
F1 INTEGER,
F2 INTEGER
);
CREATE TABLE T3 (
ID INTEGER NOT NULL,
T2ID INTEGER,
F1 INTEGER,
F2 INTEGER
);
COMMIT WORK;

ALTER TABLE T1 ADD CONSTRAINT PK_T1 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT PK_T2 PRIMARY KEY (ID);
ALTER TABLE T3 ADD CONSTRAINT PK_T3 PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT FK_T2 FOREIGN KEY (T1ID) REFERENCES T1
(ID);
ALTER TABLE T3 ADD CONSTRAINT FK_T3 FOREIGN KEY (T2ID) REFERENCES T2
(ID);
CREATE INDEX T1_IDX1 ON T1 (F1);
CREATE INDEX T1_IDX2 ON T1 (F2);