question about distinct and how to speed up query

From: Guang Mei <zlmei_at_hotmail.com>
Date: Wed, 16 Aug 2000 20:27:38 GMT
Message-Id: <10591.114776@fatcity.com>

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 Received on Wed Aug 16 2000 - 15:27:38 CDT