Re: SQL tuning
- From: Sybrand Bakker <postbus@xxxxxxxxxxxxxxxxx>
- Date: Thu, 10 Nov 2005 22:03:21 +0100
On 10 Nov 2005 12:10:44 -0800, "yuxe2000@xxxxxxxxx"
<yuxe2000@xxxxxxxxx> 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
.
- References:
- SQL tuning
- From: yuxe2000@xxxxxxxxx
- SQL tuning
- Prev by Date: Re: Why does Oracle 9i show 10/30/05 with a d or s after the time and what does the XXFF time format mean?
- Next by Date: Re: Installing 8i in Linux RH 3.0 or SLES 9
- Previous by thread: SQL tuning
- Next by thread: Re: SQL tuning
- Index(es):
Relevant Pages
|
Loading
