A file in a table is only a pointer to its information. These are utilized to accelerate the information recovery from a table.
On the off chance that we use records, the INSERT and UPDATE proclamations get executed in a more slow stage. While SELECT and WHERE get executed with in lesser time.
Creating an Index
The CREATE INDEX proclamation is utilized for making another Index in a table in Derby information base.
Syntax
Following is the sentence structure of the CREATE INDEX proclamation −
CTREATE INDEX index_name on table_name (column_name);
Example
Assume we have made a table named Employees in Apache Derby as demonstrated beneath.
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 accompanying SQL proclamation makes a record on the segment 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 records are utilized for information joining. When you make a UNIQUE list on a section in a table, it doesn't permit copy esteems.
Syntax
Following is the grammar of making an interesting list.
CREATE UNIQUE INDEX index_name on table_name (column_name);
Example
Following model makes a UNIQUE list on the section Id of the table Employee.
ij> CREATE UNIQUE INDEX unique_index on Emp (Phone_Number);
0 rows inserted/updated/deleted
Whenever you have made a one of a kind list on a section, you can't enter same qualities for that segment in another line. To put it plainly, a section which is has a UNIQE list won't permit copy esteems.
Addition a line in the Emp table as demonstrated underneath
ij> INSERT INTO Emp(Name, Salary, Location, Phone_Number) VALUES ('Amit',
45000, 'Hyderabad', 9848022338);
1 row inserted/updated/deleted
Since we have made an interesting list on the section Phone_No, on the off chance that you ty to enter a similar incentive as in the past record, it shows a mistake.
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 make a solitary file on two lines and it is called Composite record.
Syntax
Following is the linguistic structure of the composite list.
CREATE INDEX index_name on table_name (column_name1, column_name2);
Example
Following list makes a composite list on the sections Name and Location.
ij> CREATE INDEX composite_index on Emp (Name, Location);
0 rows inserted/updated/deleted
Displaying the Indexes
The SHOW INDEXES inquiry shows the rundown of lists on a table.
Syntax
Following is the linguistic structure of the SHOW INDEXES explanation −
SHOW INDEXES FROM table_name;
Example
Following model, I shows the files on the table Employees.
ij> SHOW INDEXES FROM Emp;
This creates the accompanying outcome.
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 articulation erases/drops the given record on a segment.
Syntax
Following is the language structure of the DROP INDEX proclamation.
DROP INDEX index_name;
Example
Following model drops a files named composite_index and unique_index made previously.
ij> DROP INDEX composite_index;
0 rows inserted/updated/deleted
ij>Drop INDEX unique_index;
0 rows inserted/updated/deleted
Presently, on the off chance that you check the rundown of records you can see list on one segment since we have erased the excess.
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 exhibits how to make drop files on a section 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 produces the accompanying outcome
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