Hi all,

I have the following strange effect:

The statement:

select <some values> from <some tables> where <some join statements>
and <some conditions> and value1a in (select value1b from table1 where
<some conditions>)

has a process time of more than 3 minutes. (tables are quite small,
indices are properly set).

The same statement with "select distinct" instead of "select":

select distinct <some values> from <some tables> where <some join
statements> and <some conditions> and value1a in (select distinct
value1b from table1 where <some conditions>)

only needs 1 second.

The usage of "select distinct" doesn't change the result of the
statement, because the primary keys of all used tables are in all
subexpressions and conditions. Therefore the select distinct should
also have no effect concerning the processing time.

I have no clue what happens. It seems like the database (Oracle 8i)
optimizes the "select distinct" query but doesn't optimize the "select"
query.

Any idea?

Thank you for your help

Peter