Hi to all.
Still after some hours of try, I didn't manage to overcome the problem of a
very bad performed query (you will find it below)
I should be very grateful for any idea, suggestion or explanation.
(Windows2000 Pro - SS firebird 1.5.2 )

Thanks in advance anybody who will reply for his time.

Regards
Tasos Kyriakos
Athens, Greece
<mailto:tkyr@...> tkyr@...


------------------------------- QUERY ----------------------------
Select * from T_CASES
where case_id in
(
select distinct(case_ID) from T_cases
and case_id>0
/* ------------here follow the dynamically created criteria */
and T_CASES.case_id between 19 and 30
/* -------------------------------------------- end of criteria */
)
CASE_ID is the primary key of T_CASES

The above is a simplified example but still doesn't function efficiently.
T_CASES table has 75529 records. When I run the above query, it executes
151058 accesses of table T_CASES (twice the number of its rows)
Is there any way that the join between subquery and external select be done
using the primary key and not traversing all the table?

--------------the created plan
----------------------------------------------------
PLAN SORT ((T_CASES INDEX (RDB$PRIMARY20)))
PLAN (T_CASES NATURAL)

Adapted Plan
PLAN SORT ((T_CASES INDEX (PK_CASEID))) PLAN (T_CASES NATURAL)
------------------------------------------------------ end of plan
-------------------


[Non-text portions of this message have been removed]