OraFAQ Forum: SQL & PL/SQL » Unique list without using DISTINCT

Home » SQL & PL/SQL » SQL & PL/SQL » Unique list without using DISTINCT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unique list without using DISTINCT [message #36236]

Mon, 12 November 2001 12:31

Alex Ponce
Messages: 4
Registered: November 2001

Junior Member

I have a table that contains over two million records. How can I get a unique list of a foreign key column without using the DISTINCT clause. Even though the foreign key column is indexed, it still takes too much time.
I have another table that has the unique value for this foreign key. Can I join them in some way that has better performance.

Thanks to all.

AP

----------------------------------------------------------------------

Report message to a moderator

Re: Unique list without using DISTINCT [message #36237 is a reply to message #36236]

Mon, 12 November 2001 12:40

hello
Messages: 17
Registered: November 2001

Junior Member

U can use "UNIQUE" clause instead of "DISTINCT" which usually takes very less time compared to distinct.

select unique x from dummy

instead of

select distinct x from dummy

----------------------------------------------------------------------

Report message to a moderator

Re: Unique list without using DISTINCT [message #36259 is a reply to message #36236]

Tue, 13 November 2001 09:52

andrew again
Messages: 2577
Registered: March 2000

Senior Member

If the FK is sparse it would be more efficient to get the oprimizer to only access the Index and not the Table. Try various explain plans (set autotrace on; in sqlplus). If you only select the indexed FK columns and order by those columns in the order in which they occur in the index, you shou;ld be able to get it to access only the index.

----------------------------------------------------------------------

Report message to a moderator

Re: Unique list without using DISTINCT [message #36266 is a reply to message #36259]

Tue, 13 November 2001 22:52

Rob Baillie
Messages: 33
Registered: November 2001

Member

Or, or course, you could HINT the index.

SELECT /*+ INDEX( table index ) */
FROM ...

See http://otn.oracle.com/doc/server.804/a58225/ch2.htm#8482

----------------------------------------------------------------------

Report message to a moderator

Previous Topic:	Insert statement incorrect
Next Topic:	Multiple Inline Queries - Urgent

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 16 01:05:06 CDT 2026