Re: Oracle8 Partitioning

Moorthy:

Don't bother with exchanging partitions. It's too much work. Just load your data using SQL*Loader in DIRECT mode. It will slap the data into the database and then rebuild all the indexes for that partition at the end. No need to disable indexes or use EXCHANGE. Just make sure your data is clean (no duplicates) or the index rebuild will fail and you'll have to truncate the partition and start over.

When you add a new partition for a new month, first of all change the default tablespace for each index as such: alter index INDEX1 modify default attributes tablespace NEW_IDX1_PARTITION; alter index INDEX2 modify default attributes tablespace NEW_IDX2_PARTITION; ... and so on for each index on the table.

Now when you add the partition, each new index will get put in its proper tablespace.

Angie

In article <7fnsc4$jv$1_at_nnrp1.dejanews.com>,   mrekapalli_at_yahoo.com wrote:
> Hello There,
>
> We are using Solaris 2.6 and Oracle 8.0.5 for a Data Warehouse
> application.
>
> I have a few questions on table and index partitioning.
>
> We are planning to create a partitioned table and the partitioning key
> will be a date.
>
> Based on this assumption, data that belongs to January 1999 will be in
> Jan_1999 partition.
>
> We will have some local indexes created on this table.
>
> When we get February data, we will add a new partition, Feb_1999.
> Also, whenever we add a new partition to a table, all the local indexes
> will be automatically created for that new table partition.
>
> If indexes exist, data load might a take long time. So, is there any
> way that we can
>
> a) drop the index partition
> b) load data
> c) then, re-create the index partition?
>
> Also, when adding a new partition to a table, how do I specify the
> tablespace clause for the local indexes that belong to the new table
> partition? These local index segments are created in the default
> tablespace.
>
> I was also thinking of loading the data into a seperate table and then
> use "alter table exchange partition". In the documentation, they gave
> an example on how to make a partition of a table into a non-partitioned
> table. I could not figure out how to make a non-partitioned table into
> a partition.
>
> If you know how to use "alter table exchange partition"
> (OR) if you have any suggestions to modify/improve the design, please
> send me an email.
>
> THANKS in advance.
>
> -- Moorthy
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri May 07 1999 - 08:24:30 CDT