Re: SQL tuning

On Thu, 10 Nov 2005 12:10:44 -0800, yuxe2000_at_gmail.com wrote:

>
> The indexes look fine to me on both tables. What can I do to tune the
> SQL?
Cut down on the number of blocks processed. If I am understanding you correctly, you are trying to process orders for 6 months and the report runs "more then 20 seconds". You apparently don't work for Halliburton. I see here index range scan, sort with (group by) and a lot of other things. The first thing to do is to design it a bit better: 1) Have a customer total table filled in by a trigger. That will eliminate

   group by, sums and filtering.
2) Partition orders by month, so that index range scan becomes partition

   pruning.
3) Partition customers by region, so that region lookup results in the

   partition elimination.

-- 
http://www.mgogala.com