Pro Oracle Database 11g Administration (51 page)
F_SALES_IDX1 N/A
F_SALES_GIDX1 UNUSABLE
F_SALES_GIDX2 N/A
You need to rebuild any indexes or partitions in an unusable state. This example rebuilds the unusable partition of the local index:
SQL> alter index f_sales_idx1 rebuild partition reg_west tablespace p1_tbsp; When you move a table partition to a different tablespace, this causes the ROWID of each record in the table partition to change. Because a regular index stores the table ROWID as part of its structure, the index partition is invalidated if the table partition moves. In this scenario, you must rebuild the index. When you rebuild the index partition, you have the option of moving it to a different tablespace.
Automatically Moving Updated Rows
By default, Oracle doesn’t let you update a row by setting the partition key to a value outside of its current partition. For example, this statement updates the partition-key column (D_DATE_ID) to a value that would result in the row needing to exist in a different partition: SQL> update f_regs set d_date_id = 20100901 where d_date_id = 20090201; You receive the following error:
ORA-14402: updating partition key column would cause a partition change 289
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
In this scenario, use the ENABLE ROW MOVEMENT clause of the ALTER TABLE statement to allow updates to the partition key that would change the partition in which a value belongs. For this example, the F_REGS table is first modified to enable row movement:
SQL> alter table f_regs enable row movement;
You should now be able to update the partition key to a value that moves the row to a different segment. You can verify that row movement has been enabled by querying the ROW_MOVEMENT
column of the USER_TABLES view:
SQL> select row_movement from user_tables where table_name='F_REGS'; You should see the value ENABLED:
ROW_MOVE
--------
ENABLED
To disable row movement, use the DISABLE ROW MOVEMENT clause:
SQL> alter table f_regs disable row movement;
Partitioning an Existing Table
You may have a nonpartitioned table that has grown quite large, and want to partition it. There are several methods for converting a nonpartitioned table to a partitioned table. Table 12–4 lists the pros and cons of various techniques.
Table 12–4.
Methods of Converting a Nonpartitioned Table
Conversion Method
Advantages Disadvantages
CREATE
Requires space for both old
* FROM
PARALLEL options. Direct path
and new tables.
load.
INSERT /*+ APPEND */ INTO
Fast, simple. Direct path load.
Requires space for both old
and new tables.
Data Pump EXPDP old table; IMPDP
Fast; less space required. Takes
More complicated because
new table (or EXP IMP if using older
care of grants, privileges, and so
you need to use a utility.
version of Oracle)
on. Loading can be done per
partition with filtering conditions.
Create partitioned
Potentially less downtime.
Many steps; complicated.
partitions with
Use the DBMS_REDEFINITION
Converts existing table inline.
Many steps; complicated.
package (newer versions of Oracle)
Create CSV file or external table;
Loading can be done partition by
Many steps; complicated.
load
partition.
SQL*Loader
290
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
One of the easiest ways from Table 12–4 to partition an existing table is to create a new table—one that
is
partitioned—and load it with data from the old table. Listed next are the required steps: 1. If this is a table in an active production database, you should schedule some downtime for the table to ensure that no active transactions are occurring while the table is being migrated.
2. Create a new partitioned table from the old with CREATE TABLE
3. Drop or rename the old table.
4. Rename the table created in step 1 to the name of the dropped table.
For example, let’s assume that the F_REGS table used so far in this chapter was created as an unpartitioned table. The following statement creates a new table that
is
partitioned, taking data from the old table that isn’t:
create table f_regs_new
partition by range (d_date_id)
(partition p2008 values less than(20090101),
partition p2009 values less than(20100101),
partition pmax values less than(maxvalue)
)
nologging
as select * from f_regs;
Now you can drop (or rename) the old nonpartitioned table and rename the new partitioned table to the old table name. Be sure you don’t need the old table before you drop it with the PURGE option (because this permanently drops the table):
SQL> drop table f_regs purge;
SQL> rename f_regs_new to f_regs;
Finally, build any constraints, grants, indexes, and statistics for the new table. You should now have a partitioned table that replaces the old, nonpartitioned table.
For the last step, if the original table contains many constraints, grants, and indexes, you may want to use Data Pump expd or exp to export the original table without data. Then, after the new table is created, use Data Pump impdp or imp to create the constraints, grants, indexes, and statistics on the new table.
Adding a Partition
Sometimes it’s hard to predict how many partitions you should initially make for a table. A typical example is a range-partitioned table that’s created without a MAXVALUE-created partition. You make a partitioned table that contains enough partitions for two years into the future, and then you forget about the table. Some time in the future, application users report that this message is being thrown: ORA-14406: updated partition key is beyond highest legal partition key
■
Tip
Consider using interval partitioning, which enables Oracle to automatically add range partitions when the upper bound is exceeded.
291
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
For a range-partitioned table, if the table’s highest bound isn’t defined with a MAXVALUE, you can use the ALTER TABLE...ADD PARTITION statement to add a partition to the high end of the table. If you’re not sure what the current upper bound is, query the data dictionary:
select
table_name
,partition_name
,high_value
from user_tab_partitions
where table_name = UPPER('&&tab_name')
order by table_name, partition_name;
This example adds a partition to the high end of a range-partitioned table: alter table f_regs
add partition p2011
values less than (20120101)
pctfree 5 pctused 95
tablespace p11_tbsp;
If you have a range-partitioned table with the high range bounded by MAXVALUE, you can’t add a partition. In this situation, you have to split an existing partition (see the section “Splitting a Partition” in this chapter).
For a list-partitioned table, you can add a new partition only if there isn’t a DEFAULT partition defined. The next example adds a partition to a list-partitioned table: SQL> alter table f_sales add partition reg_east values('GA');
If you have a hash-partitioned table, use the ADD PARTITION clause as follows to add a partition: alter table browns
add partition hash_5
tablespace p_tbsp
update indexes;
■
Note
When you’re adding to a hash partitioned table, if you don’t specify the UPDATE INDEXES clause, any global indexes must be rebuilt. In addition, you must rebuild any local indexes for the newly added partition.
In general, after adding a partition to a table, always check the partitioned indexes to be sure they all still have a VALID status:
select
b.table_name
,a.index_name
,a.partition_name
,a.status
,b.locality
from user_ind_partitions a
,user_part_indexes b
where a.index_name=b.index_name
and table_name = upper('&&part_table');
292
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
Also check the status of any global nonpartitioned indexes:
select
index_name
,status
from user_indexes
where table_name = upper('&&part_table');
Consider using the UPDATE INDEXES clause of the ALTER TABLE statement to automatically rebuild indexes when you’re performing maintenance operations. In some cases, Oracle may not allow you to use the UPDATE INDEXES clause, in which case you have to manually rebuild any unusable indexes. I highly recommend that you always test a maintenance operation in a nonproduction database to determine any unforeseen side effects.
Exchanging a Partition with an Existing Table
Exchanging a partition is a common technique for loading new data into large partitioned tables. This feature allows you to take a stand-alone table and swap it with an existing partition (in an already-partitioned table). Doing that lets you transparently add fully loaded new partitions without affecting the availability or performance of operations against the other partitions in the table.
The following simple example illustrates the process. Say you have a range-partitioned table created as follows:
create table f_sales
(sales_amt number
,d_date_id number)
partition by range (d_date_id)
(partition p_2009 values less than (20100101),
partition p_2010 values less than (20110101),
partition p_2011 values less than (20120101)
);
You also create a bitmap index on the D_DATE_ID column:
create bitmap index d_date_id_fk1 on
f_sales(d_date_id)
local;
Now, add a new partition to the table that will store new data:
alter table f_sales
add partition p_2012
values less than(20130101);
Next, create a staging table, and insert data that falls in the range of values for the newly added partition:
create table workpart(
sales_amt number
,d_date_id number);
insert into workpart values(100,20120201);
insert into workpart values(120,20120507);
293
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
Create a bitmap index on the WORKPART table that matches the structure of the bitmap index on F_SALES:
create bitmap index
d_date_id_fk2
on workpart(d_date_id);
Now, exchange the WORKPART table with the P_2012 partition:
alter table f_sales
exchange partition p_2012
with table workpart
including indexes
without validation;
A quick query of the F_SALES table verifies that the partition was exchanged successfully: SQL> select * from f_sales partition(p_2012);
Here’s the output:
SALES_AMT D_DATE_ID
---------- ----------
100 20120201
120 20120507
This query displays that the indexes are all still usable:
SQL> select index_name, partition_name, status from user_ind_partitions; You can also verify that a local index segment was created for the new partition: select segment_name,segment_type,partition_name
from user_segments
where segment_name IN('F_SALES','D_DATE_ID_FK1');
Here’s the output:
SEGMENT_NAME SEGMENT_TYPE PARTITION_
-------------------- ------------------ ----------
D_DATE_ID_FK1 INDEX PARTITION P_2009
D_DATE_ID_FK1 INDEX PARTITION P_2010
D_DATE_ID_FK1 INDEX PARTITION P_2011
D_DATE_ID_FK1 INDEX PARTITION P_2012
F_SALES TABLE PARTITION P_2009
F_SALES TABLE PARTITION P_2010
F_SALES TABLE PARTITION P_2011
F_SALES TABLE PARTITION P_2012
The ability to exchange partitions is an extremely powerful feature. It allows you to take a partition of an existing table and make it a stand-alone table, and at the same time make a stand-alone table (which can be fully populated before the partition exchange operation) part of a partitioned table. When you exchange a partition, Oracle updates the entries in the data dictionary to perform the exchange.
When you exchange a partition with the WITHOUT VALIDATION clause, you instruct Oracle not to validate that the rows in the incoming partition (or subpartition) are valid entries for the defined range.
This has the advantage of making the exchange a very quick operation because Oracle is only updating pointers in the data dictionary to perform the exchange operation. You need to make sure your data is accurate if you use WITHOUT VALIDATION.
294
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
If a primary key is defined for the partitioned table, the table being exchanged must have the same primary-key structure defined. If there is a primary key, the WITHOUT VALIDATION clause doesn’t stop Oracle from enforcing unique constraints.
Renaming a Partition
Sometimes you need to rename a table partition or index partition. For example, you may want to rename a partition before you drop it (to ensure that it’s not being used). Also, you may want to rename objects so they conform to standards. In these scenarios, use the appropriate ALTER TABLE or ALTER
INDEX statement.
This example uses the ALTER TABLE statement to rename a table partition: SQL> alter table f_regs rename partition reg_p_1 to reg_part_1;
The next line of code uses the ALTER INDEX statement to rename an index partition: SQL> alter index f_reg_dates_fk1 rename partition reg_p_1 to reg_part_1; You can query the data dictionary to verify the information regarding renamed objects. This query shows partitioned table names: