Databases Reference
In-Depth Information
Maintaining Indexes on Partitioned Tables
Having partitioned tables and indexes offer many advantages, but there are maintenance implications
when creating partitioned tables and indexes that must be considered and taken into account when
designing your application. The maintenance activities vary based on the circumstances and your
specific application design and database design. For your specific application, this includes the DML
patterns of your application and the volume of data that is being insert, updated, and deleted. DML
activity is slowed by the presence of indexes. In some applications, particularly in the data warehouse
arena, it is beneficial to perform partition-level operations in order to speed up the overall application
processing time. Partition-level operations can have significant impact on indexes, depending on what
type of indexes you are using within your application.
In the examples that follow, you can see the effect partition-level operations have on the different types
of indexes. Each of the examples uses a test table containing employee data. On this table, there are
three indexes: one non-partitioned index (EMPLOYEES_PART_I1), one locally partitioned index
(EMPLOYEES_PART_LI1), and one globally partitioned index (EMPLOYEES_PARTTEST_GI1). You'll see
the impact a partition-level operation on the table has on each index within the table.
■
Note
If any of the following operations are performed on an empty partition, all associated indexes are
unaffected. This is true regardless of whether they are locally partitioned, globally partitioned, or non-partitioned
indexes.
Adding a Partition
Adding partitions to a table is the least intrusive to existing indexes on your table. In fact, regardless of
the type of index you choose to use (be it locally partitioned, globally partitioned, or non-partitioned),
none of the index partitions that exist are affected, and there are no specific index-level operations
needed after the partition add operation. In the following example, you are adding a partition to the test
employees table for historical 2010 data:
SQL> alter table employees_parttest add partition p2010
2 values less than ('2011-01-01') tablespace users;
Table altered.
After adding the partition, you can run the following query to determine the impact the
add
partition
operation had on the existing indexes on the table:
SQL> SELECT index_name, null partition_name, status
2 FROM user_indexes
3 WHERE table_name = 'EMPLOYEES_PARTTEST'
4 AND partitioned = 'NO'
5 UNION
6 SELECT index_name, partition_name, status
7 FROM user_ind_partitions
8 WHERE index_name in
