Apache Derby Indexes

Derby - DML Statements

Derby - Schemas

Apache Derby indexes are the data structure that is used to improve the performance of a query if it is used in the where clause. If we create an index for a table then that index will also take its own space to store index records. If we use the index to run the SQL then the particular rows will be scanned only despite searching the complete row of a database and the query result will be faster.

Let us see the Index type and process to create it.

Create Index

Create Index command is used to create an index on a table.

Syntax:

ij> CREATE [UNIQUE] INDEX index-Name
ON table-Name ( Simple-column-Name [ ASC | DESC ]
    [ , Simple-column-Name [ ASC | DESC ]] * )

We will create a table named “Emp_Detail” and create an index on the “Emp_Salary” column.

Command:

ij> CREATE TABLE Emp_Detail ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Emp_Name VARCHAR(255),
   Emp_Salary INT NOT NULL,
   Emp_Location VARCHAR(255),
   Emp_Phone BIGINT
)

ij> CREATE INDEX emp_index_sal on Emp_Detail (Emp_Salary);

Output:

Create Unique Index

Unique indexes are used to maintain the uniqueness of data. It will not allow duplicate records.

Syntax:

ij> CREATE UNIQUE INDEX index_name on table_name (column_name);

We will create a unique index on the “Emp_Phone” column of the “Emp_Detail” table.

Command:

ij> CREATE UNIQUE INDEX unique_ind_emp_phone on Emp_Detail (Emp_Phone);

Output:

Create Composite Index

The composite index can be created on more than one column.

Syntax:

ij> CREATE INDEX index_name on table_name (column_name1, column_name2);

We will create a composite index on the “Emp_Name, Emp_Location” columns.

Command:

ij> CREATE INDEX emp_comp_index_sal on Emp_Detail (Emp_Name,Emp_Location);

Output:

Show Index

Show index command is used to display the index name created on the table.

Syntax:

ij> SHOW INDEXES FROM table_name;

Command:

ij> SHOW INDEXES FROM Emp_Detail;

Output:

Drop Index

Drop Index is used to drop an index created on a table.

Syntax:

ij> DROP INDEX index_name;

Command:

ij> DROP INDEX unique_ind_emp_phone;

Output: