Hello Tasos,

TK> ------------------------------- QUERY ----------------------------
TK> Select * from T_CASES
TK> where case_id in
TK> (
TK> select distinct(case_ID) from T_cases
and case_id>0
Did you forget WHERE clause here?

TK> /* ------------here follow the dynamically created criteria */
TK> and T_CASES.case_id between 19 and 30
TK> /* -------------------------------------------- end of criteria */
TK> )
TK> CASE_ID is the primary key of T_CASES
Why do you need internal SELECT at all? Try
select * from T_CASES
where case_id>0
and case_id between :minID and :maxID /* your parameters */
Since case_id is PK, there is no need to select distinct values.

TK> The above is a simplified example but still doesn't function efficiently.
TK> T_CASES table has 75529 records. When I run the above query, it executes
TK> 151058 accesses of table T_CASES (twice the number of its rows)
TK> Is there any way that the join between subquery and external select be done
TK> using the primary key and not traversing all the table?
Instead of WHERE VALUE IN (SELECT...) construction it's often more
efficient to use WHERE EXISTS (SELECT 1 FROM...) or joins. But as I
can understand, it is not for this case.


HTH
--
Best regards,
Pavel Menshchikov
http://www.ls-software.com