DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Partitioning

  1. #1
    Join Date
    Mar 2005
    Posts
    4

    Partitioning

    I have a non-partioned table with 3 million rows, I would like to partition it by range for each day date. Each partition will be populated with 1 million rows everyday.

    My question are:

    a) Is there any way to partition the current table without recreating a new table with partitions?
    b) When the table is partioned what will the best way to create index on each partion?


    Thanks

    Ghona Panesar

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    nope, cant do that unless you had a single partitioned table whch I am doubting you did, have to create a new one and rename it after

  3. #3
    Join Date
    Mar 2005
    Posts
    4
    thanks

  4. #4
    Join Date
    Mar 2001
    Posts
    41
    I wud suggest that you user LOCAL index. Also as much as possible your index should be prefix by the partition key..

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    I wud suggest that you user LOCAL index
    I'm not sure that we can conclude that without understanding how that table is queried. Here's a couple of threads to get you started:

    http://asktom.oracle.com/pls/ask/f?p...:2320010698020

    http://asktom.oracle.com/pls/ask/f?p...14188448949723

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Would he export the table, drop it, re-create it partitioned and import?

    Would he create a partitioned index using the same date partitioning?

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by KenEwald
    Would he export the table, drop it, re-create it partitioned and import?
    He could do that. However, I'd prefer to create a partitioned table with a maxvalue partition that has the exact same structure and exchange my table into it.


    Would he create a partitioned index using the same date partitioning?
    Maybe yes, maybe no. Depends on the app.
    Jeff Hunter

  8. #8
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Would he export the table, drop it, re-create it partitioned and import?

    If I had the space I'd create a new partitioned table using a nologging create table as select, drop the old table and rename the new one. Or create a new partitioned table and do a nologging append insert into it and then drop and rename.

    Would he create a partitioned index using the same date partitioning?

    If each partition contains only a days worth of data, it might be that the partition elimination is enough alone if people only ever specify that column as a predicate.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    exchange table into partitions is the fastest way

  10. #10
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Got in there whilst I was typing away Jeff!

    'd prefer to create a partitioned table with a maxvalue partition that has the exact same structure and exchange my table into it.
    Could you elaborate on that; I'm interested and I'm not sure I get it. It was my understanding that if I have a non-partitioned table with 3 days of data in it and I exchanged that for the maxvalue partition all 3 days would end up in the max value partition. Is this correct and you meant the other partitions for future days would be used by future inserts, or do you mean that somehow the existing data from the table I exchanged ends up in the partitions for the correct day?
    Last edited by hacketta1; 03-17-2005 at 06:33 PM.

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