4.10 How do I…Partition a table?Problem
I am creating a very large table and would like to improve access to the table by using a partitioned table. Also, I would like to perform maintenance such as restoring data, data loading, and statistical analysis on one part of a table, while the rest of the table is available for use by users. How do I partition a table?
Technique
Partitioning a table allows it to be split into several independent pieces, each with potentially differing storage parameters. One advantage is that large tables become more manageable. Each partition may be analyzed, exported, truncated, loaded, and so on. Also, if one partition of a table goes offline, users may still access and update data in other partitions of that table. Partitioning is transparent to the users and applications. The PARTITION BY option of the CREATE TABLE command is used to create table partitions. Oracle allows multiple partitions with potentially different storage parameters, including tablespace. The syntax for this is shown in Fig- ure 4.27.
Steps
1. Connect to SQL*Plus as the WAITE user account.
2. Create the table, specifying a range partition, and listing all partitions and storage parameters. CHP4_28.SQL, in Figure 4.28, shows an example of creating a partitioned table.
3. Run the CHP4_28.SQL script, which will create the EMP04 table, with partitions stored in the USER, TEMP, and SYSTEM tablespaces. Each partition will also have its own storage parameters.
4. To demonstrate that Oracle will store records in the correct partition, insert one record for each partition. Run CHP4_29.SQL, shown in Figure 4.29, to insert one record into each partition. For example, the INSERT command will place a record within the first partition, which contains employees with salaries less than $25,000.
5. Query the ROWIDs of each record to see if they are placed in different datafiles. Run the CHP4_30.SQL script, shown in Figure 4.30, to retrieve this information.
SQL> START CHP4_30.sql
JOE THE INTERN
20000
2
BRENDAN
35000
2
CHRISTOPHER
100000
3
ETHAN
1250000
4
How It Works
The CREATE TABLE command has an optional PARTITION BY clause to create partitioned tables. Step 1 creates a sample partitioned table. Step 2 inserts values into the partitioned table, placing records within each partition. Step 3 retrieves records with their ROWIDs, showing that the records are indeed placed within different tablespaces.
Partitioned tables have several advantages over regular tables. If one part of the partition is unavailable, the other parts are available for select, insert, update, and delete. Only those records that are contained within the down partition will be unavailable. Also, a lock may be obtained on one partition while the others are available for data manipulation. This may greatly improve the availability of the records of a table.
Each partition may have its own storage parameters, such as INITIAL, NEXT, PCTFREE, PCTUSED, and so on. This is important, because one partition may have one record and another one million records. By allowing individual partitions to have different storage parameters, Oracle allows great flexibility in storage and performance tuning.
Each partition may be stored in different tablespaces. This is beneficial for availability if a tablespace is offline. This is also beneficial for performance, as a table may have multiple drives reading and/or writing in parallel.
The SQL*Loader import, export, and analyze commands can be performed on one or more partitions at a time. This can dramatically improve performance, as one part of a partition may be loading, locked, or unavailable, while other parts are available for use.
Comments
In the example, the SYSTEM and TEMP tablespaces were chosen for storage of partition parts. In reality, you would not want to put any objects in these tablespaces. They were included to show a simple example of how to put partition parts into separate tablespaces.
The column keys may not be of the LONG, RAW, or ROWID datatypes.
A partition key may have more than one column, as long as the columns are separated by commas in both the definition and the range specification.