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 AS SELECT Simple; can use NOLOGGING and

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

SELECT * FROM

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.

; exchange

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 with

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 ...AS SELECT * FROM .

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: