query dilemma

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 = 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 = farm code in
  and check date = 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 ;

... 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 = 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 ><(((( > http://www.crpa.it

If Bill Gates had a dime for every time a Windows box crashed...
Oh, wait a minute... he  already  does! ;)