-
Exchanging the partition
Hi,
We have one partition table which has got 30 partitions as days of month.This is a MIS report related table. We pull data from several other database into external tables.then we will pull data in temp table from external table. Then we will exchange the temp table with partition of that day.(easiest way and fast also). Now my question is. If one partition contains some data and i exchanged it with temp table what will happen to data in partition.will it append data or replace fully.
logically i think exchanging includes only data dict updates so it will fully replace. i searched on google didnt find anything convincing.
Thanks in advance
Rgds
Parag
-
Hi Parag
Code:
CREATE TABLE table2000
( x int,
y int,
z DATE
)
PARTITION BY RANGE (z)
(
PARTITION tab_1999_h1 VALUES LESS
THAN(to_date('30-jun-1999','dd-mon-yyyy')),
PARTITION tab_1999_h2 VALUES LESS
THAN(to_date('31-dec-1999','dd-mon-yyyy')),
PARTITION tab_2000_h1 VALUES LESS
THAN(to_date('30-jun-2000','dd-mon-yyyy')),
PARTITION tab_2000_h2 VALUES LESS
THAN(to_date('31-dec-2000','dd-mon-yyyy'))
)
/
Table created.
insert into table2000 values ( 1, 1, '15-jun-1999' );
insert into table2000 values ( 2, 2, '15-dec-1999' );
insert into table2000 values ( 3, 3, '15-jun-2000' );
insert into table2000 values ( 4, 4, '15-dec-2000' );
CREATE TABLE new_table2000
( x int,
y int,
z DATE
)
PARTITION BY RANGE (z)
(
PARTITION newtab_1999_h1 VALUES LESS
THAN(to_date('30-jun-1999','dd-mon-yyyy')),
PARTITION newtab_1999_h2 VALUES LESS
THAN(to_date('31-dec-1999','dd-mon-yyyy'))
)
/
Table created.
insert into NEW_table2000 values ( -1, -1, '15-jun-1999' );
insert into NEW_table2000 values ( -2, -2, '15-dec-1999' );
create table temp ( x int, y int, z date );
Table created.
alter table new_table2000
exchange partition newtab_1999_h1
with table temp;
Table altered.
alter table table2000
exchange partition tab_1999_h1
with table temp;
Table altered.
rename temp to table2000_tab_1999_h1;
Table renamed.
select * from table2000;
X Y Z
---------- ---------- ---------
-1 -1 15-JUN-99
2 2 15-DEC-99
3 3 15-JUN-00
4 4 15-DEC-00
so the old data is actually overwritten not appended.
regards
Hrishy
-
Hrishikesh,
Thanks man i got what happens now . actually what i was thinking was correct that data will not get append it will replace tha data. Actually we dont have test env here. So i was unable to try and i dont want trial and error on PROD db. but thanks man.
First thing i will do now is i will make small test env. so that i can do R&D like this .
Rgds
Parag
-
Hi Parag
Just curious how did you get my complete name :-)
regards
Hrishy
-
hrishi,
It was a just wild guess +
Its common and widely used shortform for Hrishikesh
in Maharashatra. (Like mike for michael in foreign countries)
I dont know from which state you are. Anyways thanks man
Rgds
Parag
-
Hi Parag
Hmm i see you are good at the guessing game..ever tried your fortune at stock markets :-).You will be surprised how many millions you can make.
regards
Hrishy
-
Hrishi,
I dont have much knowledge on share market. just have seen mumbai stock exchange building from outside. and i dont make guesses which will cost my wallet .but still good point to give thought to.I am from pune and currently in coimbatore.
Rgds
Parag
-
Originally posted by hrishy
Hi Parag
Hmm i see you are good at the guessing game..ever tried your fortune at stock markets :-).You will be surprised how many millions you can make.
regards
Hrishy
Buy CKCM, got in at 13, its now threatening 25. Will go to 100.
RFID super stock, gonna have a 10 year run.
http://clearstation.etrade.com/cgi-b...on.etrade.com/
It fell to 8 on false rumors and earnings were tripled so it zoomed from 8 to 22 in a week.
I hung on and rode it out.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|