| alternative for DISTINCT [message #345169] |
Tue, 02 September 2008 08:30  |
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 #345173 is a reply to message #345170] |
Tue, 02 September 2008 08:38  |
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 |
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 |
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 |
pablolee
Messages: 2882
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
