Large Database Features In Oracle - Define the Indexing Strategy
(Page 4 of 7 )

Okay, so now you have decided how you are going to partition your data. To really get the most out of partitioning, you will need to look at some indexing strategies. There are two types of indexes applicable to partitioned tables: local and global. Let’s take a brief look at each.

Local Partitioned Indexes  Local partitioned indexes are indexes that are partitioned in the exact same manner as the data in their associated table—that is, they have a direct one-to-one relationship with the data partitions and use the same partition key. This association is illustrated in Figure 9-3; as you can see, each partition has its own associated “local” index. This drawing is based on Figure 9-2, which we saw at the beginning of the chapter. It shows how the data and indexes for each monthly subset are related and then joins it with the concept of the local index.

Because of this relationship, the following points apply to local indexes:

• You cannot explicitly add or drop a partition to/from a local index. Oracle Database 10g automatically adds or drops index partitions when related data partitions are added or dropped. Referring back to Figure 9-3, if we dropped the data partition for January 2005, the corresponding index partition would automatically be dropped as well. Likewise, it we added a new data partition for January 2006, a new index partition would automatically be created.
  
• One of the advantages of partitioning data is to allow access to other subsets while maintenance is being carried out on another partition. Since local index partitions are in line with the data partitions, this advantage still exists.
  
• Local partitioned indexes require less maintenance than global indexes.

The SQL syntax for creating a local index is presented in the next listing, which refers to the SALES table we created under the Range Partitioning section. Table 9-9 contains an explanation of the syntax.

 1 create index sales_idx_l1 on sales
   (time_id)
 2 local
 3 (partition sales_idx_200501 tablespace
   sales_ts_idx_200501,
 4  partition sales_idx_200502 tablespace
   sales_ts_idx_200502,
 5  partition sales_idx_200503 tablespace
   sales_ts_idx_200503,
...
37  partition sales_idx_200711 tablespace
   sales_ts_idx_200711,
38  partition sales_idx_200712 tablespace
   sales_ts_idx_200712,
39  partition sales_idx_max tablespace
   sales_ts_idx_max); 

FIGURE 9-3.  Local partitioned index

Lines	Important Points
2	Specifies that the index is to be local. This line alone tells Oracle
	Database 10g that the index is to be partitioned along the same ranges
	as the data.
3–39	Define the partition names and tablespaces for each partition. These
	lines are optional, but without them, Oracle Database 10g would use
	the same partition names as the data and would also place the index
	in the same tablespaces as the data—a situation that is less than ideal
	for performance!

TABLE 9-9.  Explanation of Local Partitioned Index 
                     Syntax

Ask the Expert Q:  After a table and its local indexes have been defined using range partitioning with a default maxvalue partition, how can you add more partitions as new subsets of data are received? A:  Use an alter table statement to split the default data partitions, adding your new partition ranges. For example, to add a data partition for January 2008 data in the SALES table in the previous listing, issue the following command: alter table sales  split partition sales_max at (to_date('2008-02-01','YYYY-MM-DD'))  into (partition sales_200801 tablespace sales_ts_200801,        partition sales_max tablespace sales_ts_max); This alter table command will result in the default index partition for sales_idx_l1 to also be split. However, it will use the data partition names (for example, sales_200801) and tablespaces (sales_ts_200801, for instance); remember in the local index example we explicitly specified the partition names and tablespaces for the index. Therefore, the partition names and tablespaces will need to be adjusted using alter index commands, as follows: alter index sales_idx_l1  rename partition sales_200801 to sales_idx_200801; alter index sales_idx_l1  rebuild partition sales_idx_200801 tablespace sales_ts_idx_200801; alter index sales_idx_l1  rebuild partition sales_idx_max tablespace sales_ts_idx_max;

Some other points about local partitioned indexes:

1. They can be unique, but only if the data partition key is part of the index key attributes.
   
2. Bitmap indexes on partitioned tables must be local.
   
3. Subpartitioned indexes are always local.

• They are best suited for data warehouses and decision support systems.
  
•  Local unique indexes also work well in OLTP 
   environments.

Global Partitioned Indexes  Global partitioned indexes are indexes that are not directly associated with the data partitions. Instead, their partitions are defined independently, and the partition key can be different from the data partition key. This association is illustrated in Figure 9-4. This figure is again based on Figure 9-2, seen at the beginning of the chapter. It shows that the data is partitioned by monthly ranges, with a global index partitioned by product.

One advantage of global indexes is that if partition pruning cannot occur for the data partitions due to the predicates of a query, index partition pruning may still be possible with the global partition index. Global partitioned indexes are available as

FIGURE 9-4.  Global partitioned index

either range-based or hash-based. When using range-based global indexes, you must specify a default partition with maxvalue. Let’s look at an example for creating a global partitioned index and then discuss how it would be used.

Referring again to the PHONE_USAGE table we created in the Composite Partitioning section, we have decided to create a global index on the call_type_id. Following is the SQL for this, and the explanation is presented in Table 9-10.

 1 create index phone_usg_idx_g1 on
   phone_usage (call_type_id)
 2 global
 3 partition by range (call_type_id)
 4 (partition ph_usg_idx_g1 values less than
   (2)
 5                   tablespace
   ph_usg_ts_idx_1,
 6 partition ph_usg_idx_g2 values less than
   (3)
 7                   tablespace
   ph_usg_ts_idx_2,
 8 partition ph_usg_idx_g3 values less than
   (4)
 9                   tablespace
   ph_usg_ts_idx_3,
10 partition ph_usg_idx_g4 values less than
   (5)
11                   tablespace
   ph_usg_ts_idx_4,
12 partition ph_usg_idx_gmax values less
   than (maxvalue)
13                   tablespace
   ph_usg_ts_idx_max);

Now, assume the following query is executed against the PHONE_USAGE table:

select count(*)
from phone_usage
where call_id = 3;

Without the global index we just defined, no partition pruning would occur since the query predicate does not refer to the data partition key call_date. But with the global index, only the index entries from the partition ph_usg_idx_g3 would be scanned and therefore only data records related to those entries would be used in the result set.

Lines	Important Points
2	Specifies that the index is to be global.
3	Defines the type of partitioning (here, range) for this index and the partition key (call_type_id, in this case).
4–13	Define the partition names and tablespaces for each partition.

TABLE 9-10.  Explanation for Global Partitioned Index Syntax

Some other points on global partitioned indexes:

1. They require more maintenance than local indexes, especially when you drop data partitions.
   
2. They can be unique.
   
3. They cannot be bitmap indexes.
   
4. 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.)

Next: Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index >>
 

More Oracle Articles
More By McGraw-Hill/Osborne

Comments On: Large Database Features In Oracle

· This article is an excerpt from the book "Oracle Database 10g A Beginner's Guide",...   >>> Post your comment now!

Buy this book...

This article 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). Check it out at your favorite bookstore. Buy this book now.