Re: Query CRAWLS!!!

Try moving rental_agreement into a subquery using the exists operator. i.e.:

select c.division_name, count(distinct a.customer_id) from lookup_station_location b, lookup_division_station c where exists

    (select 'x'
    from rental_agreement a
    where a.checkout_station_code = b.station_number) and b.division_number = c.division_number group by c.division_name

hth
Chris

Asif Khan wrote:

> We have an Oracle 8i server running on a Solaris Sparc E-4500 box(8
> 400mhz processors, 4 gig RAM, 12x36 gig HD).
>
> A simple query with a couple of joins like the one below takes 15
> minutes to run!!
>
> Another interesting thing is that the box is eating into 400 megs of
> SWAP space. It already has 4 gigs of RAM, and I see no reason why it
> should need SWAP.
> There are only 2 people using the server at the moment.
>
> Anyone have any suggestions???
>
> Here's the query:
> =======================================================
> select c.division_name, count(distinct a.customer_id)
> from rental_agreement a,
> lookup_station_location b,
> lookup_division_station c
> where b.division_number = c.division_number
> and a.checkout_station_code = b.station_number
> group by c.division_name
>
> ========================================================
> More info:
> a)rental_agreement has 16 million records.
> b)lookup_station_location has 10,000 records.
> c)lookup_division_location has 10 records.
>
> There are indexes on:
> a) lookup_station_location.division_number
> b) lookup_division_location.division_number
> c) rental_agreement.checkout_station_code
> d) lookup_station_location.station_number
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Jul 15 1999 - 08:15:10 CDT