Re: question about distinct and how to speed up query

Unique is exactly the same as distinct, which are both exactly the same as a group by clause. Actually I've meet developers and other Oracle people who hate using Distinct and will only use group by clauses. The first time I say UNIQUE was with MS Access..... It's an ANSI SQL thing, I believe. Why do you have the brackets around KEYCATNUMBER, BTW? Oh well.

A faster way? I'm pretty sure distinct will just do the columns you asked for, and of course if there's an index on those columns, all the better, but don't quote me on that. Beside what do you expect with a quarter of a million rows of querying?

Good Luck
Lyall Barbour

At 01:31 PM 8/16/00 -0800, you wrote:
>Hi:
>
>Someone just gave me a query:
>
>select unique(KEYCATNUMBER) as CatID, KEYCATDESCRIPTION as CatDesc
>from HPXUDBIRIUPCDICT;
>
>I run it and it worked on oracle db. As a test, I did
>
>select distinct(KEYCATNUMBER) as CatID, KEYCATDESCRIPTION as CatDesc
>from HPXUDBIRIUPCDICT;
>
>and it produced the same result set.
>
>So,
>
>Question 1: I have never used "unique", is it = distinct?
>
>Also the above query is slow (took about 20 seconds). The table
>"HPXUDBIRIUPCDICT" has a quarter million records. I already create index
>on KEYCATNUMBER.
>
>KEYCATNUMBER VARCHAR2(4)
>KEYCATDESCRIPTION VARCHAR2(24)
>
>Question2: Is there a better way, short of creating a new table, for me to
>get a list of of the unique KEYCATNUMBERs? Would Distinct (or Unique) in
>the sql query force a full table scan in this case?
>
>Thanks.
>
>Guang
>________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
>--
>Author: Guang Mei
> INET: zlmei_at_hotmail.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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 Wed Aug 16 2000 - 16:38:06 CDT