Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Partitions for relatively small tables

Partitions for relatively small tables

From: viktor <vikingil_at_yahoo.com>
Date: 26 Jun 2003 03:32:23 -0700
Message-ID: <946871c0.0306260232.5700bfd0@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...

Thanks,
Viktor Received on Thu Jun 26 2003 - 05:32:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US