Re: sql question -- distinct, group by and order by

Guang Mei wrote:
>
> Hi:
>
> I have a basic sql question about sql. I have the follwing four sqls and I
> am wondering why #3 "costs" less than #4 in explain plan. #1 and #2 cost
> the same. How is "distinct"and "group by" treated internally by Oracle? Is
> #3 a better "optimized" sql than #4?
>
> TIA.
>
> Guang Mei
> --------
> 1. select id,NAME from project group by id,name ;
> 2. select distinct id,name from project ;
> 3. select id,NAME from project group by id,name order by id;
> 4. select distinct id,name from project order by id;
>
> MT_at_atlas-SQL> desc project;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ID NOT NULL NUMBER
> NAME NOT NULL VARCHAR2(128)
>
> MT_at_atlas-SQL> select distinct id,name from project ;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216
> )
>
> 1 0 SORT (UNIQUE) (Cost=54 Card=12 Bytes=216)
> 2 1 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
> 16)
>
> COMMONNAME NOT NULL VARCHAR2(16)
> MNEMONIC NOT NULL CHAR(1)
> USE NOT NULL CHAR(1)
>
> MT_at_atlas-SQL> select id,NAME from project group by id,name ;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216
> )
>
> 1 0 SORT (GROUP BY) (Cost=54 Card=12 Bytes=216)
> 2 1 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
> 16)
>
> MT_at_atlas-SQL> select id,NAME from project group by id,name order by id;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=12 Bytes=216
> )
>
> 1 0 SORT (GROUP BY) (Cost=54 Card=12 Bytes=216)
> 2 1 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
> 16)
>
> MT_at_atlas-SQL> select distinct id,name from project order by id;
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=107 Card=12 Bytes=21
> 6)
>
> 1 0 SORT (UNIQUE) (Cost=54 Card=12 Bytes=216)
> 2 1 TABLE ACCESS (FULL) OF 'PROJECT' (Cost=1 Card=12 Bytes=2
> 16)
>

Mei,

I would disregard 'cost'; this is just what a more or less educated guess in some algorithm coded by some developer. Elapsed time is real (if nobody is computing finite elements or gzip'ing a few terabytes on your machine while you are testing), and, other things being equal, go for what requires the fewer buffer gets. Otherwise the plan answers your question - noticed 'SORT' ? What is in between parentheses only indicates what triggered the very same processing.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).