ApsaraDB for OceanBase:Normal indexes

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 DDL

• Specifying the ascending or descending order by using the ASC | DESC option is supported. Here is an 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. Here is an example:

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

• Specifying the REVERSE option for the index_attributes clause is supported. Here is an example:

  CREATE INDEX IDX ON T(C) REVERSE;

• Defining global index partitions by using the global_partitioned_index clause is supported. The syntax of global_partitioned_index is as follows:

  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 User-defined HASH partitions.

  • Specifying the number of HASH partitions by using the hash_partitions_by_quantity clause is supported. For more information, see Specifying the number of HASH partitions.

Unsupported DDL

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

• Defining an xml index by using the XMLIndex_clause clause is not supported. An error is 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 are ignored if they are specified.