Pro Oracle Database 11g Administration (50 page)

---------- ---------- --------- ---------- ------------------------------

F_SALES P1 1 P1_TBSP TO_DATE(' 2012–01-01 00:00:00'

F_SALES SYS_P477 2 P2_TBSP TO_DATE(' 2013-01-01 00:00:00'

F_SALES P2 3 P3_TBSP TO_DATE(' 2014-01-01 00:00:00'

You can also have Oracle add partitions by other increments of time, such as a week. For example: create table f_sales(

sales_amt number

,d_date date

)

partition by range (d_date)

interval(numtodsinterval(7,'day'))

store in (p1_tbsp, p2_tbsp, p3_tbsp)

(partition p1 values less than (to_date('01-oct-2010', 'dd-mon-yyyy')) tablespace p1_tbsp);

As data is inserted into weeks in the future, new weekly partitions will be created automatically. In this way, Oracle automatically manages the addition of partitions to the table.

Partitioning to Match a Parent Table

If you’re using Oracle Database 11
g
or higher, you can use the PARTITION BY REFERENCE clause to specify that a child table should be partitioned in the same way as its parent. This allows a child table to inherit the partitioning strategy of its parent table. Any parent table partition-maintenance operations are also applied to the child record tables.

■
Note
Before the advent of the partitioning-by-reference feature, you had to physically duplicate and maintain the parent table column in the child table. Doing so not only requires more disk space but also is a source of error when you’re maintaining the partitions.

For example, say you have a parent ORDERS table and a child ORDER_ITEMS table that are related by primary-key and foreign-key constraints on the ORDER_ID column. The parent ORDERS table is partitioned on the ORDER_DATE column. Even though the child ORDER_ITEMS table doesn’t contain the ORDER_DATE column, you wonder whether you can partition it so that the records are distributed the same way as in the parent ORDERS table. This example creates a parent table with a primary-key constraint on ORDER_ID and range partitions on ORDER_DATE:

create table orders(

order_id number

,order_date date

,constraint order_pk primary key(order_id)

)

partition by range(order_date)

(partition p10 values less than (to_date('01-jan-2010','dd-mon-yyyy')) 284

CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER

,partition p11 values less than (to_date('01-jan-2011','dd-mon-yyyy'))

,partition pmax values less than (maxvalue)

);

Next, you create the child ORDER_ITEMS table. It’s partitioned by naming the foreign-key constraint as the referenced object:

create table order_items(

line_id number

,order_id number not null

,sku number

,quantity number

,constraint order_items_pk primary key(line_id, order_id)

,constraint order_items_fk1 foreign key (order_id) references orders

)

partition by reference (order_items_fk1);

Notice that the foreign-key column ORDER_ID must be defined as NOT NULL. The foreign-key column must be enabled and enforced.

You can inspect the partition-key columns via the following query:

select

name

,column_name

,column_position

from user_part_key_columns

where name in ('ORDERS','ORDER_ITEMS');

Here’s the output for this example:

NAME COLUMN_NAME COLUMN_POSITION

-------------------- -------------------- ---------------

ORDERS ORDER_DATE 1

ORDER_ITEMS ORDER_ID 1

Notice that the child table is partitioned by the ORDER_ID column. This ensures that the child record is partitioned in the same manner as the parent record (because the child record is related to the parent record via the ORDER_ID key column).

When you create the referenced partition child table, if you don’t explicitly name the child table partitions, by default Oracle creates partitions for the child table with the same partition names as its parent table. This example explicitly names the child table referenced partitions: create table order_items(

line_id number

,order_id number not null

,sku number

,quantity number

,constraint order_items_pk primary key(line_id, order_id)

,constraint order_items_fk1 foreign key (order_id) references orders

)

partition by reference (order_items_fk1)

(partition c10

,partition c11

,partition cmax

);

285

CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER

You can’t specify the partition bounds of a referenced table. Partitions of a referenced table are created in the same tablespace as the parent partition unless you specify tablespaces for the child partitions.

Partitioning on a Virtual Column

If you’re using Oracle Database 11
g
or higher, you can partition on a virtual column (see Chapter 7 for a discussion of virtual columns). Here’s a sample script that creates a table named EMP with the virtual column COMMISSION and a corresponding range partition for the virtual column: create table emp (

emp_id number

,salary number

,comm_pct number

,commission generated always as (salary*comm_pct)

)

partition by range(commission)

(partition p1 values less than (1000)

,partition p2 values less than (2000)

,partition p3 values less than (maxvalue));

This strategy allows you to partition on a column that isn’t stored in the table but is computed dynamically. Virtual-column partitioning is appropriate when there is a business requirement to partition on a column that isn’t physically stored in a table. The expression behind a virtual column can be a complex calculation, can return a subset of a column string, can combine column values, and so on.

The possibilities are endless.

For example, you may have a 10-character string column in which the first 2 digits represent a region and last 8 digits represent a specific location (this is a bad design, but it happens). In this case, it may make sense from a business perspective to partition on the first two digits of this column (by region).

Giving an Application Control over Partitioning

You may have a rare scenario in which you want the application inserting records into a table to explicitly control which partition it inserts data into. If you’re using Oracle Database 11
g
or higher, you can use the PARTITION BY SYSTEM clause to allow an INSERT statement to specify into which partition to insert data. This next example creates a system-partitioned table with three partitions: create table apps

(app_id number

,app_amnt number)

partition by system

(partition p1

,partition p2

,partition p3);

When inserting data into this table, you must specify a partition. The next line of code inserts a record into partition P1:

SQL> insert into apps partition(p1) values(1,100);

When you’re updating or deleting, if you don’t specify a partition, Oracle scans all partitions of a system-partitioned table to find the relevant rows. Therefore, you should specify a partition when updating and deleting to avoid poor performance.

286

CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER

A system-partitioned table is helpful in unusual situations in which you need to explicitly control which partition a record is inserted into. This allows your application code to manage the distribution of records among the partitions. I recommend that you use this feature only when you can’t use one of Oracle’s other partitioning mechanisms to meet your business requirement.

Maintaining Partitions

When using partitions, you'll eventually have to perform some sort of maintenance operation. For example, you may be required to add, drop, truncate, split, and merge partitions. The various partition maintenance tasks are described in this section, starting with a description of the data-dictionary objects that relate to partitioning.

Viewing Partition Metadata

When you’re maintaining partitions, it’s helpful to view metadata information about the partitioned objects. Oracle provides many data-dictionary views that contain information about partitioned tables and indexes. Table 12–3 describes each of the views.

Keep in mind the DBA-level views contain data for all partitioned objects in the database, the ALL

level shows partitioning information to which the currently connect user has access, and the USER-level views contain information for the partitioned objects owned by the currently connected user.

Table 12–3.
Data-Dictionary Views Containing Partitioning Information
View Contains

DBA/ALL/USER_PART_TABLES

Displays partitioned table information

DBA/ALL/USER_TAB_PARTITIONS

Contains information regarding individual table

partitions

DBA/ALL/USER_TAB_SUBPARTITIONS

Shows subpartition-level table information

regarding storage and statistics

DBA/ALL/USER_PART_KEY_COLUMNS

Displays partition-key columns

DBA/ALL/USER_SUBPART_KEY_COLUMNS

Contains subpartition-key columns

DBA/ALL/USER_PART_COL_STATISTICS

Shows column-level statistics

DBA/ALL/USER_SUBPART_COL_STATISTICS

Displays subpartition-level statistics

DBA/ALL/USER_PART_HISTOGRAMS

Contains histogram information for partitions

DBA/ALL/USER_SUBPART_HISTOGRAMS

Shows histogram information for subpartitions

DBA/ALL/USER_PART_INDEXES

Displays partitioned index information

DBA/ALL/USER_IND_PARTITIONS

Contains information regarding individual index

partitions

DBA/ALL/USER_IND_SUBPARTITIONS

Shows subpartition-level index information

DBA/ALL/USER_SUBPARTITION_TEMPLATES

Displays subpartition template information

287

CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER

Two views you’ll use quite often are DBA_PART_TABLES and the DBA_TAB_PARTITIONS. The DBA_PART_TABLES view contains table-level partitioning information such as partitioning method and default storage settings. The DBA_TAB_PARTITIONS view contains information about the individual table partitions, such as the partition name and storage settings for individual partitions.

Moving a Partition

Suppose you create a partitioned table as shown:

create table f_sales

(reg_sales number

,sales_amt 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)

);

Also for this partitioned table, you decide to create a local index on the STATE_CODE column: SQL> create index f_sales_idx1 on f_sales(state_code) local;

You create a global index on the REG_SALES column:

SQL> create index f_sales_gidx1 on f_sales(reg_sales);

And you create a global partitioned index on the SALES_AMT column:

create index f_sales_gidx2 on f_sales(sales_amt)

global partition by range(sales_amt)

(partition pg1 values less than (25)

,partition pg2 values less than (50)

,partition pg3 values less than (maxvalue));

Later, you decide that you want to move a partition to a specific tablespace. In this scenario, you can use the ALTER TABLE...MOVE PARTITION statement to relocate a table partition. This example moves the REG_WEST partition to a new tablespace:

SQL> alter table f_sales move partition reg_west tablespace p1_tbsp; It’s a fairly simple operation to move a partition to a different tablespace. Whenever you do this, make sure you check on the status of any indexes associated with the table. When you move a table partition to a different tablespace, any associated indexes are invalidated; therefore, you must rebuild any local indexes associated with a table partition that has been moved. You can verify the status of global and local index partitions by querying the data dictionary. Here’s a sample query: select

b.table_name

,a.index_name

,a.partition_name

,a.status

,b.locality

from user_ind_partitions a

,user_part_indexes b

288

CHAPTER 11 ■ PARTITIONING: DIVIDE AND CONQUER

where a.index_name=b.index_name

and table_name = 'F_SALES';

Here’s the output for this example. One global index and one partition of a local index need to be rebuilt:

TABLE_NAME INDEX_NAME PARTITION_ STATUS LOCALITY

-------------------- --------------- ---------- ---------- --------------------

F_SALES F_SALES_IDX1 REG_MID USABLE LOCAL

F_SALES F_SALES_IDX1 REG_REST USABLE LOCAL

F_SALES F_SALES_IDX1 REG_WEST UNUSABLE LOCAL

F_SALES F_SALES_GIDX2 PG1 UNUSABLE GLOBAL

F_SALES F_SALES_GIDX2 PG2 UNUSABLE GLOBAL

F_SALES F_SALES_GIDX2 PG3 UNUSABLE GLOBAL

Notice that the entire global index is rendered unusable, and only one partition of the local index (the partition that was moved) is unusable. Keep in mind that any maintenance operation on a table invalidates every partition of any associated global partitioned indexes or global nonpartitioned indexes.

To check for global nonpartitioned indexes, you need to look in the USER_INDEXES view. Here’s a query for this example:

select

index_name

,status

from user_indexes

where table_name ='F_SALES';

Here’s some sample output:

INDEX_NAME STATUS

--------------- ----------