| Team Fly | |
Some other points on global partitioned indexes:
They require more maintenance than local indexes, especially when you drop data partitions.
They can be unique.
They cannot be bitmap indexes.
They are best suited for OLTP systems for direct access to specific records.
Prefixed and Nonprefixed Partition Indexes In your travels through the world of partitioning, you will hear the terms prefixed and nonprefixed partition indexes. These terms apply to both local and global indexes. An index is prefixed when the leftmost column of the index key is the same as the leftmost column of the index partition key. If the columns are not the same, the index is nonprefixed. That's all well and good, but what affect does it have?
It is a matter of performance—nonprefixed indexes cost more, from a query perspective, than prefixed indexes. When a query is submitted against a partitioned table and the predicate(s) of the query include the index keys of a prefixed index, then pruning of the index partition can occur. If the same index was nonprefixed instead, then all index partitions may need to be scanned. (Scanning of all index partitions will depend on the predicate in the query and the type of index, global or local—if the data partition key is included as a predicate and the index is local, then the index partitions to be scanned will be based on pruned data partitions.)
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.
1. Create two tablespaces called inv_ts_2007q1 and inv_2007q2 using the following SQL statements. These will be used to store data partitions.
create tablespace inv_ts_2007q1
datafile 'inv_ts_2007q1_1.dat' size 10m;
create tablespace inv_ts_2007q2
datafile 'inv_ts_2007q2_1.dat' size 10m;
| Team Fly | |