Re: Tablespaces and indexes

On 21 Sep 2001 12:15:25 -0500, Galen Boyer <galenboyer_at_hotpop.com> wrote:

>
>I asked this question as a standalone post but it is relevant
>here. Are there performance gains with partitioning when there
>is striping? I guess with multiple sets of arrays, partitioning
>to a certain array would give performance gains, but if using one
>striped array, I don't see partitioning bringing performance
>gains.
>

Partitioning is useful to spread (or apportion) the load in a very large table. This load can be of data movement nature or it can be related to maintenance tasks. It all depends. Generally, the idea with partitioning is to localise specific types of load so that you can control them better without affecting the rest of the data in the table.

For example: you can partition to make the inserts all go to a single partition (the "current data" one), while major summary queries are spread over the other partitions. Or you can partition to simplify a delete of older data (as in "drop the partition to get rid of data more than x months old"). And so on.

Assume for example the following situation: billing system, high volume of arrival of billing information. If you create a partition every week (or even every day!) to contain all the latest inserted rows, you can optimize this one in terms of disk allocation to get the maximum speed of writes. Older partitions, used more for getting summary information for accounting purposes, can be moved to devices (logical or physical) that favour reads. Once the data loses its currency, because the partitions localise it by date of arrival, it's a simple matter of backing up the oldest partition and then dropping it. As opposed to a mass delete of potentially millions of rows.

And I've only touched the subject here. The possibilities are tremendous.

>Why does this work? I thought mirroring was a second layer of
>safety. Does Oracle use both sides of the mirror based on its
>intelligence?

You can actually have two read operations in parallel, one to each side of the mirror (assuming a two disk mirror), each directed to different parts of the "logical" disk drive. In essence, you double the concurrent read capacity of your hardware.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Sat Sep 22 2001 - 09:23:24 CDT