Home » SQL & PL/SQL » SQL & PL/SQL » Problem with a select distinct (Oracle 9i)
Problem with a select distinct [message #427452] Thu, 22 October 2009 12:43 Go to next message
Nau
Messages: 24
Registered: October 2004
Junior Member
I have a problem with a select distinct, I have the next sql:

select distinct a,b,c,d from (select a,b,c,d,e,f...from table where ...)

The subselect have 25000 rows, it last about 10 seconds, but the select distinct over the subselect takes a long time..., I have waited for 20 minutes, and finally canceled the sql.

When I make this alternative path:

create table2 as (select a,b,c,d,e,f...from table where ...)
select distinct a,b,c,d from table2

The distinct finish in 5 seconds.

I don't understand the differences.... I can't do the alternative path, I need the first sql to work.
I don''t know where is the problem

Any advice will be greatly appreciatted
Re: Problem with a select distinct [message #427453 is a reply to message #427452] Thu, 22 October 2009 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

post EXPLAIN PLAN for both fast & slow SQL.
Re: Problem with a select distinct [message #427454 is a reply to message #427452] Thu, 22 October 2009 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing can be said with what you posted.
Please read OraFAQ Forum Guide and posted the usual and required information.

Regards
Michel
Re: Problem with a select distinct [message #427466 is a reply to message #427452] Thu, 22 October 2009 14:02 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
This is your 22nd post.You should know how to put your question.

[code]
select distinct a,b,c,d from (select a,b,c,d,e,f...from table where ...)[code]

Why you need another level of sub select when u can do like this
select distinct a,b,c,d...from table where ...


Re: Problem with a select distinct [message #427494 is a reply to message #427452] Thu, 22 October 2009 23:12 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Your concern however is correct. A select distinct over 25 thousand rows should take only a second or two.

It is possible that the optimizer is rewriting the query badly. Look at the query plan for the subselect run alone. then compare that to its counter part in the full query. This will give you a quick idea if a rewrite has change the plan.

Kevin
Re: Problem with a select distinct [message #427509 is a reply to message #427452] Fri, 23 October 2009 00:20 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nau wrote on Thu, 22 October 2009 19:43
I have a problem with a select distinct, I have the next sql:

select distinct a,b,c,d from (select a,b,c,d,e,f...from table where ...)

The subselect have 25000 rows, it last about 10 seconds, but the select distinct over the subselect takes a long time..., I have waited for 20 minutes, and finally canceled the sql.

Did it take 10 seconds to retrieve ALL rows, or did you only saw a subset of it (if you are using TOAD, PL/SQL Developer or SQL Developer, it is the latter).
Previous Topic: Code Review
Next Topic: Reading CSV file Using UTL_FILE
Goto Forum:
  


Current Time: Sun Dec 04 10:32:54 CST 2016

Total time taken to generate the page: 0.12914 seconds