This section describes Oracle partition indexes in detail.

Oracle partition indexes may be unfamiliar to many new users who are new to Oracle databases. The following describes the knowledge of Oracle partition indexes in detail. We hope that you can learn more about Oracle partition indexes.

Oracle partition index Syntax:

 
 

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

Oracle partition indexes are divided into the following 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.

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)

You can only create a HashTable or composite partition table in the parent table. 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]]]]) 
 
 

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

The number of specified partitions must be the same as that of the parent table.

 
 

  
  
LOCAL (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]]]]) 
 
 

2 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)  
  
  
( PARTITION partition VALUES LESS THAN (value_list)  
  
  
[LOGGING|NOLOGGING]  
  
  
[TABLESPACE {tablespace|DEFAULT}]  
  
  
[PCTFREE int]  
  
  
[PCTUSED int]  
  
  
[INITRANS int]  
  
  
[MAXTRANS int]  
  
  
[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 PartitionName  Update Global Indexes 
 
 

Implementation of oracle re-Indexing

Complete Oracle File System Parsing

ORACLE system table and data dictionary View

In-depth analysis of Oracle ERP system module

Detailed explanation of four categories of Oracle index Scanning