Re: count distinct

From: FlameDance <FlameDance_at_gmx.de>
Date: Thu, 24 Jun 2004 08:19:04 +0200
Message-ID: <cbdroo$1g5$06$1@news.t-online.com>

Romeo Olympia schrieb:

Cheers Romeo, that's it!

The explain plan shows there's indeed only one full table scan in this solution:

3 SELECT STATEMENT

2	SORT [GROUP BY]
1		META.T_2 EXTERNAL TABLE ACCESS [FULL]

Thanks! I had the feeling there had to be a better solution than mine, it just looked too clumsy. ;-)

Stephan

> select count(distinct column1), count(distinct column2), 
> count(distinct column3), count(distinct column4)
> from mytable;
> 
> FlameDance <FlameDance_at_gmx.de> wrote in message news:<cbcupc$o15$03$1_at_news.t-online.com>...
> 

>>Hi everyone,
>>
>>I need to know the number of distinct values of 4 columns of the same table.
>>For one column I'd use this statement:
>>
>>select count (*) from (select distinct column1 from mytable);
>>
>>This requires a full table scan. For 4 columns I could issue this
>>command 4 times, using a different column each time. That would amount
>>to 4 time consuming full table scans.
>>
>>select count (*) from (select distinct column1 from mytable);
>>select count (*) from (select distinct column2 from mytable);
>>select count (*) from (select distinct column3 from mytable);
>>select count (*) from (select distinct column4 from mytable);
>>
>>Is there a way to do this faster, ideally with just one full table scan?
>>
>>Stephan
Received on Thu Jun 24 2004 - 01:19:04 CDT