Peter wrote:
> 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
>

Post the plan (explain plan...)
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...