대용량 데이터 혹은 테이블시 고려해볼 사항

Miscellaneous Information about Creating Indexes on Partitioned Tables

You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.

Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

Using Partitioned Indexes in OLTP Applications

Here are a few guidelines for OLTP applications:

• Global indexes and unique, local indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes.

• Local indexes offer better availability when there are partition or subpartition maintenance operations on the table.

• Hash-partitioned global indexes offer better performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

Using Partitioned Indexes in Data Warehousing and DSS Applications

Here are a few guidelines for data warehousing and DSS applications:

• Local indexes are preferable because they are easier to manage during data loads and during partition-maintenance operations.

• Local indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.

Partitioned Indexes on Composite Partitions

Here are a few points to remember when using partitioned indexes on composite partitions:

• Subpartitioned indexes are always local and stored with the table subpartition by default.

• Tablespaces can be specified at either index or index subpartition levels.