sql question -- distinct, group by and order by

[Guang Mei]

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@atlas-SQL> desc project;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)

MT@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@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@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@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)











_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).