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)
96574
July 05, 2005 07:23PM
28556
July 05, 2005 07:37PM
22204
July 06, 2005 05:44AM
19093
July 07, 2005 06:13AM
17065
July 07, 2005 09:11AM
17065
July 07, 2005 10:48AM
9819
July 07, 2005 10:51AM
8666
July 08, 2005 05:27AM
9750
July 08, 2005 05:48AM
7956
August 19, 2005 05:22AM
8815
July 06, 2005 02:11AM
8127
July 06, 2005 05:33AM
7136
July 06, 2005 06:13PM
7102
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.