Re: Oracle 8i and poor query performance in some queries

> Created two tables and put test data into them. Both tables are in the
> same locally managed tablespace where allocation unit is uniform 1 MB.
> Table a1 has 15 columns and 35294 rows. Table a2 has 14 columns and
> 83134 rows. Tables have no indexes. Analyzed both tables using
> "analyze table compute statistcs", analyze of a1 took 4 seconds,
> analyze of a2 took 8 seconds. Table a1 is the master table and has a
> column "id". Each row has its own value for id so the result of
> "select count(distinct id) from a1;" is 35294. Table a2 is the detail
> table so every id in a2 is found in a1 and for every id in a1 there is
> 1 to n rows in table a2.

Why don't you use an index on id's columns ?? It increases perfomance i bet.

> Launched a simple test query "select count(*) from a1 where id not in
> (select id from a2);". I might think that this should be finished in
> matter of some seconds. But it does not... Well, finally the query
> finishes and provides a correct result (0). But it took 33 minutes 37
> seconds to execute!
>

It looks like that subquery might be run 35294 times...each time full scaning a2...

Does:
 select id from a1
 minus
select id from a2;
takes so much time, eigher??

--
--
Tomasz Balcerek
Softman S.A
tomekb_at_softman.com.pl