Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Why is "select distinct" faster than "select" ?

Why is "select distinct" faster than "select" ?

From: Peter <moreps_at_gmx.net>
Date: 18 Feb 2006 00:34:47 -0800
Message-ID: <1140251687.765848.60170@g47g2000cwa.googlegroups.com>


 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 Received on Sat Feb 18 2006 - 02:34:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US