Re: Perfomance with Partitioning

From: Frank <fbortel_at_nescape.net>
Date: Sun, 14 Dec 2003 14:45:36 +0100
Message-ID: <brhp18$fij$1@news2.tilbu1.nb.home.nl>

Can wrote:
> Hi,
>
> Thanks for your response ;o)
>
>
>

>>You can define a partition 52 right now. That's in the future.

>
>
> I see...that is to say, I'd now have to define 52 partitions (I guess I'll
> write a script creating the query, otherwise I'll be sitting here an hour
> writing it).
>
>
>

>>There are some traps re partitioning - make sure you pick
>>the right method of partitioning, or you cannot add partitions
>>later (unless you rebuild the table)

>
>
> Alright, I'll take that into account...
>
>
>

>>BTW - wouldn't an index help? You problem suggests FTS are performed
>>while only retrieving a small portion of the table.

>
>
> Well, there are a bunch of indexes on that table, and truth to tell, the
> query in question takes about 1.5 seconds but still: it used to take 0.2
> seconds 2 months ago. And the performance is perpetually decreasing as the
> table keeps growing (growing speed: approximately 1500 rows per day -
> growing speed is also turning faster).
>
>

>>Version (and OS) info are missing.

>
> oh right, the environment...
> Windows XP - Orace 9.2.0.1
>
> Thanks again ;o)
>
> Kind regards,
> Can
>
>

Hmmm - updated statistics, lately?
Saw the other thread where you were playing around with hints. Updated stats may help. Especially on the index.

If the same index on date (week#) was used, the number of index blocks to visit would hardly change; today would be the same amount (approx.) of records to check/filter than last month (only the frame begin and end points would differ, not the frame length).

Any tkprof/explain plan going on?

-- 
Regards, Frank van Bortel

Received on Sun Dec 14 2003 - 07:45:36 CST