Douglas Hawthorne wrote:
> "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
Douglas ... not sure when you arrived but thanks for making such
excellent contributions to the group.
But wait a second ... what happened to Howard in my absence?
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Sun Mar 28 2004 - 19:59:43 CST
