Pro Oracle Database 11g Administration (52 page)
select
table_name
,partition_name
,tablespace_name
from user_tab_partitions;
Similarly, this query displays partitioned index information:
select
index_name
,partition_name
,status
,high_value
,tablespace_name
from user_ind_partitions;
Splitting a Partition
Suppose you’ve identified a partition that has too many rows, and you want to split it into two partitions.
Use the ALTER TABLE...SPLIT PARTITION statement to split an existing partition. The following example splits a partition in a range-partitioned table:
alter table f_regs split partition p2010 at (20100601)
into (partition p2010_a, partition p2010)
update indexes;
If you don’t specify UPDATE INDEXES, you need to rebuild any local indexes associated with the split partition as well as any global indexes. You can verify the status of partitioned indexes with the following SQL:
SQL> select index_name, partition_name, status from user_ind_partitions; The next example splits a list partition. First, here’s the CREATE TABLE statement, which shows you how the list partitions were originally defined:
295
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
create table f_sales
(reg_sales number
,d_date_id number
,state_code varchar2(20)
)
partition by list (state_code)
( partition reg_west values ('AZ','CA','CO','MT','OR','ID','UT','NV')
,partition reg_mid values ('IA','KS','MI','MN','MO','NE','OH','ND')
,partition reg_rest values (default)
);
Next, the REG_MID partition is split:
alter table f_sales split partition reg_mid values ('IA','KS','MI','MN') into (partition reg_mid_a,
partition reg_mid_b);
The REG_MID_A partition now contains the values IA, KS, MI, and MN, and REG_MID_B is assigned the remaining values MO, NE, OH, and ND.
The split-partition operation allows you to create two new partitions from a single partition. Each new partition has its own segment, physical attributes, and extents. The segment associated with the original partition is deleted.
Merging Partitions
When you create a partition, sometimes it’s hard to predict how many rows the partition will eventually contain. You may have two partitions that don’t contain enough data to warrant separate partitions. In such a situation, use the ALTER TABLE...MERGE PARTITIONS statement to combine partitions.
This example merges the REG_P_1 partition into the REG_P_2 partition: SQL> alter table f_regs merge partitions reg_p_1, reg_p_2 into partition reg_p_2; In this example, the partitions are organized by a range of dates. The merged partition is defined to accept rows with the highest range of the two merged partitions. Any local indexes are also merged into the new single partition.
Be aware that merging partitions invalidates any local indexes associated with the merged partitions. Additionally, all partitions of any global indexes that exist on the table are marked as unusable. You can verify the status of the partitioned indexes by querying the data dictionary: select
index_name
,partition_name
,tablespace_name
,high_value,status
from user_ind_partitions
order by 1,2;
Here’s some sample output showing what a global index and a local index look like after a partition merge:
INDEX_NAME PARTITION_NAME TABLESPACE_NAME HIGH_VALUE STATUS
-------------------- --------------- --------------- --------------- ----------
F_GLO_IDX1 SYS_P680 IDX1 UNUSABLE
F_GLO_IDX1 SYS_P681 IDX1 UNUSABLE
F_GLO_IDX1 SYS_P682 IDX1 UNUSABLE
296
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
F_LOC_FK1 REG_P_2 USERS 20110101 UNUSABLE
F_LOC_FK1 REG_P_3 TBSP3 20120101 USABLE
When you merge partitions, you can use the UPDATE INDEXES clause of the ALTER TABLE statement to instruct Oracle to automatically rebuild any associated indexes:
alter table f_regs merge partitions reg_p_1, reg_p_2 into partition reg_p_2
tablespace tbsp2
update indexes;
Keep in mind that the merge operation takes longer when you use the UPDATE INDEXES clause. If you want to minimize the length of the merge operation, don’t use this clause. Instead, manually rebuild local indexes associated with a merged partition:
SQL> alter table f_regs modify partition reg_p_2 rebuild unusable local indexes; You can rebuild each partition of a global index with the ALTER INDEX...REBUILD PARTITION
statement:
SQL> alter index f_glo_idx1 rebuild partition sys_p680;
SQL> alter index f_glo_idx1 rebuild partition sys_p681;
SQL> alter index f_glo_idx1 rebuild partition sys_p682;
You can merge two or more partitions with the ALTER TABLE...MERGE PARTITIONS statement. The name of the partition into which you’re merging can be the name of one of the partitions you’re merging or a completely new name.
Before you merge two (or more) partitions, make certain the merged partition has enough space in its tablespace to accommodate all the merged rows. If there isn’t enough space, you receive an error that the tablespace can’t extend to the necessary size.
Dropping a Partition
You occasionally need to drop a partition. A common scenario is when you have old data that isn’t used anymore, meaning the partition can be dropped.
First, identify the name of the partition you want to drop. Run the following query to list partitions for a particular table for the currently connected user:
select segment_name, segment_type, partition_name
from user_segments
where segment_name = upper('&table_name');
Next, use the ALTER TABLE...DROP PARTITION statement to remove a partition from a table. This example drops the P_2008 partition from the F_SALES table:
SQL> alter table f_sales drop partition p_2008;
You should see the following message:
Table altered.
If you want to drop a subpartition, use the DROP SUBPARTITION clause: SQL> alter table f_sales drop subpartition p2_south;
You can query USER_TAB_SUBPARTITIONS to verify that the subpartition has been dropped.
297
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
■
Note
Oracle doesn’t let you drop all subpartitions of a composite-partitioned table. There must be at least one subpartition per partition.
When you drop a partition, there is no undrop operation. Therefore, before you do this, be sure you’re in the correct environment and really do need to drop the partition. If you need to preserve the data in a partition to be dropped, merge the partition to another partition instead of dropping it.
You can’t drop a partition from a hash-partitioned table. For hash-partitioned tables, you must coalesce partitions to remove one. And you can’t explicitly drop a partition from a reference-partitioned table. When a parent table partition is dropped, it’s also dropped from corresponding child reference-partitioned tables.
Generating Statistics for a Partition
After you load a large amount of data into a partition, you should generate statistics to reflect the newly inserted data. Use the EXECUTE statement to run the DBMS_STATS package to generate statistics for a particular partition. In this example, the owner is STAR, the table is F_SALES, and the partition being analyzed is P_2012:
exec dbms_stats.gather_table_stats(ownname=>'STAR',-
tabname=>'F_SALES',-
partname=>'P_2012');
If you’re working with a large partition, you probably want to specify the percentage sampling size and degree of parallelism, and also generate statistics for any indexes: exec dbms_stats.gather_table_stats(ownname=>'STAR',-
tabname=>'F_SALES',-
partname=>'P_2012',-
estimate_percent=>dbms_stats.auto_sample_size,-
degree=>dbms_stats.auto_degree,-
cascade=>true);
For a partitioned table, you can generate statistics on either a single partition or the entire table. I recommend that you generate statistics whenever a significant amount of data changes in the partition.
You need to understand your tables and data well enough to determine whether generating new statistics is required.
Removing Rows from a Partition
You can use several techniques to remove rows from a partition. If the data in the particular partition is no longer needed, consider dropping the partition. If you want to remove the data and leave the partition intact, then you can either truncate or delete from it. Truncating a partition permanently and quickly removes the data. If you need the option of rolling back the removal of records, then you should delete (instead of truncate). Both truncating and deleting are described next.
First, identify the name of the partition from which you want to remove records: select segment_name, segment_type, partition_name
from user_segments
where partition_name is not null;
298
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
Use the ALTER TABLE...TRUNCATE PARTITION statement to remove all records from a partition. This example truncates the P_2008 partition of the F_SALES table:
SQL> alter table f_sales truncate partition p_2008;
You should see the following message:
Table truncated.
In this scenario, that message doesn’t mean the entire table was truncated—it only confirms that the specified partition was truncated.
Truncating a partition is an efficient way to quickly remove large amounts of data. When you truncate a partition, however, there is no rollback mechanism. The truncate operation permanently deletes the data from the partition.
If you need the option of rolling back a transaction, use the DELETE statement: SQL> delete from f_sales partition(p_2008);
The downside to this approach is that if you have millions of records, the DELETE operation can take a long time to run. Also, for a large number of records, DELETE generates a great deal of rollback information. This can cause performance issues for other SQL statements contending for resources.
Manipulating Data within a Partition
If you need to select or manipulate data within one partition, specify the partition name as part the SQL
statement. For example, you can select the rows from a specific partition as shown: SQL> select * from f_sales partition (y11);
If you want to select from two (or more) partitions, then use the UNION clause: select * from f_sales partition (y11)
union
select * from f_sales partition (y12);
If you’re a developer and you don’t have access to the data dictionary to view which partitions are available, you can use the following SELECT...PARTITION FOR
g
and higher). With this new syntax, you provide a partition-key value, and Oracle determines what partition that key value belongs in and returns the rows from the corresponding partition. For example:
SQL> select * from f_sales partition for (20101120);
You can also update and delete partition rows. This example updates a column in a partition: SQL> update f_sales partition(Y11) set sales_amt=200;
You can use the PARTITION FOR
SQL> update f_sales partition for (20101120) set sales_amt=200;
■
Note
See the previous section on removing rows for examples of deleting and truncating a partition.
299
CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER
Partitioning Indexes
In today’s large database environments, indexes can also grow to unwieldy sizes. Partitioning indexes provides the same benefits as partitioning tables: improved performance, scalability, and maintainability.
You can create an index that uses its table-partitioning strategy (local), or you can create a partitioned index that uses a different partitioning method than its table (global). Both of these techniques are described in the following subsections.
Partitioning an Index to Follow Its Table
When you create an index on a partitioned table, you have the option of making it type LOCAL. A local partitioned index is partitioned in the same manner as the partitioned table. Each table partition has a corresponding index that contains ROWID values and index-key values for just that table partition. In other words, the ROWID values in a local partitioned index only point to rows in the corresponding table partition.
The following example illustrates the concept of a locally partitioned index. First, create a table that has only two partitions:
create table f_sales (
sales_id number
,sales_amt number
,d_date_id number)
tablespace p1_tbsp
partition by range(d_date_id)(
partition y11 values less than (20120101)
tablespace p1_tbsp
,partition y12 values less than (20130101)
tablespace p2_tbsp
);
Next, use the LOCAL clause of the CREATE INDEX statement to create a local index on the partitioned table. This example creates a local index on the D_DATE_ID column of the F_SALES table: SQL> create index f_sales_fk1 on f_sales(d_date_id) local;
Run the following query to view information about partitioned indexes: select
index_name
,table_name
,partitioning_type
from user_part_indexes
where table_name = 'F_SALES';
Here’s some sample output:
INDEX_NAME TABLE_NAME PARTITION
------------------------------ ---------- ---------
F_SALES_FK1 F_SALES RANGE
Now, query the USER_IND_PARTITIONS table to view information about the locally partitioned index:
select
300