Re: Partitions for relatively small tables

vikingil_at_yahoo.com (viktor) wrote in message news:<946871c0.0306260232.5700bfd0_at_posting.google.com>...
> Hi,
> We currently have 14 tables one per day for period of 2 weeks.
> We have a program that collects about 5,000 records for this table
> every half hour.
> So the table holds about 250,000 records when it is full.
> In order to load the data I use SQL*Loader in direct path load.
> I understand that using partitions will improve the performance of the
> load
> and I can just perform something like
> " ALTER TABLE transaction_data EXCHANGE PARTITION
> transaction_data00785 WITH TABLE transaction_data_stage INCLUDING
> INDEXES;"
> to "glue" the new inserted data as one of my partitions.
>
> The performance will increase because my data file is sorted by the
> index so I can use SORTED INDEXES in each load. This way Oracle will
> never need to merge the indexes.
> I also will be able to use UNRECOVERABLE mode of SQL*Loader and in
> case of a failure during the load just truncate the partition and
> reload the data.
> I will probably need to use 672 partitions in the table ("14(days) *
> 48 (periods) = 672").
>
> 1. Is this recommended to use this many partitions on tables of this
> size (is it a good practice)?
> 2. I also referring to working with the table after the load issues
> like performing selects and removing data...

If I understand you correctly, you are inserting 5000 rows each half hour and want to keep them for two weeks. You're asking if you should put each 5000 in its own partition. Answer is NO. I think that you should use simply inserts. You probably wouldn't get any performance gains with 5000 rows in partition.
Also I can't understand your performance issues, on any decent system you must be able load 5000 rows in no time and your system seems to me like very low loaded one. Have you encountered any real performance issue or you're just trying to improve just for improvement?

--
Dusan Bolek