select count(distinct)
I'm working with a table that has 4 columns, all of them which are integer(10). There are about 13 million records in the table. I'm trying to determine the number of rows with distinct values for col1. The table is indexed by col1.
If I do select count(distinct col1) from table, the query takes between 50 minutes to an hour to run.
I also tried a work-around which which is select sum(one), (select (1) as one from table group by col1) as j1 This query produced the same results as the first one, but took only 30 seconds to run!!! (I hope the syntax is correct, I left the exact syntax at work, but this should give the general idea?)
Has anyone experienced the same problems with select count(distinct)? Does anyone know why my fix is so much faster? If anyone wants to try my fix and compare the results with doing select count(distinct), please go ahead. I'm interested to know if this is a fluke or something that makes a difference.
Subject
Views
Written By
Posted
select count(distinct)
96789
July 05, 2005 07:23PM
28628
July 05, 2005 07:37PM
22262
July 06, 2005 05:44AM
19155
July 07, 2005 06:13AM
17125
July 07, 2005 09:11AM
17146
July 07, 2005 10:48AM
9883
July 07, 2005 10:51AM
8716
July 08, 2005 05:27AM
9812
July 08, 2005 05:48AM
8013
August 19, 2005 05:22AM
8881
July 06, 2005 02:11AM
8196
July 06, 2005 05:33AM
7209
July 06, 2005 06:13PM
7162
July 07, 2005 05:14AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.