Database Reference
In-Depth Information
EODA@ORA12CR1> update range_example
2 set range_key_column = to_date('01-jan-2014','dd-mon-yyyy')
3 where range_key_column = to_date('31-dec-2013','dd-mon-yyyy');
1 row updated.
EODA@ORA12CR1> select rowid
2 from range_example
3 where range_key_column = to_date('01-jan-2014','dd-mon-yyyy');
ROWID
------------------
AAAtzYAAGAAAae6AAA
As long as you understand that the
ROWID
of the row will change on this update, enabling row movement will
allow you to update partition keys.
■
there are other cases where a
ROWID
can change as a result of an update. It can happen as a result of an update
to the primary key of an IOt. the universal
ROWID
will change for that row, too. the Oracle 10
g
and above
FLASHBACK
TABLE
command may also change the
ROWID
of rows, as might the Oracle 10
g
and above
ALTER TABLE SHRINK
command.
Note
You need to understand that, internally, row movement is done as if you had, in fact, deleted the row and
reinserted it. It will update every single index on this table, and delete the old entry and insert a new one. It will
do the physical work of a
DELETE
plus an
INSERT
. However, it is considered an update by Oracle even though it
physically deletes and inserts the row—therefore, it won't cause
INSERT
and
DELETE
triggers to fire, just the
UPDATE
triggers. Additionally, child tables that might prevent a
DELETE
due to a foreign key constraint won't. You do have to
be prepared, however, for the extra work that will be performed; it is much more expensive than a normal
UPDATE
.
Therefore, it would be a bad design decision to construct a system whereby the partition key was modified frequently
and that modification would cause a partition movement.
Table Partitioning Schemes Wrap-up
In general, range partitioning is useful when you have data that is logically segregated by some value(s). Time-based
data immediately comes to the forefront as a classic example—partition by “Sales Quarter,” “Fiscal Year,” or “Month.”
Range partitioning is able to take advantage of partition elimination in many cases, including the use of exact equality
and ranges (less than, greater than, between, and so on).
Hash partitioning is suitable for data that has no natural ranges by which you can partition. For example, if you
had to load a table full of census-related data, there might not be an attribute by which it would make sense to range
partition by. However, you would still like to take advantage of the administrative, performance, and availability
enhancements offered by partitioning. Here, you would simply pick a unique or almost unique set of columns to hash
on. This would achieve an even distribution of data across as many partitions as you like. Hash partitioned objects
can take advantage of partition elimination when exact equality or
IN ( value, value, ... )
is used, but not when
ranges of data are used.
List partitioning is suitable for data that has a column with a discrete set of values, and partitioning by the column
makes sense based on the way your application uses it (e.g., it easily permits partition elimination in queries). Classic
examples would be a state or region code—or, in fact, many code type attributes in general.
Interval partitioning extends the range partitioning feature and allows partitions to automatically be added when
data inserted into the table doesn't fit into an existing partition. This feature greatly enhances range partitioning
in that there is less maintenance involved (because the DBA doesn't have to necessarily monitor the ranges and
manually add partitions).
