HomeOracle Page 5 - Large Database Features In Oracle
Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index - Oracle
When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, the first of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).
Data and index partitioning are an important part in maintaining large databases. We have discussed the reasons for partitioning and shown the steps to implement it. In this project, you will create a range-partitioned table and a related local partitioned index.
Step by Step
Create two tablespaces called inv_ts_2007q1 and inv_2007q2 using the following SQL statements. These will be used to store data partitions.
Create a partitioned table called INVOICE using the following listing, based on the following information:
a. Define the table with the columns identified in Table 9-11.
b. Use order_date as the partition key, and subset the data into the first and second calendar quarters 2007.
c. Define the table with the data partitions and tablespaces identified in Table 9-12.
d. Use the enable row movement option:
create table invoice( invoice_id number, customer_id number, order_date date, ship_date date) partition by range (order_date) (partition INV_2007Q1 values less than (to_date(2007-04-01','YYYY-MM-DD')) tablespace inv_ts_2007Q1, partition INV_2007Q2 values less than (to_date('2007-07-01','YYYY-MM-DD')) tablespace inv_ts_2007Q2, partition inv_max values less than (maxvalue) tablespace inv_ts_max) enable row movement;
Create a local partitioned index called inv_order_dt_idx on call_date using the following listing as well as the index partitions and tablespaces identified in Table 9-12.
create index inv_order_dt_idx on invoice(order_date) local (partition inv_idx_2007q1 tablespace inv_idx_ts_2007q1, partition inv_idx_2007q2 tablespace inv_idx_ts_2007q2, partition inv_idx_max tablespace inv_idx_ts_max);
TABLE 9-11.INVOICE Table Columns
The steps in this project reinforce some of the more common scenarios you will encounter: range-based partitioning and prefixed local partitioned indexes. Separate tablespaces were used for data and indexes, quarterly partitions were defined, a local index was defined, and the enable row movement was used to allow the database to automatically redistribute rows to their related partitions in the event of an update to the partition key.
Well, we have certainly covered a lot in this section. Having the background information on these topics will serve you well when maintaining and tuning large databases. Before we move on to the next section, letís take a quick progress check to make sure it all sank in.
Upper Range Limit
Apr 1, 2007
July 1, 2007
Apr 1, 2007
July 1, 2007
TABLE 9-12. INVOICE Table Data and Index Partitions
As you load more and more data into your database, performance and storage maintenance can quickly become concerns. Usually at the start of an implementation of a database, data volumes are estimated and projected a year or two ahead. However, often times these estimates turn out to be on the low side and you find yourself
Progress Check Answers
The following DML commands can be applied to partitions as well as tables: delete, insert, select, truncate, and update.
Partition pruning is the process of eliminating data not belonging to the subset defined by the criteria of a query.
Only one table attribute can be used to define the partition key in list partitioning.
Range partitioning is most commonly used with a date-based partition key.
List and hash partitioning cannot be combined for composite partitioning.
Only one partition key may be defined.
Local partitioned indexes have a one-to-one relationship between the data and index partitions.
A partitioned index is prefixed when the leftmost column of the index key is the same as the leftmost column of the index partition key.
scrambling for more space in order to load new data. In addition to the partitioning abilities discussed in the previous section, Oracle Database 10g has the ability to compress your data and indexes to further address the concerns of performance and maintenance.
Compression can be performed at the data or index levels. In this section, we will discuss the options available with Oracle Database 10g and their impacts.