DISTINCT and IN problem

From: Gregor Retti <Gregor.Retti_at_uibk.ac.at>
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.

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 Received on Wed Sep 22 1993 - 10:48:53 CEST

Original text of this message