Oracle Index Partition and Corresponding Table Partition

Because of the ambigours specification of local index partitioning, I looked up Oracle Documentation and find some useful information.

 

 

 

 

local_partitioned_index 

The local_partitioned_index clauses let you specify that the index is partitioned on the same columns, with the same number of partitions and the same partition bounds as table. Oracle Database automatically maintains local index partitioning as the underlying table is repartitioned.

on_range_partitioned_table 

This clause lets you specify the names and attributes of index partitions on a range-partitioned table. If you specify this clause, then the number of PARTITIONclauses must be equal to the number of table partitions, and in the same order. If you omit partition, then Oracle Database generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, then the database uses the form SYS_Pn.

You cannot specify key compression for an index partition unless you have specified key compression for the index.

on_list_partitioned_table

The on_list_partitioned_table clause is identical to on_range_partitioned_table .

on_hash_partitioned_table 

This clause lets you specify names and tablespace storage for index partitions on a hash-partitioned table.

If you specify any PARTITION clauses, then the number of these clauses must be equal to the number of table partitions. If you omit partition, then Oracle Database generates a name that is consistent with the corresponding table partition. If the name conflicts with an existing index partition name, then the database uses the form SYS_Pn. You can optionally specify tablespace storage for one or more individual partitions. If you do not specify tablespace storage either here or in the STORE IN clause, then the database stores each index partition in the same tablespace as the corresponding table partition.

The STORE IN clause lets you specify one or more tablespaces across which Oracle Database will distribute all the index hash partitions. The number of tablespaces need not equal the number of index partitions. If the number of index partitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

on_comp_partitioned_table 

This clause lets you specify the name and tablespace storage of index partitions on a composite-partitioned table.

The STORE IN clause is valid only for range-hash composite-partitioned tables. It lets you specify one or more default tablespaces across which Oracle Database will distribute all index hash subpartitions. You can override this storage by specifying different tablespace storage for the subpartitions of an individual partition in the second STORE IN clause in the index_subpartition_clause.

For range-list composite-partitioned tables, you can specify default tablespace storage for the list subpartitions in the PARTITION clause. You can override this storage by specifying different tablespace storage for the list subpartitions of an individual partition in the SUBPARTITION clause of theindex_subpartition_clause.

You cannot specify key compression for an index partition unless you have specified key compression for the index.

index_subpartition_clause

This clause lets you specify names and tablespace storage for index subpartitions in a composite-partitioned table.

The STORE IN clause is valid only for hash subpartitions of a range-hash composite-partitioned table. It lets you specify one or more tablespaces across which Oracle Database will distribute all the index hash subpartitions. The SUBPARTITION clause is valid for subpartitions of both range-hash and range-list composite-partitioned tables.

If you specify any SUBPARTITION clauses, then the number of those clauses must be equal to the number of table subpartitions. If you omit subpartition, then the database generates a name that is consistent with the corresponding table subpartition. If the name conflicts with an existing index subpartition name, then the database uses the form SYS_SUBPn.

The number of tablespaces need not equal the number of index subpartitions. If the number of index subpartitions is greater than the number of tablespaces, then the database cycles through the names of the tablespaces.

If you do not specify tablespace storage for subpartitions either in the on_comp_partitioned_table clause or in the index_subpartition_clause, then Oracle Database uses the tablespace specified for index. If you also do not specify tablespace storage for index, then the database stores the subpartition in the same tablespace as the corresponding table subpartition.