Define the Indexing Strategy - Oracle

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

TABLE 9-9.  Explanation of Local Partitioned Index 
                     Syntax

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.

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.

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.)