Partitioning Issues
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Partitioning Issues

  1. #1
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    Hi! All,
    I have a table whoose structure is
    *COMPANYID NOT NULL NUMBER(20)
    *FINANCIALYEAR NOT NULL NUMBER(4)
    *TLSDATAPOINTCODE NOT NULL VARCHAR2(20)
    QUARTER NUMBER(2)
    NUMBERVALUE NUMBER(20,5)
    STRINGVALUE VARCHAR2(4000)
    DATEVALUE DATE
    BOOLEANVALUE NUMBER(1)
    CREATEDBY NUMBER
    CREATEDDATE DATE
    MODIFIEDBY NUMBER
    MODIFIEDDATE DATE
    MANUALMODIFICATIONFLAG NUMBER(1)
    *- PK
    The table has 7005892 rows in it currently and it is going to triple in comming 3 months.
    I want to partition the table on Financialyear.
    1) Can I partition the table with data?
    2) What are the pros and cons of partitioning on financialyear which not the left most column of the PK

    There Nothing You cannot Do, The problem is HOW.

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    Hi,

    As far as I know you can't partition a table that already contains data. Create another temporary table with the partitons you require from the original. Then delete the original and rename the temporary...

    eg :

    create table newtable partition by range(column1)
    (partition p1 values less than (50),
    partition p2 values less than (maxvalue))
    as (select from original table);

    ensure newtable has been created correctly......
    if so then drop original table and rename the newtable to the original tablename.

    You may need to recreate any constraints etc...

    Cheers

    Moff.

  3. #3
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    What abt Alter TABLE MOVE ??? command. We can move partitions can we? If we can do that then we can also move a non partitoned table into a partition.

    There Nothing You cannot Do, The problem is HOW.

  4. #4
    Join Date
    Feb 2000
    Posts
    175
    Hi

    I don't think you can move a table into a partition. The move clause of the alter table statement allows you to move to a table to a new tablespace or change the storage parameters for existing data.

    Does anyone else have any thoughts on the best way to partition an already populated table?

    Cheers

    Moff.


  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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