Re: Table Partitions

From: Jay Hostetter <jhostetter_at_decommunications.com>
Date: Wed, 27 Dec 2000 09:06:31 -0500
Message-Id: <10723.125351@fatcity.com>

Q1: If you were previously using partitioned views, then your individual = tables already contain segregated data. You can do this: a) create the partitioned table.
b) exchange the partitions with the existing tables.

example:

CREATE TABLE CALL_RECORDS
(

    STRUCTURE_CODE                NUMBER(5)              NOT NULL,
    CALL_CODE                     NUMBER(3)              NOT NULL,
    CALL_DATE                     DATE                   NOT NULL

)
TABLESPACE CALL_DATA
PCTFREE 0 PCTUSED 95
STORAGE (INITIAL 2) /* use a small size. These will really be empty = after the exchanges are complete */
PARTITION BY RANGE (CALL_DATE)
(PARTITION CALL_MAY99 VALUES LESS THAN (TO_DATE('01-JUN-1999','DD-MON-YYYY=
')),

  PARTITION CALL_JUN99 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY=
')),

  PARTITION CALL_JUL99 VALUES LESS THAN (TO_DATE('01-AUG-1999','DD-MON-YYYY=
')),

  PARTITION CALL_AUG99 VALUES LESS THAN (TO_DATE('01-SEP-1999','DD-MON-YYYY=
')),

  PARTITION CALL_SEP99 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY=
')),

  PARTITION CALL_OCT99 VALUES LESS THAN (TO_DATE('01-NOV-1999','DD-MON-YYYY=
')),

  PARTITION CALL_NOV99 VALUES LESS THAN (TO_DATE('01-DEC-1999','DD-MON-YYYY=
')),

  PARTITION CALL_DEC99 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY=
')),

  PARTITION CALL_JAN00 VALUES LESS THAN (TO_DATE('01-FEB-2000','DD-MON-YYYY=
')),

  PARTITION CALL_FEB00 VALUES LESS THAN (TO_DATE('01-MAR-2000','DD-MON-YYYY=
')),

  PARTITION CALL_MAR00 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY=
')),

  PARTITION CALL_APR00 VALUES LESS THAN (TO_DATE('01-MAY-2000','DD-MON-YYYY= ')))
/

ALTER TABLE CALL_RECORDS

      EXCHANGE PARTITION CALL_JAN00
          WITH TABLE     CALL_RECORDS_1 WITH VALIDATION;
ALTER TABLE CALL_RECORDS
      EXCHANGE PARTITION CALL_FEB00
          WITH TABLE     CALL_RECORDS_2 WITH VALIDATION;

etc.

Now your previously existing tables have become the partitions. You can = go back and drop the previously existing tables, which are now empty (they = were exchanged with the small partitions that were created with your = partitioned table).
If your existing tables do not have the data segregated, create the = partitioned table and do an INSERT INTO new_table (SELECT * FROM old_table)= .=20

Q2: We are using ADVFS file systems on Tru64 4.0d. Performance is fine, = but I don't have any specific numbers to give to you. I never compared it = to raw.

Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA USA

>>> VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com> 12/26/00 10:05AM >>>

Qs 1. After Migrating an Oracle Database from 7 to 8.1.6 How can the Data ALREADY EXISTING in the Tables be split into Different Partitions with minimal effort ?=20

Qs 2 Performance-wise How does a Cluster (TruUnix of Digital Compaq on = ADVFS
file systems) compare with RAW File Systems ? This for Both OLTP & Batch Processing Transactions for a Banking Product Received on Wed Dec 27 2000 - 08:06:31 CST

Re: Table Partitions - A Basic Qs