OraFAQ Forum: SQL & PL/SQL » Problem with a select distinct

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with a select distinct (Oracle 9i)

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

Problem with a select distinct [message #427452]

Thu, 22 October 2009 12:43

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

Report message to a moderator

Re: Problem with a select distinct [message #427453 is a reply to message #427452]

Thu, 22 October 2009 12:53

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Problem with a select distinct [message #427454 is a reply to message #427452]

Thu, 22 October 2009 12:54

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Problem with a select distinct [message #427466 is a reply to message #427452]

Thu, 22 October 2009 14:02

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 ...

Report message to a moderator

Re: Problem with a select distinct [message #427494 is a reply to message #427452]

Thu, 22 October 2009 23:12

Kevin Meade
Messages: 2103
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

Report message to a moderator

Re: Problem with a select distinct [message #427509 is a reply to message #427452]

Fri, 23 October 2009 00:20

Frank
Messages: 7901
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).

Report message to a moderator

Previous Topic:	Code Review
Next Topic:	Reading CSV file Using UTL_FILE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 01:10:53 CST 2024