sql question -- distinct, group by and order by

From: Guang Mei <zlmei_at_hotmail.com>
Date: Fri, 07 Feb 2003 12:59:16 -0800
Message-ID: <F001.005479B2.20030207125916@fatcity.com>

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)











_________________________________________________________________

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: zlmei_at_hotmail.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).

Received on Fri Feb 07 2003 - 14:59:16 CST