Home » RDBMS Server » Performance Tuning » alternative for DISTINCT (Oracle9i)
alternative for DISTINCT [message #345169] Tue, 02 September 2008 08:30 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I have a query like below

select count( distinct a.doc_id) 
from tbl001 a, tabl002 
where <condition>



Will it work if I replace by

select count(*) from 
(select count(  a.doc_id) from 
tbl001 a, tabl002 where <condition> 
group by a.doc_id)


Please let me know if there is any concern.
Regards,
Oli
Re: alternative for DISTINCT [message #345170 is a reply to message #345169] Tue, 02 September 2008 08:31 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Why would you want to do this? You want a distinct count? use count(distinct)
Tools for the job.
Re: alternative for DISTINCT [message #345171 is a reply to message #345169] Tue, 02 September 2008 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Please let me know if there is any concern.

There is no concern here for the result set or response time for your SQL.
Re: alternative for DISTINCT [message #345173 is a reply to message #345170] Tue, 02 September 2008 08:38 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
pablolee wrote on Tue, 02 September 2008 08:31
Why would you want to do this? You want a distinct count? use count(distinct)
Tools for the job.




I have a query that taking long time to get output.The response time is too high. I did checked replacing DISTINCT and response time is comparatively so less.


What I want is I want an alternative for DISTINCT clause for the below query

select count( distinct a.doc_id) 
from tbl001 a, tabl002 
where a.file_id=b.file_id 
 and a.destination_id not in('ALBM')..
..



Regards,
Oli
[/code]



[Updated on: Tue, 02 September 2008 08:47]

Report message to a moderator

Re: alternative for DISTINCT [message #345176 is a reply to message #345173] Tue, 02 September 2008 08:51 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
I'm not sure if using analytics will help, but i tried this:


SQL> set timing on
SQL> select count(distinct object_type) cnt
  2  from all_objects;

       CNT
----------
        27

Elapsed: 00:00:03.03
SQL>
SQL> select count(*)
  2  from (
  3          select row_number()over(partition by object_type order by object_type) r
  4          from all_objects
  5       )
  6  where r = 1;

  COUNT(*)
----------
        27

Elapsed: 00:00:04.00



it seems it took more time than the distinct, or maybe it's a case to case, depends on your inner query. also maybe the response time was affected by the whole sequence of returning the "sorted" & "unsorted" data to the client (as explained by Michel)
Re: alternative for DISTINCT [message #345185 is a reply to message #345169] Tue, 02 September 2008 09:22 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the response
Can I use using GROUP BY clause? any concern if I change the query like this. I am getting less response time when I replace distinct and use group by

select count(*) from 
(select count(  a.doc_id) from 
tbl001 a, tabl002 where <condition> 
group by a.doc_id)


Regards,
Oli

[Updated on: Tue, 02 September 2008 09:22]

Report message to a moderator

Re: alternative for DISTINCT [message #345272 is a reply to message #345185] Tue, 02 September 2008 15:07 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK Oli, you've been here a while now. Your question is pretty much all about performance. When you want to compare the performance of one statement against the other, what should you really compare. The timing of one statement straight after another when both statements are designed to provide the same results, doesn't give us anything anywhere NEAR a full picture. What should you be looking at to compare these two statements. Other than one statement actually being faster than another, what reasons could there be, that the second query might execute faster than the first?
Do you think, that maybe, it might be more useful to post more than just a daft wee bit of pseudo code? Post your actual session (you have been asked to do this kind of thing, for this kind of post several times that I recall)

Final point, when you want to do a count distinct of a column, why do you think that Oracle would provide a count(distinct ...) function and then go and create another faster method of performing that exact same task?
Re: alternative for DISTINCT [message #345512 is a reply to message #345272] Wed, 03 September 2008 22:25 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle internally treats a DISTINCT the same way it treats GROUP BY, so you cannot make it faster - but depending on the complexity of the rewrite - you could make it slower.

There are 2 things you can do to improve the performance of a COUNT DISTINCT:
  • Index the column. You would have to include all other columns in the query though.
  • Bitmap index the column. This is the absolute fastest way to get a COUNT DISTINCT, but it is inappropriate for some tables/systems, and will only work if the query references NO other columns.


Ross Leishman
Previous Topic: Not Exists Vs Join
Next Topic: Which one is to choose?
Goto Forum:
  


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

Total time taken to generate the page: 0.09171 seconds