Home arrow Oracle arrow Page 4 - Large Database Features In Oracle

Define the Indexing Strategy - 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).

  1. Large Database Features In Oracle
  2. Implement Data Partitioning
  3. Select the Type of Partitioning
  4. Define the Indexing Strategy
  5. Project 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index
  6. Data Compression
  7. Parallel Processing Configuration
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 26
December 08, 2005

print this article



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
2 local
3 (partition sales_idx_200501 tablespace
4  partition sales_idx_200502 tablespace
5  partition sales_idx_200503 tablespace
37  partition sales_idx_200711 tablespace
38  partition sales_idx_200712 tablespace
39  partition sales_idx_max tablespace

FIGURE 9-3.  Local partitioned index


Important Points


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.


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 

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 

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
 5                   tablespace
 6 partition ph_usg_idx_g2 values less than
 7                   tablespace
 8 partition ph_usg_idx_g3 values less than
 9                   tablespace
10 partition ph_usg_idx_g4 values less than
11                   tablespace
12 partition ph_usg_idx_gmax values less
   than (maxvalue)
13                   tablespace

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.


Important Points


Specifies that the index is to be global.


Defines the type of partitioning (here, range) for this index and the partition key (call_type_id, in this case).


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

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: