An index in a table is nothing but a pointer to its data. These are used to speed up the data retrieval from a table.
If we use indexes, the INSERT and UPDATE statements get executed in a slower phase. Whereas SELECT and WHERE get executed with in lesser time.
Creating an Index
The CREATE INDEX statement is used for creating a new Index in a table in Derby database.
Syntax
Following is the syntax of the CREATE INDEX statement −
CTREATE INDEX index_name on table_name (column_name);
Example
Suppose we have created a table named Employees in Apache Derby as shown below.
CREATE TABLE Emp ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, Name VARCHAR(255), Salary INT NOT NULL, Location VARCHAR(255), Phone_Number BIGINT );
The following SQL statement creates an index on the column named Salary in the table Employees.
ij> CREATE INDEX example_index on Emp (Salary); 0 rows inserted/updated/deleted
Creating a UNIQUE index
In Apache Derby, UNIQUE indexes are used for data integration. Once you create a UNIQUE index on a column in a table, it does not allow duplicate values.
Syntax
Following is the syntax of creating a unique index.
CREATE UNIQUE INDEX index_name on table_name (column_name);
Example
Following example creates a UNIQUE index on the column Id of the table Employee.
ij> CREATE UNIQUE INDEX unique_index on Emp (Phone_Number); 0 rows inserted/updated/deleted
Once you have created a unique index on a column, you cannot enter same values for that column in another row. In short, a column which is has a UNIQE index will not allow duplicate values.
Insert a row in the Emp table as shown below
ij> INSERT INTO Emp(Name, Salary, Location, Phone_Number) VALUES
('Amit', 45000, 'Hyderabad', 9848022338);
1 row inserted/updated/deleted
Since we have created a unique index on the column Phone_No, if you ty to enter the same value as in the previous record, it shows an error.
ij> INSERT INTO Emp(Name, Salary, Location, Phone_Number) VALUES
('Sumit', 35000, 'Chennai', 9848022338);
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'UNIQUE_INDEX' defined on 'EMP'.
Creating a COMPOSITE index
You can create a single index on two rows and it is called Composite index.
Syntax
Following is the syntax of the composite index.
CREATE INDEX index_name on table_name (column_name1, column_name2);
Example
Following index creates a composite index on the columns Name and Location.
ij> CREATE INDEX composite_index on Emp (Name, Location); 0 rows inserted/updated/deleted
Displaying the Indexes
The SHOW INDEXES query displays the list of indexes on a table.
Syntax
Following is the syntax of the SHOW INDEXES statement −
SHOW INDEXES FROM table_name;
Example
Following example, i displays the indexes on the table Employees.
ij> SHOW INDEXES FROM Emp;
This produces the following result.
ij> SHOW INDEXES FROM Emp; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- EMP |PHONE_NUMBER|false |3 |A |NULL |NULL EMP |NAME |true |3 |A |NULL |NULL EMP |LOCATION |true |3 |A |NULL |NULL EMP |SALARY |true |3 |A |NULL |NULL 4 rows selected
Dropping Indexes
The Drop Index statement deletes/drops the given index on a column.
Syntax
Following is the syntax of the DROP INDEX statement.
DROP INDEX index_name;
Example
Following example drops an indexes named composite_index and unique_index created above.
ij> DROP INDEX composite_index; 0 rows inserted/updated/deleted ij>Drop INDEX unique_index; 0 rows inserted/updated/deleted
Now, if you verify the list of indexes you can see index on one column since we have deleted the remaining.
ij> SHOW INDEXES FROM Emp; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- EMP |SALARY |true |3 |A |NULL |NULL 1 row selected
Handling Indexes using JDBC program
Following JDBC program demonstrates how to create drop indexes on a column in a table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class IndexesExample {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:MYDATABASE;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating the Emp table
String createQuery = "CREATE TABLE Emp( "
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), "
+ "Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "Phone_Number BIGINT )";
stmt.execute(createQuery);
System.out.println("Table created");
System.out.println(" ");
//Creating an Index on the column Salary
stmt.execute("CREATE INDEX example_index on Emp (Salary)");
System.out.println("Index example_index inserted");
System.out.println(" ");
//Creating an Unique index on the column Phone_Number
stmt.execute("CREATE UNIQUE INDEX unique_index on Emp (Phone_Number)");
System.out.println("Index unique_index inserted");
System.out.println(" ");
//Creating a Composite Index on the columns Name and Location
stmt.execute("CREATE INDEX composite_index on Emp (Name, Location)");
System.out.println("Index composite_index inserted");
System.out.println(" ");
//listing all the indexes
System.out.println("Listing all the columns with indexes");
//Dropping indexes
System.out.println("Dropping indexes unique_index and, composite_index ");
stmt.execute("Drop INDEX unique_index");
stmt.execute("DROP INDEX composite_index");
}
}
Output
On executing, this generates the following result
Table created Index example_index inserted Index unique_index inserted Index composite_index inserted Listing all the columns with indexes Dropping indexes unique_index and, composite_index