Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL statement question. please help ...

Re: SQL statement question. please help ...

From: Alan Hopkins <ahop_at_CYBERIA.COM>
Date: Tue, 14 Sep 1999 17:08:39 -0400
Message-ID: <7rmdg3$frh$1@winter.news.rcn.net>


Wont' this be the best performer?

SELECT DECODE(SIGN(500-domain_size), -- if domain

                                    -1,                                     
    -- greater 500
                                    'TOO MANY',                       --
then
                                    domain_size                       --
else
                                  )
FROM   (SELECT COUNT(DISTINCT(aaa)) AS domain_size
                FROM      t_a
               )

In this example, I would think the parser would resolve the query and then just to the conditional. If you put COUNT(DISTINCT) in two places in the DECODE, won't the parser (even thought the result set - the answer - is in cache) have to determine the answer by an additional iteration of parsing? Maybe not and it's really identical. Either way, the difference is probably micro seconds I'm sure. No additional physical IO or even grouping (distinct) would have to be done either way.

I think sub selects are more readable anyway. Does anyone know if there's an inherent penalty that would make the other options presented more efficient?

Jimmy <c6635500_at_comp.polyu.edu.hk> wrote in message news:37DF247A.2C5DEC07_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 - 16:08:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US