DISTINCT and IN problem
Date: 22 Sep 1993 08:48:53 GMT
Message-ID: <27p3hl$a6a_at_news.uibk.ac.at>
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.
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 Received on Wed Sep 22 1993 - 10:48:53 CEST