Partitioned Indexes

本文深入探讨了分区索引的概念,包括本地分区索引和全局分区索引的定义、优势及创建方法。通过具体实例展示了如何在数据仓库环境中使用本地分区索引提高可用性与性能,以及在OLTP应用程序中利用全局分区索引来实现快速访问与数据完整性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Partitioned Indexes

       A partitioned index is an index that, like a partitioned table, has been decomposed into smaller and more manageable pieces. Global indexes are partitioned independently of the table on which they are created, whereas local indexes are automatically linked to the partitioning method for a table. Like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability.
       The following graphic shows index partitioning options.



Local Partitioned Indexes

       In a local partitioned index, the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as its table. Each index partition is associated with exactly one partition of the underlying table, so that all keys in an index partition refer only to rows stored in a single table partition. In this way, the database automatically synchronizes index partitions with their associated table partitions, making each table-index pair independent.
       Local partitioned indexes are common in data warehousing environments. Local indexes offer the following advantages:
       ■Availability is increased because actions that make data invalid or unavailable in a partition affect this partition only.
       ■Partition maintenance is simplified. When moving a table partition, or when data ages out of a partition, only the associated local index partition must be rebuilt or maintained. In a global index, all index partitions must be rebuilt or maintained.
       ■If point-in-time recovery of a partition occurs, then the indexes can be recovered to the recovery time (see "Data File Recovery" on page 18-14). The entire index does not need to be rebuilt.
       Example 4–4 shows the creation statement for the partitioned hash_sales table, using the prod_id column as partition key.         Example 4–5 creates a local partitioned index on the time_id column of the hash_sales table.

Example 4–5 Local Partitioned Index
CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;

       In Figure 4–4, the hash_products table has two partitions, so hash_sales_idx has two partitions. Each index partition is associated with a different table partition. Index partition SYS_P38 indexes rows in table partition SYS_P33, whereas index partition SYS_P39 indexes rows in table partition SYS_P34.


       You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
       Like other indexes, you can create a bitmap index on partitioned tables. The only restriction is that bitmap indexes must be local to the partitioned table—they cannot be global indexes. Global bitmap indexes are supported only on nonpartitioned tables.

Global Partitioned Indexes

       A global partitioned index is a B-tree index that is partitioned independently of the underlying table on which it is created. A single index partition can point to any or all table partitions, whereas in a locally partitioned index, a one-to-one parity exists between index partitions and table partitions.
       In general, global indexes are useful for OLTP applications, where rapid access, data integrity, and availability are important. In an OLTP system, a table may be partitioned by one key, for example, the employees.department_id column, but an application may need to access the data with many different keys, for example, by employee_id or job_id. Global indexes can be useful in this scenario.
       You can partition a global index by range or by hash. If partitioned by range, then the database partitions the global index on the ranges of values from the table columns you specify in the column list. If partitioned by hash, then the database assigns rows to the partitions using a hash function on values in the partitioning key columns.
As an illustration, suppose that you create a global partitioned index on the time_range_sales table from Example 4–2. In this table, rows for sales from 1998 are stored in one partition, rows for sales from 1999 are in another, and so on. Example 4–6 creates a global index partitioned by range on the channel_id column.

Example 4–2 Range-Partitioned Table
CREATE TABLE time_range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
);


Example 4–6 Global Partitioned Index
CREATE INDEX time_channel_sales_idx ON time_range_sales (channel_id)
GLOBAL PARTITION BY RANGE (channel_id)
(PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN (4),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

       As shown in Figure 4–5, a global index partition can contain entries that point to multiple table partitions. Index partition p1 points to the rows with a channel_id of 2, index partition p2 points to the rows with a channel_id of 3, and index partition p3 points to the rows with a channel_id of 4 or 9.



### Oracle 分区索引概述 在Oracle数据库中,分区索引是一种用于提升性能的技术,通过将单个大索引分割成较小的部分来实现。这不仅有助于加速数据检索过程,还能改善I/O效率和简化维护工作[^1]。 #### 索引分区的分类 分区索引主要分为两类:本地分区索引(Local Partitioned Indexes) 和 全局分区索引(Global Partitioned Indexes)[^2]。每种类型又细分为带前缀(Prefixed)和不带前缀(Non-prefixed)两种形式: - **本地分区索引**:与表分区紧密关联,通常每个表分区对应一个独立的索引分区。 - **全局分区索引**:跨越整个表范围,适用于所有记录而不局限于特定分区内的数据。 #### 创建分区索引 为了创建有效的分区索引,在设计阶段需考虑如下因素: 1. 表结构及其预期的数据分布模式; 2. 查询频率较高的列应优先考虑建立索引; 3. 对于频繁更新的操作,则要评估其对现有索引的影响程度。 下面是一个简单的例子展示如何定义带有日期范围分区策略的B-tree索引: ```sql CREATE INDEX sales_idx ON sales(sale_date) GLOBAL PARTITION BY RANGE (sale_date)( PARTITION p_jan VALUES LESS THAN(TO_DATE('02/01/2023','MM/DD/YYYY')), PARTITION p_feb VALUES LESS THAN(TO_DATE('03/01/2023','MM/DD/YYYY')) ); ``` 此命令会针对`sales`表按月划分索引分区,并指定具体的边界条件。 #### 维护与优化 随着时间推移,随着新数据不断加入到系统当中,定期审查现有的索引配置变得至关重要。特别是对于那些经历了大量插入、删除操作的对象而言更是如此。常见的做法包括但不限于: - 定期分析统计信息以保持CBO(Cost-Based Optimizer)的有效性; - 当发现某个分区增长过快时及时调整其容量规划; - 如果某些旧的历史数据很少再被访问,则可考虑将其归档或迁移至低成本存储介质上。 此外,利用并行处理功能可以在不影响正常服务的前提下完成复杂的重组任务,比如在线重构建大型索引来消除碎片化问题[^3]。 #### 性能调优建议 选择合适的候选键是成功实施高效索引方案的关键所在。理想情况下,所选字段应当具备良好的区分度——即不同值的数量接近总行数(`NUM_DISTINCT ≈ NUM_ROWS`),同时空缺率较低(`NUM_NULLS`尽可能少)[^4]。然而值得注意的是,最终决策还需综合考量应用逻辑需求及其他潜在影响因子。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值