Re: SQL tuning

On 10 Nov 2005 12:10:44 -0800, "yuxe2000_at_gmail.com" <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

If you want help, you need to disclose all details, and you shouldn't assume and decide anything yourself ('The indexes look fine to me on both tables ' *WHAT INDEXES*!!!)
You should also include version information, the statements EXPLAIN PLAN, info on the optimizer being used, and whether statistics are current.
Right now the best answer anyone can give to your question is: Read the performance tuning manual, especially the chapters on CBO.

--
Sybrand Bakker, Senior Oracle DBA