Normal indexes

This topic describes the conversion scope of CREATE INDEX DDL operations for normal indexes during data migration from an Oracle database to an Oracle tenant of OceanBase Database.

Syntax

table_index_clause:
[ schema. ] table [ t_alias ]
(index_expr [ ASC | DESC ]
  [, index_expr [ ASC | DESC ] ]...)
  [ index_properties ]

index_properties:
[ { { global_partitioned_index
    | local_partitioned_index
    }
  | index_attributes
  }...
| INDEXTYPE IS { domain_index_clause
               | XMLIndex_clause
               }
]

global_partitioned_index:
GLOBAL PARTITION BY
   { RANGE (column_list)
        (index_partitioning_clause)
   | HASH (column_list)
        { individual_hash_partitions
        | hash_partitions_by_quantity
        }
   }


local_partitioned_index:
LOCAL
[ on_range_partitioned_table
| on_list_partitioned_table
| on_hash_partitioned_table
| on_comp_partitioned_table
]

index_attributes:
[ { physical_attributes_clause
  | logging_clause
  | ONLINE
  | TABLESPACE { tablespace | DEFAULT }
  | index_compression
  | { SORT | NOSORT }
  | REVERSE
  | VISIBLE | INVISIBLE
  | partial_index_clause
  | parallel_clause
  }...
]

domain_index_clause:
indextype
   [ local_domain_index_clause ]
   [ parallel_clause ]
   [ PARAMETERS ('ODCI_parameters') ]

XMLIndex_clause:
[XDB.] XMLINDEX [ local_XMLIndex_clause ]
                [ parallel_clause ]
  [ XMLIndex_parameters_clause ]

Supported operations

• Specifying the ascending or descending order by using the ASC | DESC option is supported. Example:

  CREATE INDEX IDX ON T(C ASC);
  CREATE UNIQUE INDEX IDX ON T(C DESC);
  

• Creating composite indexes is supported.

• Creating function indexes is supported. Example:

  CREATE INDEX IDX ON T(SUBSTR(C,1,4));
  

• Specifying the REVERSE option for the index_attributes clause is supported. Example:

  CREATE INDEX IDX ON T(C) REVERSE;
  

• Defining global index partitions by using the global_partitioned_index clause is supported. Syntax of global_partitioned_index:

  table_index_clause:
  [ schema. ] table [ t_alias ]
  (index_expr [ ASC | DESC ]
    [, index_expr [ ASC | DESC ] ]...)
    [ index_properties ]
  
  index_properties:
  [ { { global_partitioned_index
      | local_partitioned_index
      }
    | index_attributes
    }...
  | INDEXTYPE IS { domain_index_clause
                | XMLIndex_clause
                }
  ]
  
  global_partitioned_index:
  GLOBAL PARTITION BY
    { RANGE (column_list)
          (index_partitioning_clause)
    | HASH (column_list)
          { individual_hash_partitions
          | hash_partitions_by_quantity
          }
    }
  
  local_partitioned_index:
  LOCAL
  [ on_range_partitioned_table
  | on_list_partitioned_table
  | on_hash_partitioned_table
  | on_comp_partitioned_table
  ]
  
  index_attributes:
  [ { physical_attributes_clause
    | logging_clause
    | ONLINE
    | TABLESPACE { tablespace | DEFAULT }
    | index_compression
    | { SORT | NOSORT }
    | REVERSE
    | VISIBLE | INVISIBLE
    | partial_index_clause
    | parallel_clause
    }...
  ]
  
  domain_index_clause:
  indextype
    [ local_domain_index_clause ]
    [ parallel_clause ]
    [ PARAMETERS ('ODCI_parameters') ]
  
  XMLIndex_clause:
  [XDB.] XMLINDEX [ local_XMLIndex_clause ]
              [ parallel_clause ]
  [ XMLIndex_parameters_clause ]
  

  • Creating GLOBAL RANGE index partitions by using the GLOBAL PARTITION BY RANGE(column_name_list) clause is supported.

  • Creating GLOBAL HASH index partitions by using the GLOBAL PARTITION BY HASH(column_name_list) clause is supported.

  • Defining RANGE partitions by using the index_partitioning_clause clause is supported.

  • Defining HASH partitions by using the individual_hash_partitions clause is supported. For more information, see the "User-defined HASH partitions" section of the User-defined partitions topic.

  • Specifying the number of HASH partitions by using the hash_partitions_by_quantity clause is supported. For more information, see the "Number of HASH partitions" section of the User-defined partitions topic.

Unsupported operations

• Defining a domain index by using the domain_index_clause clause is not supported. An error will be returned when you perform this operation.

• Defining an xml index by using the XMLIndex_clause clause is not supported. An error will be returned when you perform this operation.

Ignored clauses and options

• The options that specify the physical attributes and tablespaces in the segment_attributes_clause subclause of the index_partitioning_clause clause for defining the GLOBAL RANGE index partitions.

• The local_partitioned_index clause for defining index partitions.

• Only the REVERSE option is supported for the index_attributes clause. Other options are not supported and will be ignored.