Range Partitioning
Range partitioning organizes rows in the various assigned tablespaces based on a column(s). As illustrated in Figure 15.1, a partitioned table is a logical table physically stored across many tablespaces.
Figure 15.1. ST_SALES range partitioned table.
NOTE
The ST_SALES partitioned table is not implemented with the other Sales Tracking objects because of the lack of physical disk drives on the author's NT platform and the lack of example data. Partitioning is useful for tables with hundreds of thousands of rows, or even millions of rows.
Listing 15.1 illustrates the CREATE TABLE syntax that creates the ST_SALES table shown in Figure 15.1. Notice the PARTITION BY RANGE with the key field on which the partitioning will be based. More than one column can be listed with this clause. Each partition then has a LESS THAN clause that tells Oracle which rows to place in which tablespaces. It is good practice to always use the MAXVALUE clause, as illustrated in the last PARTITION statement in Listing 15.1.
Listing 15.1. Sales Tracking ST_SALES Range Partition Table Example
CREATE TABLE st_sales
(sales_customer_id NUMBER(6),
sales_sale_amt NUMBER(9,2),
sales_sale_date DATE)
PARTITION BY RANGE(sales_sale_date)
(PARTITION st_q1_00 VALUES LESS THAN ('01-APR-2000')
TABLESPACE ts_st_q1_00,
PARTITION st_q2_00 VALUES LESS THAN ('01-JUL-2000')
TABLESPACE ts_st_q2_00,
PARTITION st_q3_00 VALUES LESS THAN ('01-OCT-2000')
TABLESPACE ts_st_q3_00,
PARTITION st_q4_00 VALUES LESS THAN (MAXVALUE)
TABLESPACE ts_st_q4_00
); ok pm
|
TIP
This table easily can be created by first creating the tablespaces, then following the example in Listing 15.1, and finally adding a postupdate and/or a postinsert trigger to execute from the ST_INVENTORY table when the Inv_sale date field is not null.
NOTE
Each partition can have its own defined storage clause.
Each partition can be accessed independently from the others. The SQL statement SELECT sales_customer_id, sales_sale_amt from ST_SALES PARTITION (st_q2_00) would only access the rows in the st-q2-00 tablespace.
As new quarters are encountered, the DBA can simply add new partitions. Because the ST_SALES quarterly data is no longer needed, the tablespace can be backed up and then dropped, easily dropping all the rows. However, the rows can be easily re-established if business needs require it. Listing 15.2 shows the valid partitioning tablespace syntax options.
TIP
It is advised that you do not use the keyword MAXVALUE on the last partition of data-sensitive partitioning, such as the date field used in this example. It is easier to use the date for the partitioning and add another partition when the business requires it rather than to have to split, rename, and so on the last partition to accommodate a new range of dates.
TIP
Partitions can be easily dropped with the command ALTER TABLE ST_SALES DROP PARTITION st_q1_00. New tablespaces also can easily be added (unless the MAXVALUE clause has been specified) by using ALTER TABLE ST_SALES ADD PARTITION st-q1-01 VALUES LESS THAN '01-APR-2001' TABLESPACE st_q1_01.
Listing 15.2. Sales Tracking ST_SALES Partitioned Table Example
ALTER TABLE ADD PARTITION ALTER TABLE DROP PARTITION ALTER TABLE MOVE PARTITION ALTER TABLE SPLIT PARTITION ALTER TABLE TRUNCATE PARTITION ALTER TABLE MODIFY PARTITION ADD VALUES ALTER TABLE MODIFY PARTITION DROP VALUES ALTER TABLE EXCHANGE PARTITION |
NOTE
Notice in Figure 15.1 that the partitions have meaningful names. Accessing the data by partition might be necessary for good reason. However, with hash partitioning, no real reason exists to access the data by partition, although the Oracle syntax does allow for this.