Re: DISTINCT and IN problem

Gregor Retti (Gregor.Retti_at_uibk.ac.at) wrote:

: we are about to install a oracle-db for our journals archiv. the tests
: are
: running on oracle server for macintosh 1.0 (v6), frontends on oracle
: for
: macintosh 2.0. the final installation should use v7 on novell.
 

: there are 4 "data"-tables joined by 4 "relation"-tables, all of the
: type 1 -
: mc / mc - 1. selecting data does work with simple joins over the
: relation-tables, but it is not too fast by now. we tried subqueries and
: the
: result was very fast performance, but the combination of DISTINCT and
: IN did
: slow down performance remarkably.
 

: FAST
 
: select count(nr1) from data1,rel where data1.nr1=rel.nr1 and
: rel.nr2 IN (select data2.nr2 from data2 where <something>);
 

: VERY SLOW
 
: select count(DISTINCT nr1) from data1,rel where data1.nr1=rel.nr1 and
: rel.nr2 IN (select data2.nr2 from data2 where <something>);
 

: FAST, but only useable for 1 row returned by the subquery.
 

: select count(DISTINCT nr1) from data1,rel where data1.nr1=rel.nr1 and
: rel.nr2 = (select data2.nr2 from data2 where <something>);
 

: any suggestions? thanks in advance

Since you seem to be testing for the existance of something in table data2, try using the EXISTS function:

select count(distinct nr1) from data1, rel where data1.nr1 = rel.nr1
and EXISTS (select 'x' from data2
  where <something> and data2.nr2 = rel.nr2);

-- 
  +---------------------------------------------------------------------+
  |  Phillip Huber   Analyst/Programmer   Michigan Technological Univ.  |
  |  Internet: phil_at_mtu.edu    Phone: 906-487-2223    Fax: 906-487-2787 |
  +---------------------------------------------------------------------+