partitioned indexes

         local partitioned index

                   CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;

                   advantages

                            availability is increased because actions that make data invalid or unavailable in 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. the entire index does not need to be rebuilt                                 

                   local partitioned index storage

                            a local index partition is stored in its own segment,each segment contains a portion of the total index data.thus,a local index made up of four partitions is not stored in single index segment,but in for separate segments

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));

    advantages

             OLTP applications,where rapid access,data integrity,and availability are important 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.

             note partition a global index by range or by hash

                       partition a global index by range:global index on the ranges of values from the table columns you specify in the column list

                       partition a global index by hash:database assigns rows to the partitions using a hash function on values in the partitioning key columns

partitioned index-organized tables

                   advantages

                            IOT by range,list or hash. partitioning is usefull for providing improved manageability,availability,and performance for IOTs

                   note following characteristics of partitioned IOTs

                            partition columns must be a subset of primary key columns

                            secondary indexes can be partitioned locally and globally

                            overflow data segments are always equipartitioned with the table partitions

附表：

分区索引

1、   本地分区索引

语法： CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;

优点： ① 增强了可用性（因为无效的数据或无效的分区仅仅影响这个分区）

② 简化了维护（当迁移一个表分区或数据分区老化，仅仅本地分区索引必须重建或维护）

           ③ 指定时间点恢复，当索引必须恢复到一个恢复时间点，索引条目不需要重建

本地分区索引存储： 像表分区，一个本地索引分区存储它自己拥有的段中，每个段包含全部索引数据的一部分。这样，一个本地索引四分区不能存储一个单独索引段中，可以四个单独段中。

2、   全局分区索引

语法： 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));

                   优点： 通常，全局索引在 OLTP 应用，快速存取，数据完整性，可用性是很有用的。在 OTLP 系统中， 一个表可能是由一个关键的分区，例如： employees.department_id 列，但是一个应用可能需要指向许多不同建的数据，例如： employee_id 或 job_id 。这种方案全局索引是有用的。

              注意：

                     Partition a global index by range

                     数据库分区全局索引的表列的列列表中指定的值的范围

                     Parttion a global index by hash

                     数据库中指定行的分区使用一个哈希函数的分区键列中的值

索引组织表分区

              优点： IOT 范围，列表，哈希。改进管理，可用性，性能

              注意：分区索引组织表特性

                     ① 分区列必须是主键列的一个子集

② 辅助索引可以在本地分区或全局分区

③ Overflow 数据段总是均等的存储表分区内