Hello Tasos,

TK> Trying to use model with exists, i run the query 2. This finished with
TK> 151058 reads from T_CASES (has totally 75529 records) and returns the
TK> (correct) 3 records of V_CASES_GRID.
TK> This is EXACTLY the same performence and result with mine (Query 1)

TK> i used to believe that only 6 reads from T_CASES should be enough giving
TK> that records with id 19,20 and 21 exist and that yp_id is (primary) indexed.
TK> Maybe I am wrong but where?

TK> Just for giving a better idea i enclose (as query 3) a more expanded form
TK> of the query, believing that this time I haven't misspelled something.

TK> /*---------------- QUERY 1 ----------------*/
TK> select * from V_CASES_GRID vc
TK> where vc.YP_ID in
TK> (
TK> select distinct(ty.YP_ID) from T_CASES ty
You don't need distinct here: ty.YP_ID is PK (as you mentioned) and it
has no correlation with the view - it's always unique.

TK> where
TK> ty.yp_id between 19 and 21
TK> )
TK> order by vc.yp_id
TK> /*-----------------------*/

TK> /*---------------- QUERY 2 ----------------*/
TK> select VC.* from V_CASES_GRID VC
TK> where
TK> exists
TK> (
TK> select 1 from T_CASES yp
TK> where vc.yp_id=yp.yp_id
TK> and
TK> yp.yp_id between 19 and 21
TK> order by yp.yp_id
You don't need ORDER BY, since EXISTS returns TRUE if the first
existing row is encountered (it doesn't process all rows for internal
select).

TK> )
TK> order by vc.yp_id
TK> /*-----------------------*/

TK> /*---------------- QUERY 3 ----------------*/
TK> select * from V_CASES_GRID vc
TK> where vc.YP_ID in
TK> (
TK> select distinct(ty.YP_ID) from T_CASES ty
TK> join T_SYNALL on sy_id=ty.yp_anti /* opponents */
TK> join t_crossyp on cry_yp =ty.yp_id /* users */
TK> where
TK> SY_ID=9655 /* opponent's id */
TK> and CRY_Person = 24 /* user's id */
You forgot the alias here.

TK> and ty.yp_id between 1 and 1000
TK> and ty.yp_descr like '%KEYWORD%'
TK> )
TK> order by vc.yp_id
TK> /*-----------------------*/

Tasos, could you post your V_CASES_GRID definition?


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