分割槽表索引實踐案例

分割槽表的全域性索引圖示如下:

建立語法為：
CREATE [url=]INDEX[/url] INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1)
  GLOBAL PARTITION BY RANGE(COL1)
         PARTITION IDX_P1 values less than (1000000),
         PARTITION IDX_P2 values less than (2000000),
         PARTITION IDX_P3 values less than (MAXVALUE)
  )
LOCAL索引結構圖如下示:

建立語法為：
CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;
也可按照分割槽表的的分割槽結構給與一一定義，索引的分割槽將得到重新命名。
分割槽上的點陣圖索引只能為LOCAL索引，不能為GLOBAL全域性索引。
=================================================================================================

分割槽索引分為本地(local index)索引和全域性索引(global index)。
---------------&gt>官方指南:

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 ). The entire index does not need to be rebuilt.

shows the creation statement for the partitioned hash_sales table, using the prod_id column as partition key. creates a local partitioned index on the time_id column of the hash_sales table.

CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;

In , 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.

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 . In this table, rows for sales from 1998 are stored in one partition, rows for sales from 1999 are in another, and so on. creates a global index partitioned by range on the channel_id column.

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 , 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.