Re: partitioned data

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Mon, 29 Mar 2004 00:15:32 GMT
Message-ID: <EUJ9c.128947$Wa.16448@news-server.bigpond.net.au>

"Cupofjava1961" <cupofjava1961_at_aol.com> wrote in message news:20040328182006.17332.00000227_at_mb-m23.aol.com...
> So, are you saying that the data will be swapped and exist in two places,
> redundantly?

Maybe an example would help to clarify matters:

I created a simple partitioned table of two (2) partitions with an identification of the original source of the data: CREATE TABLE part_table1

   (

      part_id NUMBER(1),
      part_text VARCHAR2(40)

   )
   PARTITION BY RANGE ( part_id )
   (

      PARTITION part_1
         VALUES LESS THAN ( 5 ),
      PARTITION part_2
         VALUES LESS THAN ( 9 )

   )
;

INSERT INTO part_table1

   SELECT

         ROWNUM,
         'Original table'
      FROM
         all_objects
      WHERE
         rownum < 9

;

SELECT * FROM part_table1;

   PART_ID PART_TEXT
---------- --------------

         1 Original table
         2 Original table
         3 Original table
         4 Original table
         5 Original table
         6 Original table
         7 Original table
         8 Original table

8 rows selected.

Now, I create another (unpartitioned) table that has the same structure as the partitioned table, and populate that table with different data:

CREATE TABLE outside_tab

   AS SELECT *

      FROM part_table1
      WHERE 1=0

/

INSERT INTO outside_tab

   SELECT

         0,
         'Outside Table'
      FROM
         all_objects
      WHERE
         rownum <= 20

/

SELECT * FROM outside_tab;

   PART_ID PART_TEXT
---------- -------------

         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table

20 rows selected.

Now to exchange the first partition with the outside table:

ALTER TABLE part_table1 EXCHANGE PARTITION part_1 WITH TABLE outside_tab;

SELECT * FROM part_table1;

   PART_ID PART_TEXT
---------- --------------

         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         0 Outside Table
         5 Original table
         6 Original table
         7 Original table
         8 Original table

24 rows selected.

SELECT * FROM outside_tab;

   PART_ID PART_TEXT
---------- --------------

         1 Original table
         2 Original table
         3 Original table
         4 Original table

As you can see, the data still exists in one place but in different places.

Douglas Hawthorne Received on Sun Mar 28 2004 - 18:15:32 CST