Re: query dilemma

From: David Fitzjarrell <oratune_at_aol.com>
Date: Fri, 26 Jan 2001 16:42:49 GMT
Message-ID: <94s9e6$v36$1@nnrp1.deja.com>

In our last gripping episode Cristian Veronesi <c.veronesi_at_crpa.it> wrote:
> I have two tables (Oracle 7.3.4) :
>
> Farms (PK: farm_code)
> Checks (PK: farm_code+check_date+cow_id)
>
> I have to perform a query like this:
>
> select farms.farm_town,
> trunc(check_date,'MONTH') as check_month,
> ??? as farm_size,
> count(*)
> from farms, checks
> where farms.farm_code =3D checks.farm_code
> group by farms.farm_town, trunc(check_date,'MONTH'), [farm_size] ;
>
> The problem is that the only way to determine the farm size at a
> particular date is to count the number of cows checked in that date. I
> then wrote a stored function getFarmSize...
>
> function getFarmSize (
> farm_code_in in varchar2,
> check_date_in in varchar2
> ) return varchar2 is
> cow_count integer ;
> begin
> select count(*)
> into cow_count
> from checks
> where farm_code =3D farm_code_in
> and check_date =3D check_date_in ;
> if cow_count between 1 and 50 then
> return 'BIG' ;
> elsif cow_count between 51 and 100 then
> return 'MEDIUM' ;
> elsif cow_count > 100 then
> return 'SMALL' ;
> end ;
> end ;
>
> =2E.. and included it in the query this way:
>
> select farms.farm_town,
> trunc(check_date,'MONTH') as check_month,
> getFarmSize(checks.farm_code,check_date) as farm_size,
> count(*)
> from farms, checks
> where farms.farm_code =3D checks.farm_code ;
> group by farms.farm_town, trunc(check_date,'MONTH'),
> getFarmSize(checks.farm_code,check_date) ;
>
> It works, but obviously the performance is very bad. Do you have some
> ideas about to do the same thing with a better performance?
>
> TIA, Cris
>
> -- =
>
> Cristian Veronesi ><((((=BA> http://www.crpa.it
>
> If Bill Gates had a dime for every time a Windows box crashed...
> Oh, wait a minute... he _already_ does! ;)
>

You could try an in-line view with decode, but that would take much more work to implement as you would need to code for every possibility, up to 255, in the decode statement, and I don't think it would improve performance:

 select farms.farm_town,
 trunc(check_date,'MONTH') as check_month,  farm_size,
 count(*)
 from farms, checks,

 (  select decode(count(*), 1, 'SMALL',
                            2, 'SMALL',
                            3, 'SMALL',
                            4, 'SMALL',
                             ...) farm_size

    from checks
    where farm_code = checks.farm_code
    and check_date = check_month)
 where farms.farm_code = checks.farm_code  group by farms.farm_town, trunc(check_date,'MONTH'),  farm_size ;

I can't, off the top of my hairy cranium, think of another way. Of course, I've been wrong before ...

And I wonder about your size assignments -- somehow I can't quite understand that 1 cow makes a BIG farm and over 100 cows make a SMALL farm...

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/

Received on Fri Jan 26 2001 - 10:42:49 CST