Re: partitioning

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 15 Nov 2010 11:36:37 -0700
Message-ID: <4CE17DB5.3020904_at_evdbt.com>



  
    
  
  
    My apologies - indeed an unfortunate choice of words.  I meant
    "enable row movement" (not "enable row migration"), this is the
    syntax for the CREATE/ALTER TABLE statement.



On 11/15/2010 11:32 AM, Bobak, Mark wrote:

Hmmm….

 

I think “migration” was an unfortunate choice of word on Tim’s part.

 

The feature that allows rows to travel between partitions when the partition key changes, is row movement.  What one enables on the table is row movement.

 

Row chaining and row migration are something quite different, not in control of the user, and are used when Oracle internally manages space in the data block.

 

Row migration:

Row migration happens when the length of a row grows to the point that there is not enough free space left in the block, to satisfy the growth.  In this case, the entire row is moved to a block with enough free space in it, and the original block will have nothing but a pointer to the row in the new block.

 

Row chaining:

Row chaining happens when the length of a row grows to the point that the row does not fit into a block (even an empty block) at all.  In this case, the row is chained.  That is, part of the row is stored in one block, and the rest of the row is stored in another block.

 

Note that in the event of row migration, the solution, (to “clean up” the mess created) is to reorganize the table.  (alter table move, and don’t forget to rebuild the indexes).  To prevent recurrence of row migration, you may consider increasing PCTFREE on the table.

 

However, in the event of row chaining, the only way it can be avoided, is to rebuild the database with a larger blocksize.

 

Hope that helps clarify the concepts for you,

 

-Mark

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zelli, Brian
Sent: Monday, November 15, 2010 1:18 PM
To: 'Mark W. Farnham'; tim_at_evdbt.com
Cc: 'oracle-l-freelists'
Subject: RE: partitioning

 

Ok, I googled on row-migration and found more things on row chaining.  Is there a site or location for a white paper?

 

ciao,

Brian

 


From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Monday, November 15, 2010 12:40 PM
To: tim_at_evdbt.com; Zelli, Brian
Cc: 'oracle-l-freelists'
Subject: RE: partitioning

Nice, especially if the way a row gets to a “no longer active status” is from a row by row interactive update form.

 

If it is big chunks at a time, then the Gorman method of using partition exchange might be more suitable. That is more in line with your idea of once a month granularity. If that is what you’re after, then once a month creating two segments (active and non-active) from your “current” table and swapping the new non-actives for a new empty “became inactive in monthname” partition and the extracted “actives” back with the active partition might make sense. Tim has written marvelous paper “Scaling to Infinity” (approximate title) about this whole process.

 

Making the final status NULL would keep any index on active status types very small and keep the index zero length on the value NULL partition in case partitioning pruning is not effective in any of your queries. If you’re thinking about eventually archiving and purging inactive records, a column of the date last modified regarding the status might be useful as well. But I’m thinking that your first move doesn’t really need composite partitioning and may never.

 


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Monday, November 15, 2010 10:05 AM
To: Brian.Zelli_at_RoswellPark.org
Cc: oracle-l-freelists
Subject: Re: partitioning

 

Brian,

Depending on how the status column gets updated, you could list partition on it and just enable row-migration.  That way, an UPDATE to the partition-key column would result in the row being deleted from one partition and inserted into the other.

Hope this helps...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...


On 11/15/2010 7:15 AM, Zelli, Brian wrote:

Ok folks it's been awhile since I had to partition.  So the user wants the partition done once a month (for now) based on values in a column.  He wants to keep active-type records current and partition off completed or cancelled records.  So basically I would set up a script to run on the 1st that would look at the values and just partition off correct?

 

ciao,

Brian

 


This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.

-- http://www.freelists.org/webpage/oracle-l


This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you.

-- http://www.freelists.org/webpage/oracle-l Received on Mon Nov 15 2010 - 12:36:37 CST

Original text of this message