Informatica Tutorials

Big Data Analytics

Guidelines for Partitioning Indexes

When deciding how to partition indexes on a table, consider the mix of applications
that need to access the table. There is a trade-off between performance on the one hand
and availability and manageability on the other. Here are some of the guidelines you
should consider:
■ For OLTP applications:
– Global indexes and local prefixed indexes provide better performance than
local nonprefixed indexes because they minimize the number of index
partition probes.
– Local indexes support more availability when there are partition or
subpartition maintenance operations on the table. Local nonprefixed indexes
are very useful for historical databases.
■ For DSS applications, local nonprefixed indexes can improve performance because
many index partitions can be scanned in parallel by range queries on the index
For example, a query using the predicate "acctno between 40 and 45" on the table
checks of Figure 5–4 on page 5-30 causes parallel scans of all the partitions of the
nonprefixed index ix3. On the other hand, a query using the predicate deptno
BETWEEN 40 AND 45 on the table deptno of Figure 5–5 on page 5-31 cannot be
parallelized because it accesses a single partition of the prefixed index ix1.
■ For historical tables, indexes should be local if possible. This limits the impact of regularly scheduled drop partition operations.
■ Unique indexes on columns other than the partitioning columns must be global
because unique local nonprefixed indexes whose key does not contain the
partitioning key are not supported.

Related Posts Plugin for WordPress, Blogger...

Please Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More

Follow TutorialBlogs
Share on Facebook
Tweet this Blog
Add Blog to Technorati