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)
96864
July 05, 2005 07:23PM
28653
July 05, 2005 07:37PM
22283
July 06, 2005 05:44AM
19180
July 07, 2005 06:13AM
17147
July 07, 2005 09:11AM
17174
July 07, 2005 10:48AM
9900
July 07, 2005 10:51AM
8735
July 08, 2005 05:27AM
9834
July 08, 2005 05:48AM
8037
August 19, 2005 05:22AM
8901
July 06, 2005 02:11AM
8215
July 06, 2005 05:33AM
7232
July 06, 2005 06:13PM
7184
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.