Re: SQL tuning

yuxe2000_at_gmail.com wrote:
> 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

This looks remarkably like school work but assuming it is not here's what is required for posting here and getting help.

And make sure you have run current statistics for the optimizer with DBMS_STATS.

1. Oracle version number
2. Information on what indexes exist
3. Explain Plan or Autotrace output
4. The number of rows in each table
5. The % of rows in months 1 - 6
6. The % of rows in region '01'

Still I'm betting this is school work as I can't imagine anyone wanting to group by order amounts between 100 and 10000 in the real world.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)