Home » SQL & PL/SQL » SQL & PL/SQL » Unique list without using DISTINCT
Unique list without using DISTINCT [message #36236] Mon, 12 November 2001 12:31 Go to next message
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

----------------------------------------------------------------------
Re: Unique list without using DISTINCT [message #36237 is a reply to message #36236] Mon, 12 November 2001 12:40 Go to previous messageGo to next message
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

----------------------------------------------------------------------
Re: Unique list without using DISTINCT [message #36259 is a reply to message #36236] Tue, 13 November 2001 09:52 Go to previous messageGo to next message
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.

----------------------------------------------------------------------
Re: Unique list without using DISTINCT [message #36266 is a reply to message #36259] Tue, 13 November 2001 22:52 Go to previous message
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

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


Current Time: Thu Mar 28 14:38:23 CDT 2024