SQL tuning

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

.

Relevant Pages

  • Re: SQL tuning
    ... > I need to join two huge tables in Oracle to generate a sales report: ... >Table 1: Customer ... >My SQL: ... Sybrand Bakker, Senior Oracle DBA ...
    (comp.databases.oracle.server)
  • Re: Too many WHERE conditions?
    ... You might want get an SQL expert to check it out, ... I have no access to the customer ... database or their setup. ... has it had all the patches applied (that Oracle issue)? ...
    (microsoft.public.data.ado)
  • Re: How to convince my customer to use SQL2005 instaed of Oracle10g
    ... Sounds like the customer wants what the customer wants... ... Although SQL ... particular application which makes them want to use Oracle instead of SQL ... Server as well. ...
    (microsoft.public.sqlserver)
  • Re: How to convince my customer to use SQL2005 instaed of Oracle10g
    ... I need to convince my customer to use SQL2005, but after google, it ... seem like most of the people say oracle is better than SQL ...
    (microsoft.public.sqlserver)
  • Re: Oracle 10G Grid Control
    ... listing of what an Oracle product included and what was optional. ... Why should a customer have to dig for basic information to determine ... I certainly hope neither of you are ever in sales. ... My job as purchaser simple ... ...
    (comp.databases.oracle.server)
Loading