Re: Striping Tables

Leslie Carter wrote:
>
> I have a very large tablespace with about 40 existing tables all in 1
> datafile. These tables are heavily accessed, and I would like to split
> up the tablespace in 3 datafiles residing on different disks in order to
> increase performance. I plan to export the tables drop the tablespace.
> Recreate it with multiple data files. But how can I manage the
> placement of the tables in the different data files?
>
> I can use the alter database command and bring the datafiles offline to
> make them unavailable as the tables are imported, but I prefer to stripe
> the data, have each table spread across the datafiles.
>
> I saw how I could stripe data across the datafiles in the
> Administrator's Guide, but this would only work for 1 table per set of
> datafiles. That would be a major pain.
>
> Any ideas?

Your options are limited. You can only specify datafiles in the ALTER TABLE ALLOCATE EXTENT command, not in the CREATE TABLE command. You could fake it out:

Say you had three datafiles, each 10M+1 block (so 10M free and available in the tablespace after giving up the datafile header block).

Datafile 1:  10M
Datafile 2:  10M
Datafile 3:  10M

Create Table1, storage initial 1M, next 1M, pctinc 0. It'll go into Datafile 1.

Now, fill the rest of Datafile 1 with a dummy table: create table dummy_table ... storage (initial 9M, next 1M pctinc 0)

Now your datafiles look like:

Datafile 1:  (filled)
Datafile 2:  10M free
Datafile 3:  10M free

The next object you create will go into Datafile 2. Lather, rinse, repeat. When you've got objects in 2 and 3, drop the dummy table, create another in Datafile 1, and repeat.

This all gets timeconsuming. It is simpler to strip the data at the operating system level. Then, your main concern is separating objects across RAID sets, not across individual disks.

See Also: Advanced Oracle Tuning & Admin, Chapter 4.

Kevin.
Rollback: One of the moves in the Macarena. Received on Tue Nov 26 1996 - 00:00:00 CET