Oracle compatible mode

This topic describes the conversion scope of CREATE INDEX DDL operations for ordinary table indexes during data migration from an Oracle database to the Oracle compatible mode 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 ASC | DESC attribute to indicate the ascending or descending order. Example:

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

• Creating composite indexes.

• Creating function indexes. Example:

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

• Specifying the REVERSE attribute for the index_attributes option. Example:

  CREATE INDEX IDX ON T(C) REVERSE;
  

• Defining global index partitions by using the global_partitioned_index option. Syntax of the global_partitioned_index option:

  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) option.

  • Creating GLOBAL HASH index partitions by using the GLOBAL PARTITION BY HASH(column_name_list) option.

  • Specifying range partitions by using the index_partitioning_clause option.

  • Specifying hash partitions by using the individual_hash_partitions option, for more information, see User-defined hash partitions.

  • Specifying hash partitions by using the hash_partitions_by_quantity option. For more information, see Specify the number of hash partitions.

Unsupported operations

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

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

Ignored clauses and options

• The segment_attributes_clause subclause in the index_partitioning_clause option for defining the physical attributes and tablespace storage for GLOBAL RANGE index partitions.

• The local_partitioned_index option for defining index partitions.

• The index_attributes option supports only the REVERSE attribute for defining reverse indexes. Other attributes specified for the index_attributes option will be ignored.