Re: How does DISTINCT work?

Oleg Roshchin <oleg_at_ca-ib.com> wrote in message news:7pgcop$86f$1_at_news.netway.at...
> Hi, all!
>
> How does DISTINCT work: during the selecting or after it?

after

> What is more efficient: DISTINCT or GROUP BY
> when GROUP BY is used for all fields in the query?

DISTINCT takes a "sort (unique)" option, which is less cost. GROUP BY takes a "sort (group by)" option, So, DISTINCT is more efficient, and more readable. See the following script:

SQL> select distinct ename, job, sal from emp; Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=364)    1 0 SORT (UNIQUE) (Cost=3 Card=14 Bytes=364)    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=364)

SQL> select ename, job, sal from emp group by ename, job, sal; Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=560)    1 0 SORT (GROUP BY) (Cost=3 Card=14 Bytes=560)    2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=560) Received on Thu Aug 19 1999 - 11:22:50 CDT