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 -> Re: Oracle8 Partitioning

Re: Oracle8 Partitioning

From: Andrew Babb <andrewb_at_mail.com>
Date: Fri, 23 Apr 1999 09:10:02 +0800
Message-ID: <371FC86A.BF974A99@mail.com>


Moorthy,

It looks like you want to use the exchange partition option. When you exchange a partition you exchange it with a table, which is the same definition of the partition table. Also, the Local indexes on the partition are marked unusable, but remain with the partition, and any global index is marked unusable as well.

You can then load the data into the table, that used to be the partition, using SQL*Loader, and when the load is complete you can exchange the table back into the partition. The indexes will still be unusable, so you will need to issue the 'rebuild unusable local index' option (not sure if it is an alter table or index).

e.g.

Describe part_table

Name                         Not Null?    Type
---------------------------- ------------ --------
r_date                       NOT NULL     date
....

Describe part_table_stub

Name                         Not Null?    Type
---------------------------- ------------ --------
r_date                       NOT NULL     date
....
  1. TRUNCATE TABLE PART_TABLE_STUB
  2. ALTER TABLE PART_TABLE EXCHANGE PARTITION JAN_1999 for PART_TABLE_STUB;
  3. Do your SQL*Load with the Target table being PART_TABLE_STUB;
  4. ALTER TABLE PART_TABLE EXCHANGE PARTITION JAN_1999 for PART_TABLE_STUB;
  5. ALTER TABLE PART_TABLE REBUILD UNUSABLE LOCAL INDEXES;
Syntax maybe wrong, but this is the logic. Andrew

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
Received on Thu Apr 22 1999 - 20:10:02 CDT

Original text of this message

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