-
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
-
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
-
-
I wud suggest that you user LOCAL index. Also as much as possible your index should be prefix by the partition key..
-
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
-
Would he export the table, drop it, re-create it partitioned and import?
Would he create a partitioned index using the same date partitioning?
-
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
-
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.
-
exchange table into partitions is the fastest way
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|