Partition a table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Partition a table

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    160

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  3. #3
    Join Date
    Nov 2000
    Posts
    160
    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.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    I have done the exchange partition/table several times in several versions what do you mean it doesnt work?

    Errors?

  5. #5
    Join Date
    Nov 2000
    Posts
    160
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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
  •  



Click Here to Expand Forum to Full Width