-
Partition a table
Dear all,
I have an old and non-partitioned table with couple hundred millions of rows. Now we need to partition the table to increase performance. Is there a way that I can add partition to the table without recreating it? I tried this and got error:
alter table test add partition by range (DATE)
(partition DEC04 values less than ('01-JAN-05'),
partition JUN05 values less than ('01-JUL-05'));
alter table test add partition by range (DATE)
*
ERROR at line 1:
ORA-00902: invalid datatype
I know that Oracle will allow me to add partitions to existing partitioned table, but what about a non-partitioned table?
Any help will be appreciated.
Thanks,
Unna
-
I know that Oracle will allow me to add partitions to existing partitioned table, but what about a non-partitioned table?
Nope.
You need to create partitioned table first if you want to add another partition.
Tamil
-
I'll add that I'm not a fan of that partition naming scheme -- personally I've found it useful for partition names to be alphanumerically ordered the same as the ranges they contain. eg. "Y2004M01", "Y2005M12" etc.
-
Tamil,
Thank you for your reply. I also found from asktom that I can convert a nonpartitioned table into a partition (or subpartition) of a partitioned table by exchanging their data segments, by using "alter table t exchange partition par1 with table t1 without validation". But it doesn't seem to work in my case. The best way to go with it now is to create a new table with partition, insert data from the original table, drop it, then rename the new table to the original table.
Thanks again,
Unna.
-
I have done the exchange partition/table several times in several versions what do you mean it doesnt work?
Errors?
-
I tried this:
create table t (b_dt date not null)
partition by range (b_dt)
(partition oct04 values less than (to_date('01-NOV-2004','DD-MON-YY')),
partition jun05 values less than (to_date('01-JUL-2005','DD-MON-YY')) );
insert into t
exchange partition oct04
with table test
without validation;
SQL> select count(*) from t;
COUNT(*)
----------
20
SQL> select b_dt, count(*) from t
group by b_dt;
B_DT COUNT(*)
--------- ----------
01-OCT-04 10
01-JUN-05 10
SQL> select count(*) from t partition (oct04);
COUNT(*)
----------
20
SQL> select count(*) from t partition (jun05);
COUNT(*)
----------
0
insert into t
exchange partition jun05
with table test
without validation;
With this partition, I didn't get partition for both OCT04 and JUN05; however if I have "insert into t select * from test", I got the data partitioned:
SQL> select count(*) from t partition (oct04);
COUNT(*)
----------
10
SQL> select count(*) from t partition (jun05);
COUNT(*)
----------
10
I am not sure what I have done wrong with the alter...exchange partion... clause. If I am going to the wrong direction, can you please help me the right way to do that?
Thanks,
Unna
-
Demonstration script
Code:
drop table t1;
drop table t2;
create table t1
(
col1 date
)
partition by range (col1)
(partition Y2005M01 values less than (date '2005-02-01'),
partition Y2005M02 values less than (date '2005-03-01'))
/
create table t2
(
col1 date
)
/
set feedback off
insert into t1 values (date '2005-01-01');
insert into t1 values (date '2005-01-02');
insert into t1 values (date '2005-01-03');
insert into t1 values (date '2005-02-01');
insert into t1 values (date '2005-02-02');
insert into t1 values (date '2005-02-03');
insert into t2 values (date '2005-01-06');
insert into t2 values (date '2005-01-05');
insert into t2 values (date '2005-01-04');
select * from t1;
select * from t2;
select * from t1 partition (Y2005M01);
alter table t1
exchange partition Y2005M02
with table t2
with validation
/
alter table t1
exchange partition Y2005M01
with table t2
with validation
/
select * from t2;
select * from t1 partition (Y2005M01);
... and here is its execution ...
Code:
SQL> drop table t1;
SQL> drop table t2;
SQL>
SQL> create table t1
2 (
3 col1 date
4 )
5 partition by range (col1)
6 (partition Y2005M01 values less than (date '2005-02-01'),
7 partition Y2005M02 values less than (date '2005-03-01'))
8 /
SQL>
SQL> create table t2
2 (
3 col1 date
4 )
5 /
SQL>
SQL> set feedback off
SQL>
SQL> insert into t1 values (date '2005-01-01');
SQL> insert into t1 values (date '2005-01-02');
SQL> insert into t1 values (date '2005-01-03');
SQL>
SQL> insert into t1 values (date '2005-02-01');
SQL> insert into t1 values (date '2005-02-02');
SQL> insert into t1 values (date '2005-02-03');
SQL>
SQL> insert into t2 values (date '2005-01-06');
SQL> insert into t2 values (date '2005-01-05');
SQL> insert into t2 values (date '2005-01-04');
SQL>
SQL> select * from t1;
COL1
---------
01-JAN-05
02-JAN-05
03-JAN-05
01-FEB-05
02-FEB-05
03-FEB-05
SQL>
SQL> select * from t2;
COL1
---------
06-JAN-05
05-JAN-05
04-JAN-05
SQL>
SQL> select * from t1 partition (Y2005M01);
COL1
---------
01-JAN-05
02-JAN-05
03-JAN-05
SQL>
SQL> alter table t1
2 exchange partition Y2005M02
3 with table t2
4 with validation
5 /
with table t2
*
ERROR at line 3:
ORA-14099: all rows in table do not qualify for specified partition
SQL>
SQL> alter table t1
2 exchange partition Y2005M01
3 with table t2
4 with validation
5 /
SQL>
SQL> select * from t2;
COL1
---------
01-JAN-05
02-JAN-05
03-JAN-05
SQL>
SQL> select * from t1 partition (Y2005M01);
COL1
---------
06-JAN-05
05-JAN-05
04-JAN-05
-
tried this:
create table t (b_dt date not null)
partition by range (b_dt)
(partition oct04 values less than (to_date('01-NOV-2004','DD-MON-YY')),
partition jun05 values less than (to_date('01-JUL-2005','DD-MON-YY')) );
Confused with
(to_date('01-JUL-2005','DD-MON-YY'))
2005 with YY
Tamil
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
|