Re: SQL statement question. please help ...

From: Kevin Gillins <kevin_gillins_at_compuserve.com>
Date: Tue, 14 Sep 1999 20:09:16 -0600
Message-ID: <7rmo8c$j6e$1@ssauraab-i-1.production.compuserve.com>

The issue here is not the number of scans but rather that the scan will return all rows from the table even if there were 5 million then perform the count in a sort temp segment. You may want to look at the having clause of a query.

select count(distinct(AAA)) from table
having count(distinct(AAA)) <= 500;

--

Kevin Gillins

  Jimmy <c6635500_at_comp.polyu.edu.hk> wrote in message news:37DF24FF.F76D532D_at_comp.polyu.edu.hk...   Hello all,

      I want to do a query by following requirements:

      I want to retrieve the distinct value of AAA of table T_A if the   count(distinct(AAA)) of this SQL statmenet is smaller than 500,   otherwise returns 'TOO MANY';

      i.e. select distinct(AAA) from T_A if count(distinct(AAA)) <= 500;
           select 'TOO MANY' from T_A if count(distinct(AAA)) > 500;

      I don't know how to write a SQL statement to do the above

  requirements. Is is possible to do this by one SQL statement? (and this   SQL statment has the shortest response time and AAA may or may not the   index of the table T_A)

      Does Oracle needs two table scan in using one SQL statement to do   the above query? First, scan the table T_A and get the   count(distinct(AAA)). If <=500, then scan the table again and retrieve   the distinct(AAA), am I right?

  Thanks,
  Jimmy Received on Tue Sep 14 1999 - 21:09:16 CDT