AntDB-Oracle Compatibility Developer's Manual P3–8
Published: 2023-08-31 08:58:42

#### CREATE INDEX

**Name**

CREATE INDEX - define a new index

**Summary**

CREATE \[ UNIQUE ] INDEX name ON table

( { column \| ( expression ) } )

\[ TABLESPACE tablespace ]

**Description**

The CREATE INDEX command is used to create an index on the specified table. Indexes are mainly used to improve the performance of the database (incorrect use of indexes can reduce performance).

The keyword field of an index can be a column name or an expression written in parentheses. We can specify multiple fields to create multi-column indexes.

An index field can be an expression calculated from the value of a column or multiple column fields in a table record. We can use this feature to quickly access the transformed data based on the underlying data. For example, the index value calculated by UPPER(col) allows the clause WHERE UPPER(col) = 'JIM' to use the index.

AntDB provides indexes for B-tree structures.

By default, indexes cannot be used for the IS NULL clause.

All functions and operators used in the index definition must be immutable, i.e., their results depend only on their arguments and are independent of outside influences (e.g., the contents of another table or changes in the current system time). This restriction ensures that the behavior of the index is clearly defined, and if a user-defined function is used in the index, it must be remembered that the function is marked as immutable when it is created.

**argument**

UNIQUE

This parameter tells the system to check for duplicate values in the table when creating an index (if the data already exists) and each time data is added. If an insert or update operation is performed that produces duplicate values, an error will be generated.

name

The name of the index to be created. It does not contain the schema name. An index can only be created in the schema where its source table is located.

table

The name of the table for which the index is to be created (can be referenced in a schema-qualified manner).

column

The name of the column in the table.

expression

An expression based on one or more columns in a table. As the syntax indicates, expressions must normally be enclosed in parentheses. However, if the expression is in the form of a function call, the parentheses can be left out.

tablespace

The tablespace used to create the index. If this parameter is not specified, then the tablespace specified by the default\_tablespace parameter is used. If the default\_tablespace parameter value is an empty string, then the database's default tablespace is used.

**Note**

Up to 32 fields can be specified for multi-column indexes.

**Example**

Create a B-tree index on the column with the name ename in the emp table.

```
CREATE INDEX name_idx ON emp (ename); 
```

Create an index as above on the tablespace index\_tblspc.

```
CREATE INDEX name_idx ON emp (ename) TABLESPACE index_tblspc; 
```

#### CREATE PROCEDURE

**Name**

CREATE PROCEDURE -- Define a new stored procedure. Before executing this operation, set to turn PLSQL\_MODE on, and then set this parameter to OFF after creating the procedure, otherwise, the transaction will not be committed automatically, and the command "/" can commit the transaction. The method is as follows.

\\set PLSQL\_MODE ON

\\set PLSQL\_MODE OFF

**Summary**

CREATE \[ OR REPLACE ] PROCEDURE name

(\[argname \[ IN \| IN OUT \| OUT ] argtype \[ DEFAULT value ]

\[, ...] ] )

{ IS \| AS }

\[ declaration; ] \[, ...]

BEGIN

statement; \[...]

\[ EXCEPTION

{ WHEN exception \[ OR exception ] \[...] THEN

statement; \[, ...] } \[, ...]

]

END \[ name ]