The following example shows the usage and creation of a partitioned table.

I would like to create a Sales table that would store sales data. This table should be partitioned with the presumption that the months of December, January, and February have the most sales. The storage of this partition should be larger compared to the other partitions. The syntax for such a table can be seen in Listing 33.1.

Listing 33.1. Creating a Sales table.

Create table sales

(sales_id    varchar(12),

day          number(2),

month        number(2),

year         number(4),

store_id     number(4))

partition by range(month)

(partition P1 values less than (4)

tablespace sales1 storage

(initial 10M next 10M pctincrease 0),

partition P2 values less than (7)

tablespace sales2 storage

(initial 4M next 4M pctincrease 0),

partition P3 values less than 13

tablespace sales3 storage

(initial 4M next 4M pctincrease 0)); 

In this listing, the tablespaces are sized based on the data expected to be supported by them.

---

Note:  
Partitioning can be done based on numbers as well as character data.

---

---

Note:  
You can have multicolumn partition keys.

---

Partitioned Indexes

There are many different types of indexing options provided by Oracle8.

---

Note:  
A bitmap index on a nonpartitioned table cannot be partitioned.

---

---

Note:  
A bitmap index on a partitioned table must be a local index.

---

Table 33.2 summarizes the types of indexes that can be created on partitioned and non-partitioned tables.

The following sections discuss several types of indexes possible in an Oracle8 system.

Local and Global Indexes

In a local index, all keys in the index partition have a corresponding row in the table partition, and the keys do not overlap with keys in other partitions. Partition maintenance is independent because whenever an underlying table partition is modified, only one index needs to be modified. As a result, the different partitions can be worked on independently; for example, users can access one partition through a SQL query while another partition is offline for maintenance.

---

Note:  
Local indexes are equi-partitioned with the partitions of the underlying table, while global indexes are normally not equi-partitioned with the partitions of the underlying table.

---

---

Note:  
Even if for some reason the global index is equi-partitioned with the partitions of the underlying table, the optimizer is not aware of it and therefore partitions are not eliminated from the query plan.

---

---

Note:  
Oracle8 does not support global nonprefixed indexes.

---

Local Prefixed

An index is prefixed if the leftmost columns of the index exactly match the same columns of the partition key—in the same order with the same number of columns. This index can be unique or nonunique. Unique indexes guarantee that to get to the data you will have to access only one index partition, while nonunique indexes guarantee that to get to the data you will have to access only one index partition if the full partition key is provided as part of the WHERE clause.

Local Nonprefixed

An index is nonprefixed if it is not partitioned on a left prefix of the index columns.

Global Prefixed

A global index is one in which the index range may include more than one partition. Global indexes are harder to manage than local indexes, and they do not provide partition independence. As a result, it is not possible to take part of the global index offline and continue work on the rest of the index.

An index can be range partitioned with the following restrictions:

•  Cannot be a cluster index.

•  Cannot be an index for a cluster table.

•  A bitmap index on a partitioned table must be a local index.

---

Note:  
Each partitioned index is built as a separate B-tree structure.

---

There are several things to consider when using partitioned indexes:

•  Whether an index will be local or global.
The only usage of a global index I have seen is to get uniqueness in nonpartition key columns. You may be able to find other uses of the global index.

•  Whether an index will be prefixed or nonprefixed.

•  If the overhead of scanning multiple partitions is acceptable from a performance standpoint.

•  An understanding of when partition independence is needed.

---

Note:  
It is more expensive to scan a nonprefixed index than a prefixed index.

---

---

Note:  
For Online Transaction Processing (OLTP) systems, global indexes may provide improved performance by minimizing the number of index probes.

Historical tables should have local indexes.

Local, nonprefixed indexes may improve performance through parallel DML for Decision Support Systems (DSSs).

---

A partitioned index may be marked as index unusable (IU) in the following circumstances. When this happens, the index should be rebuilt.

•  Direct-path SQL load will mark affected local or global indexes as IU if the load is not successful.

•  If the local index maintenance is bypassed while an IMPORT PARTITION of conventional path SQL load is being performed.

•  Statements such as alter table move partition, which change the ROWIDs of local or global indexes.

•  Global indexes are marked as IU if operations like alter table truncate partition, which remove rows, are performed.

•  Operations like alter table split partition, which modify the local index definition but do not cause index rebuild.