SQL tuning

From: <yuxe2000_at_gmail.com>
Date: 10 Nov 2005 12:10:44 -0800
Message-ID: <1131653444.498382.240360@g47g2000cwa.googlegroups.com>

Hi,

 I need to join two huge tables in Oracle to generate a sales report:

Table 1: Customer (5 million records)

    Cust_ID (index)
    Cust_Name
    Region_id (index)
    Cust_type (index)

...

Table 2: Order (more than 60 million records)

     Order_ID
     Cust_ID   (index)
     Order_Type  (index)
     Order_Month (index)
     Order_Amount

 ...

My SQL:

SELECT a.cust_id, a.cust_name,
  SUM (b.order_amount),
FROM customer a, order b,
WHERE a.cust_id =b.cust_id

AND b.order_month between 1 and 6
AND b.order_type = 10
AND a.region_id ='01'

HAVING SUM(b. order_amount) BETWEEN '100' AND '10000' GROUP BY a.cust_id, a.cust_name
Order by a.cust_id

The query runs very slow (more than 20 seconds) even when only a few thousand records returned. It can run 10 mins if I don't add region id in where clause.

The indexes look fine to me on both tables. What can I do to tune the SQL? Thanks,

Ben Received on Thu Nov 10 2005 - 14:10:44 CST