Databases Reference
In-Depth Information
C H A P T E R 6
■ ■ ■
Partitioned Indexes
If you use partitioned tables as part of your application, it is likely, if not imperative, that you use
partitioned indexes to complement the advantages gained by using table partitioning. Usually, having
partitioned table and indexes go hand in hand—when there's one, there's usually both. This is common,
but not essential. It is possible to have partitioned tables without partitioned indexes, and it is possible
to have a non-partitioned table with partitioned indexes. There are several factors that affect the design
of the database tables and indexes, including:
•
Application data loading requirements (DML)
•
Is it an OLTP system?
•
Is it a data warehouse?
•
Client query requirements
•
Data volume
Data purging requirements
Deciding on whether your indexes should be partitioned will largely be based on the answers to the
aforementioned factors. All of the above factors are important, but often it boils down to data volume.
The volume of your data affects load speed, query speed, and data purging speed. Obviously, as volume
increases, design considerations must include factors to improve the speed of all these factors. All this
said, some key reasons to have partitioned indexes include:
•
•
You can perform maintenance on only a portion of the index based on activity in
the table.
•
You can rebuild only portions of an index.
You can spread an index out evenly—that is, you can always have a balanced
index.
Within this chapter are examples of how to create partitioned indexes. In addition, there are some
examples of partitioned index usage, along with examples of operations that are performed on the
database that can affect partitioned indexes.
•
