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@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) >
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: [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).
