Oracle partition index details

Syntax:

Table Index   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_nameON [schema.]table_name [tbl_alias](col [ASC | DESC]) index_clause index_attribs

Index_clses:
There are two situations:

1. Local Index

The storage location of the index information depends on the Partition information of the parent table. In other words, to create such an index, the parent table must be Partition.

1.1 The index information is stored in the tablespace where the partition of the parent table is located. However, you can only create a HashTable or composite partition table in the parent table.

    LOCAL STORE IN (tablespace)

1.2 you can only create a HashTable or composite partition table in the parent table. And the specified number of partitions must be the same as the number of partitions IN the parent table. local store in (tablespace)
(PARTITION [partition
[LOGGING | NOLOGGING]
[TABLESPACE {tablespace | DEFAULT}]
[PCTFREE int]
[PCTUSED int]
[INITRANS int]
[MAXTRANS int]
[STORAGE storage_clause]
[Store in {tablespace_name | DEFAULT]
[SUBPARTITION [subpartition [TABLESPACE tablespace])

1.3 The index information is stored in the tablespace where the partition of the parent table is located. This syntax is the simplest and is also the most commonly used partition index creation method.

Local
1.4 and the number of specified partitions must be the same as that of the parent table. LOCAL (Partition [Partition[LOGGING | NOLOGGING] [TABLESPACE {Tablespace| DEFAULT}] [PCTFREEInt] [PCTUSEDInt] [INITRANSInt] [MAXTRANSInt] [STORAGE storage_clause] [store in {Tablespace_name| DEFAULT] [SUBPARTITION [Subpartition[TABLESPACETablespace])

Global Index

The storage location of the index information is completely irrelevant to the Partition information of the parent table. It doesn't even matter if the parent table is a partition table. Syntax:Global partition by range (col_list) (PARTITIONPartitionValues less (Value_list) [LOGGING | NOLOGGING] [TABLESPACE {Tablespace| DEFAULT}] [PCTFREEInt] [PCTUSEDInt] [INITRANSInt] [MAXTRANSInt] [STORAGE storage_clause])

However, in this case, if the parent table is a partition table, you must update Global Index to delete all partitions of the parent table. Otherwise, the Index information is incorrect.
Alter table TableName drop partition PartitionNameUpdate Global Indexes
I did not go into any situations and which method is more effective. But my personal feeling is:Simple is the best